linq to sql 学习（11）linq 构造更完美的动态数据查询（可扩展）

平常我们在做列表查询条件，最常用的就是拼接查询条件，如下面的代码：

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那种累加的查询方式好很多呢，至少我是这么认为，呵呵。