Entity Framework使用表达式树实现动态条件查询

1、前言

在一般的系统开发中，动态条件查询随处可见，下图就是一个典型的动态查询页面：

分析上图可以发现：面板中一共设置了4个筛选条件，也就是说一共有16种查询条件的组合形式。由于现在无法确定用户会选择哪几个条件作为查询的依据，因此在后台需要动态构建表达式树，下面给出实现代码。

2、实现方法

2.1、继承ExpressionVisitor

作为一个抽象类，ExpressionVisitor无法实例化对象，因此在这里创建一个ParameterRebinder继承它，代码如下：

using System.Linq.Expressions;

namespace WebApplication1
{
    public class ParameterRebinder : ExpressionVisitor
    {
        /// <summary>
        /// 类型参数
        /// </summary>
        private ParameterExpression parameter;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="parameter">类型参数</param>
        public ParameterRebinder(ParameterExpression parameter)
        {
            this.parameter = parameter;
        }

        /// <summary>
        /// 替换类型参数
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        public Expression RebindParameter(Expression expression)
        {
            return Visit(expression);
        }

        /// <summary>
        /// 重写VisitParameter
        /// </summary>
        /// <param name="node"></param>
        /// <returns></returns>
        protected override Expression VisitParameter(ParameterExpression node)
        {
            return this.parameter;
        }
    }
}

2.2、扩展Expression
创建一个静态类ExpressionExtension，该类用来扩展Expression，代码如下：

using System;
using System.Linq.Expressions;

namespace WebApplication1
{
    public static class ExpressionExtension
    {
        /// <summary>
        /// 初始化一个逻辑值为true的表达式
        /// </summary>
        /// <typeparam name="TEntity">实体类型</typeparam>
        /// <returns>新的表达式</returns>
        public static Expression<Func<TEntity, bool>> True<TEntity>()
        {
            return t => true;
        }

        /// <summary>
        /// 初始化一个逻辑值为false的表达式
        /// </summary>
        /// <typeparam name="TEntity">实体类型</typeparam>
        /// <returns>新的表达式</returns>
        public static Expression<Func<TEntity, bool>> False<TEntity>()
        {
            return t => false;
        }

        /// <summary>
        /// 生成逻辑与表达式
        /// </summary>
        /// <typeparam name="TEntity">实体类型</typeparam>
        /// <param name="first">第一个表达式</param>
        /// <param name="second">第二个表达式</param>
        /// <returns>新的表达式</returns>
        public static Expression<Func<TEntity, bool>> And<TEntity>(this Expression<Func<TEntity, bool>> first, Expression<Func<TEntity, bool>> second)
        {
            ParameterExpression parameter = Expression.Parameter(typeof(TEntity), "t");
            ParameterRebinder rebinder = new ParameterRebinder(parameter);
            Expression left = rebinder.RebindParameter(first.Body);
            Expression right = rebinder.RebindParameter(second.Body);
            Expression body = Expression.AndAlso(left, right);
            Expression<Func<TEntity, bool>> expression = Expression.Lambda<Func<TEntity, bool>>(body, parameter);
            return expression;
        }

        /// <summary>
        /// 生成逻辑或表达式
        /// </summary>
        /// <typeparam name="TEntity">实体类型</typeparam>
        /// <param name="first">第一个表达式</param>
        /// <param name="second">第二个表达式</param>
        /// <returns>新的表达式</returns>
        public static Expression<Func<TEntity, bool>> Or<TEntity>(this Expression<Func<TEntity, bool>> first, Expression<Func<TEntity, bool>> second)
        {
            ParameterExpression parameter = Expression.Parameter(typeof(TEntity), "t");
            ParameterRebinder rebinder = new ParameterRebinder(parameter);
            Expression left = rebinder.RebindParameter(first.Body);
            Expression right = rebinder.RebindParameter(second.Body);
            Expression body = Expression.OrElse(left, right);
            Expression<Func<TEntity, bool>> expression = Expression.Lambda<Func<TEntity, bool>>(body, parameter);
            return expression;
        }
    }
}

2.3、后台动态构造查询条件
后台接收参数，如果某个参数不为空，则构造该参数对应的表达式，代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using WebApplication1.Models;

namespace WebApplication1.Handlers
{
    /// <summary>
    /// SearchHandler 的摘要说明
    /// </summary>
    public class SearchHandler : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";

            // 获取参数
            int pageIndex = int.Parse(context.Request["pageIndex"].ToString().Trim());
            int pageSize = int.Parse(context.Request["pageSize"].ToString().Trim());
            string name = context.Request["name"].ToString().Trim();
            string gender = context.Request["gender"].ToString().Trim();
            string birthOfDate = context.Request["birthOfDate"].ToString().Trim();
            string maritalStatus = context.Request["maritalStatus"].ToString().Trim();

            // 构造表达式
            Expression<Func<Person, bool>> expression = ExpressionExtension.True<Person>();
            if (!string.IsNullOrEmpty(name))
            {
                expression = expression.And(p => p.Name.Contains(name));
            }
            if (!string.IsNullOrEmpty(gender))
            {
                expression = expression.And(p => p.Gender == gender);
            }
            if (!string.IsNullOrEmpty(birthOfDate))
            {
                DateTime dateTime = DateTime.Parse(birthOfDate);
                expression = expression.And(p => p.BirthOfDate == dateTime);
            }
            if (!string.IsNullOrEmpty(maritalStatus))
            {
                expression = expression.And(p => p.MaritalStatus == maritalStatus);
            }

            // 查询数据
            DaoContext db = new DaoContext();
            var data = new
            {
                total = db.Set<Person>().Where(expression).Count(),
                rows = db.Set<Person>().Where(expression).OrderBy(p => p.Id).Skip((pageIndex - 1) * pageSize).Take(pageIndex * pageSize).ToList()
            };
            db.Dispose();

            // 序列化返回前端
            IsoDateTimeConverter converter = new IsoDateTimeConverter();
            converter.DateTimeFormat = "yyyy-MM-dd";
            context.Response.Write(JsonConvert.SerializeObject(data, Formatting.None, converter));
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

2.4、前端代码

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title>动态条件查询</title>
    <!-- bootstrap -->
    <link href="lib/bootstrap/css/bootstrap.min.css" rel="stylesheet" />
    <script src="lib/bootstrap/js/jquery-3.4.1.min.js"></script>
    <script src="lib/bootstrap/js/bootstrap.min.js"></script>
    <!-- bootstrap-datetimepicker -->
    <link href="lib/bootstrap-datetimepicker/bootstrap-datetimepicker.min.css" rel="stylesheet" />
    <script src="lib/bootstrap-datetimepicker/bootstrap-datetimepicker.min.js"></script>
    <script src="lib/bootstrap-datetimepicker/bootstrap-datetimepicker.zh-CN.js"></script>
    <!-- bootstrap-table -->
    <link href="lib/bootstrap-table/bootstrap-table.min.css" rel="stylesheet" />
    <script src="lib/bootstrap-table/bootstrap-table.min.js"></script>
    <script src="lib/bootstrap-table/locale/bootstrap-table-zh-CN.min.js"></script>
</head>
<body>
    <div class="container">
        <div class="row">
            <div class="col-md-12" style="margin-top:80px;">
                <div class="panel panel-primary">
                    <div class="panel-heading">
                        <h3 class="panel-title">查询条件</h3>
                    </div>
                    <div class="panel-body">
                        <form class="form-inline" role="form" onsubmit="return search()">
                            <div class="form-group">
                                <label for="name">姓名：</label>
                                <input type="text" id="name" class="form-control" style="width:180px;" placeholder="请输入姓名" />
                            </div>
                            <div class="form-group">
                                <label for="gender">性别：</label>
                                <select id="gender" class="form-control" style="width:180px;">
                                    <option value="">请选择性别</option>
                                    <option value="男">男</option>
                                    <option value="女">女</option>
                                </select>
                            </div>
                            <div class="form-group">
                                <label for="birthOfDate">出生日期：</label>
                                <input type="text" id="birthOfDate" class="form-control" style="width:180px;" placeholder="请选择出生日期" />
                            </div>
                            <div class="form-group">
                                <label for="maritalStatus">婚姻状况：</label>
                                <select id="maritalStatus" class="form-control" style="width:180px;">
                                    <option value="">请选择婚姻状况</option>
                                    <option value="未婚">未婚</option>
                                    <option value="已婚">已婚</option>
                                </select>
                            </div>
                            <div class="form-group">
                                <input type="submit" value="查询" class="btn btn-primary" style="margin-left:20px;width:80px;" />
                            </div>
                        </form>
                    </div>
                </div>
            </div>
        </div>
        <div class="row">
            <div class="col-md-12">
                <table id="table"></table>
            </div>
        </div>
    </div>

    <script>
        // 初始化时间表单
        $('#birthOfDate').datetimepicker({
            format: 'yyyy-mm-dd',
            weekStart: 1,
            autoclose: true,
            startView: 2,
            minView: 2,
            forceParse: false,
            language: 'zh-CN',
            todayBtn: true
        });

        // 初始化表格
        $('#table').bootstrapTable({
            url: "Handlers/SearchHandler.ashx",                    // URL
            method: "post",                                        // 请求类型
            contentType: "application/x-www-form-urlencoded",      // post请求必须要有，否则后台接受不到参数
            sidePagination: "server",                              // 设置在服务端还是客户端分页
            showRefresh: false,                                    // 是否刷新按钮
            sortStable: false,                                     // 是否支持排序
            cache: false,                                          // 是否使用缓存
            pagination: true,                                      // 是否显示分页
            search: false,                                         // 是否有搜索框
            clickToSelect: true,                                   // 是否点击选中行
            pageNumber: 1,                                         // 首页页码，默认为1
            pageSize: 5,                                           // 页面数据条数
            pageList: [5, 10, 20],
            queryParamsType: "",
            queryParams: function (params) {
                return {
                    pageIndex: params.pageNumber,                  // 当前页索引
                    pageSize: params.pageSize,                     // 每页记录条数
                    name: $('#name').val(),                        // 姓名
                    gender: $('#gender').val(),                    // 性别
                    birthOfDate: $('#birthOfDate').val(),          // 出生日期
                    maritalStatus: $('#maritalStatus').val()       // 婚姻状况
                };
            },
            columns: [
                {
                    field: 'Id',
                    title: '编号',
                    align: "center",
                    halign: "center"
                },
                {
                    field: 'Name',
                    title: '姓名',
                    align: "center",
                    halign: "center"
                },
                {
                    field: 'Gender',
                    title: '性别',
                    align: "center",
                    halign: "center"
                },
                {
                    field: 'BirthOfDate',
                    title: '出生日期',
                    align: "center",
                    halign: "center"
                },
                {
                    field: 'MaritalStatus',
                    title: '婚姻状况',
                    align: "center",
                    halign: "center"
                }
            ]
        });

        // 查询
        function search() {
            $('#table').bootstrapTable('refresh', { pageNumber: 1 });
            return false;
        }
    </script>
</body>
</html>

运行结果如下所示：