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
Light
Dark
0 comments