Implementing Dynamic Searching Using LINQ

A common requirement in forms-over-data applications is that users be able to search by any combination of fields to construct a dynamic query at run time.  For example, the search feature in this application allows the user to find all records that meet criteria defined on multiple columns:



 


 


 


LINQ makes it easy to write powerful queries like this over various data sources; for instance, we can use the following query to find all Orders shipped to a given country within a user-specified timeframe:


        Dim query = From order In db.Orders _


                    Where order.ShipCountry = txtCountry.Text _


                      And order.ShippedDate >= dtpStartDate.Value _


                      And order.ShippedDate <= dtpEndDate.Value


 


This is easy at compile-time, but what if we want to check the date the order was entered instead of the date it was shipped?  In this case we’d have to write a separate query using order.OrderDate.  Doing this dynamically at runtime isn’t all that difficult if you’re just building up a SQL string, but how would we do this with LINQ?  Doesn’t LINQ require me to specify the criteria at compile time in order to construct the query?  Fortunately the answer’s no, LINQ supports constructing dynamic queries at runtime through the Expression Tree API and the Expression Compiler.


 


In Visual Studio 2008, any valid VB expression can be represented as an expression tree.  What we need to do is create an Expression Tree to represent the user’s criteria, and then pass it to the LINQ to SQL runtime to do the SQL translation.  So for the first part of our Where clause above it’d look something like this:


        Dim p = Expression.Parameter(GetType(Order), “”)


        Dim order = GetType(Order).GetProperty(“ShipCountry”)


        Dim expr = Expression.Equal(Expression.PropertyOrField(p, order.Name), Expression.Constant(“Germany”))


        Dim predicate = Expression.Lambda(Of Func(Of Order, Boolean))(expr, New ParameterExpression() {p})


 


>Yikes, that’s a mouthful…and we’ve only done 1/3 of the Where clause so far!  I definitely don’t want to write 12 lines just to construct a Where clause at runtime.  What I’d like to be able to do is write an extension method called CreateCondition that would allow me to construct the expression tree in one easy line, something like this:


        Dim condition1 = db.Orders.CreateCondition(“ShipCountry”, Compare.Equal, “Germany”)


 


And then for ShippedDate we could make these two conditions:


        Dim startDate? = #1/1/1997#


        Dim endDate? = #1/31/1997#


 


        Dim condition2 = db.Orders.CreateCondition(“ShippedDate”, Compare.GreaterThanOrEqual, startDate)


        Dim condition3 = db.Orders.CreateCondition(“ShippedDate”, Compare.LessThanOrEqual, endDate)


 


(Note: There’s a similar sample in Beta2 called DynamicQueries which does a lot more than what we’re looking at here; think of this as an easier-to-write subset of DynamicQueries).


 


Notice that we passed in a String the first time, and a Nullable Date literal for the next two; we can do this because the CreateCondition method is generic and infers the type based on the parameter passed in.  We now need to combine the conditions into one big condition:


        Dim c = Condition.Combine(condition1, Compare.And, condition2, condition3)


 


Or we could take advantage of Operator Overloading and do it this way (equivalent to the line above):


        Dim c = condition1 And condition2 And condition3


 


Ok so now that we’ve constructed our Condition object, let’s use it to filter the data:


        ‘Filter out all Orders that don’t match the Condition


        ‘Note that the query isn’t executed yet to due to deferred execution


        Dim filteredQuery = db.Orders.Where(c)


 


        ‘We can now perform other operations (such as Order By) on filteredQuery


        Dim query = From row In filteredQuery _


                    Order By row.OrderDate, row.OrderID _


                    Select row


 


        ‘Executes the query and displays the results in DataGridView1


        DataGridView1.DataSource = query


 


So far so good, we’re using LINQ over a dynamically-constructed condition and everything’s strongly-typed.  We’re still only hitting the database once since deferred execution ensures the query doesn’t run until we actually enumerate the results (through databinding).  The Condition API has made it a lot easier to construct and compile the expression trees, but we’d still have to write a fair bit of code to construct these conditions based on user input.  That’s where the ConditionBuilder control comes in:


 



  


 


 


This allows the user to dynamically specify the criteria at runtime:


 



 


Notice that for Date fields we automatically get a DateTimePicker instead of a TextBox, and Booleans would result in a CheckBox.


 


Ok so we’ve covered what the user experience is at runtime, but how do we actually create the Condition API?  There’s a fair bit of documentation in the code below so I won’t go through all of it, but here’s the basics:


1.       There are three main classes: Condition, Condition(Of T), and Condition(Of T, S)


a.       Condition is an abstract class that is used to construct the generic versions.  By structuring it this way we get the benefits of generic type parameter inference – i.e. we don’t have to worry about passing the generic type parameters to the method; the factory method figures it out for us.


b.      Condition(Of T) is used to join multiple conditions together.  T is the element type (i.e. Order in the example above).


c.       Condition(Of T, S) is the simplest type; it represents an “object.propery <comparison> value” expression.  The type parameter S will be inferred to be the type of the value passed in (i.e. String, Date, Boolean etc…).


 


2.       For local execution of a query we compile the LambdaExpression to a delegate so that it can be executed in-memory.  The user can invoke this delegate by calling the Matches method.


‘Compile the lambda expression into a delegate


del = DirectCast(LambdaExpr.Compile(), Func(Of T, Boolean))


 


3.       The extension methods at the bottom are defined on IQueryable(Of T) for remote execution, and IEnumerable(Of T) for local execution.


 


The complete solution is attached in the file DynamicCondition.zip below, note that you’ll need to update the ConnectionString in app.config to point to your version of Northwind.


 


Imports System.ComponentModel


Imports System.Linq.Expressions


Imports System.Runtime.CompilerServices


 


Public Module DynamicQuery


    Public MustInherit Class Condition


 


‘Used to ensure we get the same instance of a particular ParameterExpression


‘across multiple queries


Private Shared ParamTable As New Dictionary(Of String, ParameterExpression)


 


‘The expression tree which will be passed to the LINQ to SQL runtime


Protected Friend LambdaExpr As LambdaExpression


 


‘Enumerates all the different comparisons which can be performed


Public Enum Compare


    [Or] = ExpressionType.Or


    [And] = ExpressionType.And


    [Xor] = ExpressionType.ExclusiveOr


    [Not] = ExpressionType.Not


    Equal = ExpressionType.Equal


    [Like] = ExpressionType.TypeIs + 1


    NotEqual = ExpressionType.NotEqual


    [OrElse] = ExpressionType.OrElse


    [AndAlso] = ExpressionType.AndAlso


    LessThan = ExpressionType.LessThan


    GreaterThan = ExpressionType.GreaterThan


    LessThanOrEqual = ExpressionType.LessThanOrEqual


    GreaterThanOrEqual = ExpressionType.GreaterThanOrEqual


End Enum


 


‘Constructs a Condition with T as the element type and S as the value’s type


Public Shared Function Create(Of T, S)(ByVal dataSource As IEnumerable(Of T), _


                                       ByVal propertyName As String, _


                                       ByVal condType As Compare, _


                                       ByVal value As S) As Condition(Of T, S)


 


    Return New Condition(Of T, S)(propertyName, condType, value)


End Function


 


‘Constructs a Condition with T as the element type and valueType as the value’s type


‘This is useful for situations where you won’t know the value’s type until runtime.


Public Shared Function Create(Of T)(ByVal dataSource As IEnumerable(Of T), _


                                    ByVal propertyName As String, _


                                    ByVal condType As Compare, _


                                    ByVal value As Object, _


                                    ByVal valueType As Type) As Condition(Of T)


 


    Return New Condition(Of T)(propertyName, condType, value, valueType)


End Function


 


 


”’ <summary>


”’ Creates a Condition which combines two other Conditions


”’ </summary>


”’ <typeparam name=”T”>The type the condition will execute against</typeparam>


”’ <param name=”cond1″>The first Condition</param>


”’ <param name=”condType”>The operator to use on the conditions</param>


”’ <param name=”cond2″>The second Condition</param>


”’ <returns>A new Condition which combines two Conditions into one according to the specified operator</returns>


”’ <remarks></remarks>


Public Shared Function Combine(Of T)(ByVal cond1 As Condition(Of T), _


                                     ByVal condType As Compare, _


                                     ByVal cond2 As Condition(Of T)) As Condition(Of T)


    Return Condition(Of T).Combine(cond1, condType, cond2)


End Function


 


‘Combines multiple conditions according to the specified operator


Public Shared Function Combine(Of T)(ByVal cond1 As Condition(Of T), _


                                     ByVal condType As Compare, _


                                     ByVal ParamArray conditions() As Condition(Of T)) As Condition(Of T)


    Return Condition(Of T).Combine(cond1, condType, conditions)


End Function


 


‘Combines two Expressions according to the specified operator (condType)


Protected Shared Function CombineExpression(ByVal left As Expression, _


                                            ByVal condType As Compare, _


                                            ByVal right As Expression) As Expression


 


    ‘Join the Expressions based on the operator


    Select Case condType


        Case Compare.Or : Return Expression.Or(left, right)


        Case Compare.And : Return Expression.And(left, right)


        Case Compare.Xor : Return Expression.ExclusiveOr(left, right)


        Case Compare.Equal : Return Expression.Equal(left, right)


        Case Compare.OrElse : Return Expression.OrElse(left, right)


        Case Compare.AndAlso : Return Expression.AndAlso(left, right)


        Case Compare.NotEqual : Return Expression.NotEqual(left, right)


        Case Compare.LessThan : Return Expression.LessThan(left, right)


        Case Compare.GreaterThan : Return Expression.GreaterThan(left, right)


        Case Compare.LessThanOrEqual : Return Expression.LessThanOrEqual(left, right)


        Case Compare.GreaterThanOrEqual : Return Expression.GreaterThanOrEqual(left, right)


        Case Compare.Like


            ‘For the Like operator we encode a call to the LikeString method in the VB runtime


            Dim m = GetType(CompilerServices.Operators).GetMethod(“LikeString”)


            Return Expression.Call(m, left, right, Expression.Constant(CompareMethod.Binary))


        Case Else


            Throw New ArgumentException(“Not a valid Condition Type”, “condType”, Nothing)


    End Select


End Function


 


‘Since both type parameters must be the same, we can turn what would normally


‘be a Func(Of T, T, Boolean) into a Func(Of T, Boolean)


Protected Shared Function CombineFunc(Of T)(ByVal d1 As Func(Of T, Boolean), _


                                            ByVal condType As Compare, _


                                            ByVal d2 As Func(Of T, Boolean)) As Func(Of T, Boolean)


 


    ‘Return a delegate which combines delegates d1 and d2


    Select Case condType


        Case Compare.Or : Return Function(x) d1(x) Or d2(x)


        Case Compare.And : Return Function(x) d1(x) And d2(x)


        Case Compare.Xor : Return Function(x) d1(x) Xor d2(x)


        Case Compare.Equal : Return Function(x) d1(x) = d2(x)


        Case Compare.OrElse : Return Function(x) d1(x) OrElse d2(x)


        Case Compare.AndAlso : Return Function(x) d1(x) AndAlso d2(x)


        Case Compare.NotEqual : Return Function(x) d1(x) <> d2(x)


        Case Compare.LessThan : Return Function(x) d1(x) < d2(x)


        Case Compare.GreaterThan : Return Function(x) d1(x) > d2(x)


        Case Compare.LessThanOrEqual : Return Function(x) d1(x) <= d2(x)


        Case Compare.GreaterThanOrEqual : Return Function(x) d1(x) >= d2(x)


        Case Else


            Throw New ArgumentException(“Not a valid Condition Type”, “condType”)


    End Select


End Function


 


‘Guarantees that we get the same instance of a ParameterExpression for a given type t.


Protected Shared Function GetParamInstance(ByVal dataType As Type) As ParameterExpression


 


    ‘Parameters are matched by reference, not by name, so we cache the instances in a Dictionary.


    If Not ParamTable.ContainsKey(dataType.Name) Then


        ParamTable.Add(dataType.Name, Expression.Parameter(dataType, dataType.Name))


    End If


 


    Return ParamTable.Item(dataType.Name)


End Function


 


    End Class


 


    Public Class Condition(Of T) : Inherits Condition


 


‘Delegate that contains a compiled expression tree which can be run locally


Friend del As Func(Of T, Boolean)


 


Friend Sub New()


End Sub


 


Friend Sub New(ByVal propName As String, ByVal condType As Compare, ByVal value As Object, ByVal valueType As Type)


    ‘Split the string to handle nested property access


    Dim s = propName.Split(“.”c)


 


    ‘Get the PropertyInfo instance for propName


    Dim pInfo = GetType(T).GetProperty(s(0))


    Dim paramExpr = GetParamInstance(GetType(T))


    Dim callExpr = Expression.MakeMemberAccess(paramExpr, pInfo)


 


    ‘For each member specified, construct the additional MemberAccessExpression


    ‘For example, if the user says “myCustomer.Order.OrderID = 4” we need an


    ‘additional MemberAccessExpression for “Order.OrderID = 4”


    For i = 1 To UBound(s)


        pInfo = pInfo.PropertyType.GetProperty(s(i))


        callExpr = Expression.MakeMemberAccess(callExpr, pInfo)


    Next


 


    ‘ConstantExpression representing the value on the left side of the operator


    Dim valueExpr = Expression.Constant(value, valueType)


 


    Dim b As Expression = CombineExpression(callExpr, condType, valueExpr)


    LambdaExpr = Expression.Lambda(Of Func(Of T, Boolean))(b, New ParameterExpression() {paramExpr})


 


    ‘Compile the lambda expression into a delegate


    del = DirectCast(LambdaExpr.Compile(), Func(Of T, Boolean))


End Sub


 


‘Combines two conditions according to the specified operator


Friend Overloads Shared Function Combine(ByVal cond1 As Condition(Of T), _


                                         ByVal condType As Compare, _


                                         ByVal cond2 As Condition(Of T)) As Condition(Of T)


    Dim c As New Condition(Of T)


 


    Dim b As Expression = CombineExpression(cond1.LambdaExpr.Body, _


                                            condType, _


                                            cond2.LambdaExpr.Body)


 


    Dim paramExpr() = New ParameterExpression() {GetParamInstance(GetType(T))}


 


    ‘Create the LambdaExpression and compile the delegate


    c.LambdaExpr = Expression.Lambda(Of Func(Of T, Boolean))(b, paramExpr)


    c.del = Condition.CombineFunc(cond1.del, condType, cond2.del)


 


    Return c


End Function


 


‘Combines multiple conditions according to the specified operator


Friend Overloads Shared Function Combine(ByVal cond1 As Condition(Of T), _


                                         ByVal condType As Compare, _


                                         ByVal ParamArray conditions() As Condition(Of T)) As Condition(Of T)


    Dim finalCond = cond1


    For Each c In conditions


        finalCond = Condition.Combine(finalCond, condType, c)


    Next


 


    Return finalCond


End Function


 


‘Run query locally instead of remotely


Public Function Matches(ByVal row As T) As Boolean


    Return del(row) ‘passes the row into the delegate to see if it’s a match


End Function


 


‘Overloaded operators – allows syntax like “(condition1 Or condition2) And condition3”


Public Shared Operator And(ByVal c1 As Condition(Of T), ByVal c2 As Condition(Of T)) As Condition(Of T)


    Return Condition.Combine(c1, Compare.And, c2)


End Operator


 


Public Shared Operator Or(ByVal c1 As Condition(Of T), ByVal c2 As Condition(Of T)) As Condition(Of T)


    Return Condition.Combine(c1, Compare.Or, c2)


End Operator


 


Public Shared Operator Xor(ByVal c1 As Condition(Of T), ByVal c2 As Condition(Of T)) As Condition(Of T)


    Return Condition.Combine(c1, Compare.Xor, c2)


End Operator


 


    End Class


 


    ‘Represents a condition like “object.Property = value”


    ‘In this case object is of type T, and value is of type S


   


    ‘Even though most of the logic for this is already in the base class,


    ‘defining a second generic parameter means the user doesn’t have to


    ‘pass in a System.Type – it can just be inferred.


    Public Class Condition(Of T, S) : Inherits Condition(Of T)


 


        Friend Sub New(ByVal propName As String, ByVal condType As Compare, ByVal value As S)


            MyBase.New(propName, condType, value, GetType(S))


        End Sub


 


    End Class


 


#End Region


 


#Region “Extension Methods”


 


    ‘Filters an IQueryable(Of T) according to the specified condition


    <Extension()> _


    Public Function Where(Of T)(ByVal source As IQueryable(Of T), _


                                ByVal condition As Condition(Of T)) As IQueryable(Of T)


 


        Dim callExpr = Expression.Call(GetType(Queryable), “Where”, _


                                       New Type() {source.ElementType}, source.Expression, _


                                       Expression.Quote(condition.LambdaExpr))


 


        Return CType(source.Provider.CreateQuery(callExpr), IQueryable(Of T))


    End Function


 


    ‘Filters an IEnumerable(Of T) according to the specified condition


    <Extension()> _


    Public Function Where(Of T)(ByVal source As IEnumerable(Of T), _


                                ByVal condition As Condition(Of T)) As IEnumerable(Of T)


        Return source.Where(condition.del)


    End Function


 


 


    ‘Extension method that can be called off any type that implements IEnumerable(Of T),


    ‘which constructs a Condition with T as the element type and S as the value’s type


    <Extension(), EditorBrowsable(EditorBrowsableState.Always)> _


    Public Function CreateCondition(Of T, S)(ByVal dataSource As IEnumerable(Of T), _


                                             ByVal propName As String, _


                                             ByVal condType As condition.Compare, _


                                             ByVal value As S) As Condition(Of T, S)


 


        Return Condition.Create(dataSource, propName, condType, value)


    End Function


 


#End Region


 


End Module


The complete sample (including the code for the ConditionBuilder control) is available in the attached .zip file below.  While we’ve got the basics going pretty well, there’s definitely a lot of things you can extend the sample to do, such as:


1.       Add support for Grouping conditions into the ConditionBuilder – right now you’d have to drop down to the API to construct something like (condition1 Or condition2) And condition3.


2.       Add a .Select extension method to dynamically control which fields you bring back from the database.  This will improve performance in situations where you don’t want to return all fields, but you’ll lose strong-typing of your results since the method will have to return IQueryable instead of IQueryable(Of T).


3.       Add extension methods for OrderBy, Take, Skip, Distinct etc…  Each of these methods can still return strongly-typed results since they don’t change the element type; if you pass in T they return T.


4.       The Condition API supports nested property access (i.e. Where customer.Orders.Salesman.Name = “Bob”), but it’d take a bit more work to surface this in the ConditionBuilder control.


 


That’s everything, now all you have to do is download Beta2 and try it out!


 


Jonathan

DynamicCondition.zip