LINQ那些事儿(5)- 动态查询
本文讨论了在LINQ2SQL中执行动态查询的方法
所谓动态查询,是指查询条件或查询值都是在运行时才能确定的查询。这就意味着我们不能hard-code定义查询变量(query variable),只有根据查询时传递的条件来拼凑。下面我们看看几组不同条件组合的查询。
1) 用户输入查询条件:City为”London”且ContactName包含”Thomas”
public IQueryable<Customer> GetCustomers(string city, string contactName) { var context = GenerateContext(); IQueryable<Customer> result = context.Customers; if (!string.IsNullOrEmpty(city)) { result = result.Where(c => c.City == city); } if (!string.IsNullOrEmpty(contactName)) { result = result.Where(c => c.ContactName.Contains(contactName)); } return result; }
2) 用户输入查询条件:City为”London”或”Paris”
由于Where和Where的连接是表示AND的关系,所以我们无法用1)的方法来表达这个查询。有一个可以利用的query operator是Union:
var context = GenerateContext(); IQueryable<Customer> result = null; string[] cities = { "London", "Paris" }; foreach (string item in cities) { string tmp = item; result = result == null ? context.Customers.Where(c => c.City == tmp) : result.Union(context.Customers.Where(c => c.City == tmp)); } context.Log = Console.Out; Console.WriteLine(result.Count());
虽然结果符合我们的要求,但是通过输出的SQL语句,你可以看到对于这种方式构造的Expression Tree,SQL并没有我们想要的精简(我们期望的是t0.City=’London’ OR t0.City=’Paris’)。输出SQL如下:
SELECT COUNT(*) AS [value] FROM ( SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[Cont actTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[ Country], [t0].[Phone], [t0].[Fax] FROM [dbo].[Customers] AS [t0] WHERE [t0].[City] = @p0 UNION SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[Cont actTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[ Country], [t1].[Phone], [t1].[Fax] FROM [dbo].[Customers] AS [t1] WHERE [t1].[City] = @p1 ) AS [t2]
另外的方法就是是根据所有查询条件和查询值,给Where构造一个Expession。首先来看看Where的签名:
IQueryable<T> Where<T>(this IQueryable<T> source, Expression<Func<T, bool>> predicate)
对于这样一个Expression,如果你熟悉Expression Tree的构造方法,完全可以自己写。《C# 3.0 In Nutshell》的作者写了一个小巧的PrediateBuilder,可以帮助我们用lambda expression快速构造这样的expression:
var context = GenerateContext(); Expression<Func<Customer, bool>> filter = PredicateBuilder.False<Customer>(); string[] cities = { "London", "Paris" }; foreach (string item in cities) { string tmp = item; filter = filter.Or(c => c.City == tmp); } context.Log = Console.Out; context.Customers.Where(filter).Count().Dump();
输出的SQL也是我们期待的结果
SELECT COUNT(*) AS [value] FROM [dbo].[Customers] AS [t0] WHERE ([t0].[City] = @p0) OR ([t0].[City] = @p1)
最后,如果你对以前拼接SQL语句的方式还是念念不忘的话,可以去用Dynamic LINQ,参考SocttGu’s Blog。
参考资料:
PredicateBuilder http://www.albahari.com/nutshell/predicatebuilder.aspx
Dynamic LINQ http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
链接
1、 LINQ那些事儿(1)- 定义从关系数据库到Entity Class的映射
2、 LINQ那些事儿(2)- 简单对象的CRUD操作和Association的级联操作
4、 LINQ那些事儿(4)- Query Expression和Query Operator
6、 LINQ那些事儿(6)- DataContext的对象生命周期管理
7、 LINQ那些事儿(7)- 通过自定义IEnumerable<T>来扩展LINQ
8、LINQ那些事儿(8)- 通过自定义IQueryable<T>和IQueryableProvider来扩展LINQ
All the posts in this blog are provided "AS IS" with no warranties, and confer no rights. Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution 2.5 China Mainland License.