LINQ动态组合查询
2010-08-02 09:09 破狼 阅读(18424) 评论(39) 编辑 收藏 举报最近在项目中用到了LINQ,在界面上有许多组合查询条件,我是一个比较懒的人,呵呵,不想在数据查询层写许多方法。我自己尝试写了个Expression的LINQ动态查询,目的是到达了,但是我在代码的初始表达式为null,每次组合AND前都要判断是否为空,为空则返回右边的表达式。今天在网上Google了一下,看到肖坤:Linq动态查询与模糊查询(带源码示例)中讲到的《dynamic linq queries / dynamic where clause (part 2) 》,中老外写的PredicateExtensions类。便用这个类修改了下自己的方案。不在是全表达式树动态生成了。今天特地写了一个基于Northwind的Demo拿来和大家分享:
效果贴图:
界面是用Telerik控件做的(控件对于我们后台开发人员来说就是少调样式)。
代码:
代码
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);
}
}
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);
}
}
其中其实很简单,主要方法就两个,一个事表达式树组合,和数据绑定。数据绑定,有一个
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看不全:故在这里也贴一个:
代码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点了,故改到下回讲解。
附带:本随笔代码下载
作者:破 狼
出处:http://www.cnblogs.com/whitewolf/
本文版权归作者,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。该文章也同时发布在我的独立博客中-个人独立博客、博客园--破狼和51CTO--破狼。