平常我们在做列表查询条件,最常用的就是拼接查询条件,如下面的代码:
if (!string.IsNullOrEmpty(txt_proname.Text))
{
predicate = predicate.And(p => p.ProName.Contains(txt_proname.Text));
}
if (!string.IsNullOrEmpty(txt_insertcount.Text))
{
predicate = predicate.And(p => p.InsertCount.Equals(txt_insertcount.Text));
}
类似于我们平时SQL的条件累加,这种方式最容易实现我们所想要的查询结果,
但是在工作中,我们发现到,客户的需求,总是很不稳定,时不时的就给我们增加一个或者多个查询条件,
我们就总是在后台代码再增加一个if的判断条件去累加,这种方式看上去也没什么问题,但是如果条件多了,
我们看到一大篇的if查询条件的累加,代码看上去特别的不优雅,很费神,
现在我们就是要改造这种方法,让我们的代码看上去更清晰,扩展性更好。
我们新建一个类IQueryBuilder.cs,这个类是我们扩展查询条件方法的实现,例如有like,in,Equals,between等查询条件方式,如果我们需要用到别的查询方式,我们也可以在这里面进行扩展。
using System;
using System.Linq;
using System.Linq.Expressions;
using System.Data.Linq.SqlClient;
using System.Collections.Generic;
namespace LinqDemo
{
public static class QueryBuilder
{
public static IQueryBuilder<T> Create<T>()
{
return new QueryBuilder<T>();
}
}
class QueryBuilder<T> : IQueryBuilder<T>
{
private Expression<Func<T, bool>> predicate;
Expression<Func<T, bool>> IQueryBuilder<T>.Expression
{
get
{
return predicate;
}
set
{
predicate = value;
}
}
public QueryBuilder()
{
predicate = PredicateBuilder.True<T>();
}
}
/// <summary>
/// 动态查询条件创建者
/// </summary>
/// <typeparam name="T"></typeparam>
public interface IQueryBuilder<T>
{
Expression<Func<T, bool>> Expression { get; set; }
}
public static class IQueryBuilderExtensions
{
/// <summary>
/// 建立 Between 查询条件
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="q">动态查询条件创建者</param>
/// <param name="property">属性</param>
/// <param name="from">开始值</param>
/// <param name="to">结束值</param>
/// <returns></returns>
public static IQueryBuilder<T> Between<T, P>(this IQueryBuilder<T> q, Expression<Func<T, P>> property, P from, P to)
{
var parameter = property.GetParameters();
var constantFrom = Expression.Constant(from);
var constantTo = Expression.Constant(to);
Type type = typeof(P);
Expression nonNullProperty = property.Body;
//如果是Nullable<X>类型,则转化成X类型
if (IsNullableType(type))
{
type = GetNonNullableType(type);
nonNullProperty = Expression.Convert(property.Body, type);
}
var c1 = Expression.GreaterThanOrEqual(nonNullProperty, constantFrom);
var c2 = Expression.LessThanOrEqual(nonNullProperty, constantTo);
var c = Expression.AndAlso(c1, c2);
Expression<Func<T, bool>> lambda = Expression.Lambda<Func<T, bool>>(c, parameter);
q.Expression = q.Expression.And(lambda);
return q;
}
/// <summary>
/// 建立 Like ( 模糊 ) 查询条件
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="q">动态查询条件创建者</param>
/// <param name="property">属性</param>
/// <param name="value">查询值</param>
/// <returns></returns>
public static IQueryBuilder<T> Like<T>(this IQueryBuilder<T> q, Expression<Func<T, string>> property, string value)
{
value = value.Trim();
if (!string.IsNullOrEmpty(value))
{
var parameter = property.GetParameters();
var constant = Expression.Constant("%" + value + "%");
MethodCallExpression methodExp = Expression.Call(null, typeof(SqlMethods).GetMethod("Like",
new Type[] { typeof(string), typeof(string) }), property.Body, constant);
Expression<Func<T, bool>> lambda = Expression.Lambda<Func<T, bool>>(methodExp, parameter);
q.Expression = q.Expression.And(lambda);
}
return q;
}
/// <summary>
/// 建立 Equals ( 相等 ) 查询条件
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="q">动态查询条件创建者</param>
/// <param name="property">属性</param>
/// <param name="value">查询值</param>
/// <returns></returns>
public static IQueryBuilder<T> Equals<T, P>(this IQueryBuilder<T> q, Expression<Func<T, P>> property, P value)
{
var parameter = property.GetParameters();
var constant = Expression.Constant(value);
Type type = typeof(P);
Expression nonNullProperty = property.Body;
//如果是Nullable<X>类型,则转化成X类型
if (IsNullableType(type))
{
type = GetNonNullableType(type);
nonNullProperty = Expression.Convert(property.Body, type);
}
var methodExp = Expression.Equal(nonNullProperty, constant);
Expression<Func<T, bool>> lambda = Expression.Lambda<Func<T, bool>>(methodExp, parameter);
q.Expression = q.Expression.And(lambda);
return q;
}
/// <summary>
/// 建立 In 查询条件
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="q">动态查询条件创建者</param>
/// <param name="property">属性</param>
/// <param name="valuse">查询值</param>
/// <returns></returns>
public static IQueryBuilder<T> In<T,P>(this IQueryBuilder<T> q, Expression<Func<T, P>> property, params P[] values)
{
if (values != null && values.Length > 0)
{
var parameter = property.GetParameters();
var constant = Expression.Constant(values);
Type type = typeof(P);
Expression nonNullProperty = property.Body;
//如果是Nullable<X>类型,则转化成X类型
if (IsNullableType(type))
{
type = GetNonNullableType(type);
nonNullProperty = Expression.Convert(property.Body, type);
}
Expression<Func<P[], P, bool>> InExpression = (list, el) => list.Contains(el);
var methodExp = InExpression;
var invoke = Expression.Invoke(methodExp, constant, property.Body);
Expression<Func<T, bool>> lambda = Expression.Lambda<Func<T, bool>>(invoke, parameter);
q.Expression = q.Expression.And(lambda);
}
return q;
}
private static ParameterExpression[] GetParameters<T, S>(this Expression<Func<T, S>> expr)
{
return expr.Parameters.ToArray();
}
static bool IsNullableType(Type type)
{
return type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>);
}
static Type GetNonNullableType(Type type)
{
return type.GetGenericArguments()[0];
//return IsNullableType(type) ? type.GetGenericArguments()[0] : type;
}
}
}
②,我们再新建一个PredicateBuilder.cs类,这个类的作用,前面的章节有介绍过,如果项目中已经有这个类的话,这里就不需要创建了。
using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;
public static class PredicateBuilder
{
public static Expression<Funcbool>> True()
{
return f => true;
}
public static Expression<Funcbool>> False()
{
return f => false;
}
public static Expression<Funcbool>> Or(this Expression<Funcbool>> expr1, Expression<Funcbool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Funcbool>>(Expression.Or(expr1.Body, invokedExpr), expr1.Parameters);
}
public static Expression<Funcbool>> And(this Expression<Funcbool>> expr1, Expression<Funcbool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Funcbool>>(Expression.And(expr1.Body, invokedExpr), expr1.Parameters);
}
}
③,接下来我们要做一个查询的例子,来看看这种方式的好处到底有多好。我们新建一个页面ListQuery.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ListQuery.aspx.cs" Inherits="LinqDemo.ListQuery" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePartialRendering="true">
</asp:ScriptManager>
<table width="100%" border="1">
<tr>
<td>
id:<asp:TextBox ID="txt_id" runat="server"></asp:TextBox>
</td>
<td>
名称<asp:TextBox ID="txt_proname" runat="server"></asp:TextBox>
</td>
<td>
数量:<asp:TextBox ID="txt_insertcountMin" runat="server"></asp:TextBox>-->
<asp:TextBox ID="txt_insertcountMax" runat="server"></asp:TextBox>
</td>
<td>
时间:<asp:TextBox ID="txt_startTime" runat="server"></asp:TextBox>-->
<asp:TextBox ID="txt_EndTime" runat="server"></asp:TextBox>
</td>
<td>
<asp:Button ID="btnQuery" runat="server" OnClick="btnQuery_Click" Text="查询" />
</td>
</tr>
<tr>
<td colspan="5">
<asp:UpdatePanel runat="server" ID="up">
<ContentTemplate>
<asp:GridView ID="GridView1" runat="server" Width="100%" AutoGenerateColumns="False"
BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px"
CellPadding="4" ForeColor="Black" GridLines="Vertical">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<input type="checkbox" id="chk" runat="server" value='<%# Eval("id") %>' />
</ItemTemplate>
<ItemStyle Width="1px" />
</asp:TemplateField>
<asp:BoundField DataField="id" HeaderText="id" />
<asp:BoundField DataField="proname" HeaderText="名称" />
<asp:BoundField DataField="insertday" HeaderText="入货时间" />
<asp:BoundField DataField="insertcount" HeaderText="入货数量" />
</Columns>
<RowStyle HorizontalAlign="Center" BackColor="#F7F7DE" />
<FooterStyle BackColor="#CCCC99" />
<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="btnQuery" EventName="Click" />
</Triggers>
</asp:UpdatePanel>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
后台代码如下:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace LinqDemo
{
public partial class ListQuery : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Bind();
}
}
private void Bind()
{
//初始化数量的最大值和最小值
int InstertCountMin = int.MinValue;
int InsertCountMax = int.MaxValue;
InstertCountMin = txt_insertcountMin.Text == "" ? InstertCountMin : int.Parse(txt_insertcountMin.Text);
InsertCountMax = txt_insertcountMax.Text == "" ? InsertCountMax : int.Parse(txt_insertcountMax.Text);
//初始化时间的最大值和最小值
DateTime InsertStartDay = DateTime.Parse("1990-01-01");
DateTime InsertEndDay = DateTime.Parse("2100-01-01");
InsertStartDay = txt_startTime.Text == "" ? InsertStartDay : DateTime.Parse(txt_startTime.Text);
InsertEndDay = txt_EndTime.Text == "" ? InsertEndDay : DateTime.Parse(txt_EndTime.Text);
//创建查询条件
var builder = QueryBuilder.Create<InStore>();
builder = builder.Like(p => p.ProName, txt_proname.Text)
.Between(p => p.InsertCount, InstertCountMin, InsertCountMax)
.Between(p => p.InsertDay, InsertStartDay, InsertEndDay)
;
InStoreDataContext db = new InStoreDataContext();
var query = db.InStore.Where(builder.Expression);
GridView1.DataSource = query;
GridView1.DataBind();
}
protected void btnQuery_Click(object sender, EventArgs e)
{
Bind();
}
}
}
现在我们根据下面的图来解释下,这个方法是如何使用的。
黄色部分的,就是这个查询条件的用法,如果我们还需要别的条件,这需要在这个builder后面继续添加,这种查询还有个好处就是可以扩展,让我们的代码看上去更加的优雅,更方便阅读和修改。See,是不是感觉比用if那种累加的查询方式好很多呢,至少我是这么认为,呵呵。