[原文作者]：Jonathan Aneja
[原文链接]：Implementing Dynamic Searching Using LINQ
在数据绑定应用程序中,一个比较常见的需求是:在程序运行时,用户能够使用任何字段的组合,动态构造条件进行查询。例如,在以下程序的查询功能中,用户可以任意组合多个列上定义的条件,并查询出符合条件的所有记录：
就像上图一样,通过各种数据源绑定,LINQ可以写出很强大的查询语句,例如,我们可以使用以下语句来查找指定的装运时间范围内,送到指定国家的所有订单:
Dim query = From order In db.Orders _
Where order.ShipCountry = txtCountry.Text _
And order.ShippedDate >= dtpStartDate.Value _
And order.ShippedDate <= dtpEndDate.Value
这很容易在编译时实现,但如果我们需要查询的是订单时间范围而不是装运时间范围呢？这种情况我们需要编写一个使用 order.OrderDate的查询。在运行时,动态生成一条这样的SQL语句不是一件难事,但如果用 LINQ应该怎么做呢? LINQ需要我们在编译时指定条件才能构造查询吗? 很幸运,答案是不,通过表达式树 API(Expression Tree API) 和表达式编译器(Expression Compiler),LINQ 支持在运行时构造动态查询.
在 Visual Studio 2008中,任何有效的VB表达式可表示为一个表达式树(Expression Tree)。 我们要做的是创建一个Expression Tree来表示该用户的条件,然后将它传递给LINQ to SQL[注①]来转换成SQL。
因此,上面 的Where子句可以写成这样:
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})
到目前为止,我们只完成了其中1/3的Where子句,为了动态构造一个Where子句而去写12行代码显得太啰嗦了!我会去写一个CreateCondition 的扩展方法,这个方法可以让我构造出Expression tree,而只需要用一行简单的代码就行了,参见:
Dim condition1 = db.Orders.CreateCondition("ShipCountry",Compare.Equal,"Germany")
对于ShippedDate的日期查询条件,我们可以使用下面的startDate 和endDate:
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)
(注: 在Visual Studio 目录有一个叫DynamicQuery的项目,里面有更详细的应用).
注意到我们为第一个条件传入一个字符串”Germany”,后两个传入了可空类型的日期;因为CreateCondition是一个泛型方法,可以根据传入的参数推断出具体类型.我们现在需要把这几个条件集合成一个条件.
Dim c = Condition.Combine(condition1,Compare.And,condition2,condition3)
或者可以利用操作符重载写成这样(和上面的效果一样):
Dim c = condition1 And condition2 And condition3
好了,我们已经构造好了查询条件,让我们筛选出所需数据吧.
'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
至此,我们已经使用LINQ动态构造出了查询条件,而且所有都是强类型的.并且我们只和数据库打了一次交道,由于延迟执行,直到我们需要的时候(数据绑定的时候)查询才会被执行.Condition API 使得我们构造和编绎Expression trees已经显得很简单,但是我们还必须根据用户的输入,写代码去构造这些查询条件. 我们可以考虑实现一个ConditionBuilder 控件,这样就只要把控件拖放过来就好,一切显得很简单:
这个控件允许用户在运行时动态的指定条件:
注意到当条件为日期时,控件用DateTimePicker代替了TextBox,布尔型会用CheckBox表示,这个实用的功能会让用户觉得很方便.
我们已经在文章中比较详细的说明用户是怎么使用控件的,但我们又是如何创建Condition API的呢？在代码中可以看到详细的实现,这里就不一一讲解,下面对一些基本的代码实现作个简要解释:
1. 有三个主要的Class：Condition,Condition(Of T),and Condition(Of T,S)
a.Condition 是一个抽象类,其他泛型类会继承此抽象类.通过这种方式,泛型的类型推断的好处是很显见的,例如,我们不需要担心传递给方法的泛型类型参数；工厂方法会做出判断.
b.Condition(Of T) 用来把多个条件结合在一起。T是element type（在上面的示例中即为 Order）。
c.Condition(Of T,S)的代码是最简单的,只有短短的几行； 它表示一个”object.property <comparison> value” 表达式。参数S的类型将被作为传入值的类型（即值的类型是字符串类型,日期类型,Boolean 类型…）
2.为了在本地执行查询,我们把 LambdaExpression 编译成delegate ,这样他可以在内存中执行.用户可以通过访问匹配方法来调用此delegate。
'Compile the lambda expression into a delegate
del = DirectCast(LambdaExpr.Compile(),Func(Of T,Boolean))
3.Module DynamicQuery最下面的护展方法,返回IQueryable(Of T) 为远程执行,返回IEnumerable (Of T) 用于本地执行。
附加文件 DynamicCondition.zip是完整的解决方案,您将需要更新app.config中的 “ConnectionString” 连接字符串,以指向你需要使用到的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