使用LINQ实现动态查询
[原文作者]：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 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
完整的版本可以参见附件(包括ConditionBuilder 控件的代码).我们可以这个基础上做许多扩展,以在项目中使用,比如:
1. 为ConditionBuilder 加入条件分组的支持 – 例如,构造像(condition1 Or condition2) And condition3这样的查询条件.
2. 加入扩展方法Select,动态选择你需要的字段,这样不必返回所有字段以提高性能,但是将会失去强类型的支持,因为方法将返回Iqueryable而不是IQueryable(Of T).
3. 加入扩展方法 OrderBy,Take,Skip,Distinct 等等,这些方法依然是强类型的返回值,因为他们不会改变element type; 你传入T也将会返回T.
4. Conidition API 支持嵌套属性访问(如Where customer.Orders.Salesman.Name = “Bob”),但是这样需要对ConditionBuilder 控件介面修改.
以上就是这些内容了,现在你需要做的就是下载vs2008 Beta2, 自己试一试.
注①[ LINQ to SQL是 O/RM (对象关系映射),允许使用.NET类来为关系数据库建模,是LINQ（.NET语言集成查询）的一部分,全称基于关系数据的.NET 语言集成查询,用于以对象形式管理关系数据,并提供了丰富的查询功能,它和Linq to xml、Linq to objects、Linq to dataset、Linq to entities等组成了强大的LINQ。更多内容可以参考网络文章]
posted on 2007-09-27 14:24:00 by VBCTI 评论(8) 阅读(3725)