最近在項目中用到了LINQ，在界面上有許多組合查詢條件，我是一個比較懶的人，呵呵，不想在資料查詢層寫許多方法。我自己嘗試寫了個Expression的LINQ動态查詢，目的是到達了，但是我在代碼的初始表達式為null，每次組合AND前都要判斷是否為空，為空則傳回右邊的表達式。今天在網上Google了一下，看到肖坤：Linq動态查詢與模糊查詢（帶源碼示例）中講到的《dynamic linq queries / dynamic where clause (part 2) 》，中老外寫的PredicateExtensions類。便用這個類修改了下自己的方案。不在是全表達式樹動态生成了。今天特地寫了一個基于Northwind的Demo拿來和大家分享：
效果貼圖：
界面是用Telerik控件做的（控件對于我們背景開發人員來說就是少調樣式）。
其中其實很簡單，主要方法就兩個，一個事表達式樹組合，和資料綁定。資料綁定，有一個
Expression<Func<Orders, bool>> 的where查詢條件。
Controller類Code
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Web;
- using System.Linq.Expressions;
- using System.Diagnostics;
- public class OrderController
- {
- protected NorthwindDataContext DataContext
- {
- get
- {
- NorthwindDataContext context =
- HttpContext.Current.Items["NorthwindDataContext"]
- as NorthwindDataContext;
- if (context == null)
- {
- context = new NorthwindDataContext();
- HttpContext.Current.Items["NorthwindDataContext"] = context;
- }
- return context;
- }
- }
- public List<Orders> GetOrders<TKey>(int currentPage,
- int pagesize, Expression<Func<Orders, bool>> whereQuery,
- QueryableOrderEntry<Orders, TKey> orderQuery)
- //重點在這裡的參數
- {
- IQueryable<Orders> query = DataContext.Orders;
- if (whereQuery != null)
- {
- query = query.Where(whereQuery);
- }
- if (orderQuery != null)
- {
- if (orderQuery.OrderDirection == OrderDirection.ASC)
- {
- query= query.OrderBy(orderQuery.Expression);
- }
- else
- {
- query = query.OrderByDescending(orderQuery.Expression);
- }
- }
- Debug.WriteLine(DataContext.GetCommand(query.Skip((currentPage - 1) * pagesize)
- .Take(pagesize)).CommandText);
- return query.Skip((currentPage - 1) * pagesize).Take(pagesize).ToList();
- }
- public void Save()
- {
- DataContext.SubmitChanges();
- }
- }
- 複制代碼
我的排序輔助類： - 代碼
- using System;
- using System.Linq.Expressions;
- public class QueryableOrderEntry<TSource, TKey>
- {
- public QueryableOrderEntry(Expression<Func<TSource, TKey>> expression)
- {
- this.Expression = expression;
- OrderDirection = OrderDirection.ASC;
- }
- public QueryableOrderEntry(Expression<Func<TSource, TKey>> expression,
- OrderDirection orderDirection)
- {
- this.Expression = expression;
- OrderDirection = orderDirection;
- }
- public Expression<Func<TSource, TKey>> Expression
- {
- get;
- set;
- }
- public OrderDirection OrderDirection
- {
- get;
- set;
- }
- }
- public enum OrderDirection
- {
- ASC, DESC
- }
- 複制代碼
老外的PredicateExtensions類很簡單，隻是真的思路很優秀，看到代碼我們都會恍然大悟， 但是估計我這鼠輩很難想到。 代碼
- public static class PredicateExtensions
- {
- public static Expression<Func<T, bool>> True<T>() { return f => true; }
- public static Expression<Func<T, bool>> False<T>() { return f => false; }
- public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expression1,
- Expression<Func<T, bool>> expression2)
- {
- var invokedExpression = Expression.Invoke(expression2,
- expression1.Parameters.Cast<Expression>());
- return Expression.Lambda<Func<T, bool>>(Expression.Or(
- expression1.Body, invokedExpression),
- expression1.Parameters);
- }
- public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expression1,
- Expression<Func<T, bool>> expression2)
- {
- var invokedExpression = Expression.Invoke(expression2,
- expression1.Parameters.Cast<Expression>());
- return Expression.Lambda<Func<T, bool>>(Expression.And(expression1.Body,
- invokedExpression), expression1.Parameters);
- }
- }
- 複制代碼
上面貼圖條件輸出框中輸出的sql我也截了一個圖來供參考：
sql看不全：故在這裡也貼一個： - SELECT TOP (15) [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate],
- [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName],
- [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode],
- [t0].[ShipCountry]FROM [dbo].[Orders] AS [t0]
- WHERE ([t0].[CustomerID] LIKE @p0) AND ([t0].[EmployeeID] IS NOT NULL) AND
- (([t0].[EmployeeID]) = @p1) AND ([t0].[OrderDate] IS NOT NULL) AND
- (([t0].[OrderDate]) >= @p2) AND ([t0].[OrderDate] IS NOT NULL) AND
- (([t0].[OrderDate]) <= @p3)ORDER BY [t0].[CustomerID] DESC
- 複制代碼
在這裡說老外的思想優秀，該講解一下，但是限于篇幅，還有時間淩晨1點了，故改到下回講解。 附帶： 本随筆代碼下載下傳
：代碼
代碼：
- using System;
- using System.Web.UI;
- using System.Linq.Expressions;
- public partial class _Default : System.Web.UI.Page
- {
- private OrderController controller = new OrderController();
- protected void Page_Load(object sender, EventArgs e)
- {
- if (!IsPostBack)
- {
- Bindgrid(null);
- }
- }
- public void Bindgrid(Expression<Func<Orders, bool>> whereqQuery)
- {
- grid.DataSource = controller.GetOrders(1, 15, whereqQuery,
- new QueryableOrderEntry<Orders,
- string>(t => t.CustomerID, OrderDirection.DESC));// 分頁數目應該用分頁控件，
- 這裡隻是簡單測試，是以偷個懶
- grid.DataBind();
- }
- protected void btnSearch_Click(object sender, ImageClickEventArgs e)
- {
- Expression<Func<Orders, bool>> expression = PredicateExtensions.True<Orders>();
- if (!string.IsNullOrEmpty(txtCustomerId.Text.Trim()))
- {
- string str = txtCustomerId.Text.Trim();
- expression = expression.And(o => o.CustomerID.Contains(str));
- }
- if (!string.IsNullOrEmpty(txtEmplyeeId.Text.Trim()))
- {
- string str = txtEmplyeeId.Text.Trim();
- expression = expression.And(o => o.EmployeeID.HasValue &&
- o.EmployeeID.Value.Equals(int.Parse(str)));
- }
- if (txtOrderDateStart.SelectedDate.HasValue)
- {DateTime dt=txtOrderDateStart.SelectedDate.Value;
- expression = expression.And(o => o.OrderDate.HasValue
- && o.OrderDate.Value >= dt);
- }
- if (txtOrderDateEnd.SelectedDate.HasValue)
- {
- DateTime dt = txtOrderDateEnd.SelectedDate.Value;
- expression = expression.And(o => o.OrderDate.HasValue
- && o.OrderDate.Value <=dt);
- }
- Bindgrid(expression);
- }
- }
- 複制代碼