本人写的3个有关Linq to Entity 动态查询的例子

  那二日一直想写三个动态查询的法门,先是网上查询了一晃,发现大家写的大半都以同样的【如:http://www.cnblogs.com/ASPNET2008/archive/2012/10/28/2743053.html#commentformhttp://www.cnblogs.com/lyj/archive/2008/03/25/1122157.html】,我觉得那里倒霉的地点就是在询问的时候照旧要领会查询的是哪二个列,然后依照这几个查询的列是或不是为空等等的一部分断定来拓展动态查询的拼凑,

那样一来,每二个实体的查询都要对查询字段和数据开展逐项判断,如首先篇例子里面包车型客车

 if (planCondition.Project != 0) { predicate = predicate.And(c => c.ProjectId == planCondition.Project); }

此间在查询时首先要理解查询的是【ProjectId】字段,然后再依据此字段是还是不是为空来进行查询的拼凑,作者觉得那样就很不灵敏了。所以笔者就准备自个儿写叁个。

          笔者觉着动态是那样的:查询的实体是动态的,查询的列是动态的,查询的数目是动态的,查询的办法[如:OTiggo依旧And查询,EQUAL查询依旧Like查询]。最注重的某个正是动态查询的艺术要灵活并且通用。

因此就协调尝尝着写了2个情势,由于对Expression的垂询很有限,因而在中间也是一再的尝尝。刚伊始兑现的时候尝试了其余的部分主意,最终都因为动态列的品类未知而败诉。但是最后还是促成了

上面是2个结合EasyUI 的三个例子:

页面数据:

图片 1

页面查询的是一个【DataDictionary】实体的多寡,查询的数据列是图上的那3个(随着供给随时能够更改),点击【查询】按钮列表绑定查询的值。前台的的询问控件

    <table>
        <tr>
            <td>所属类型:</td>
            <td>
                <select id="SelParentID" name="SelParentID" style="width: 130px;"></select>
            </td>
            <td>字典名称:</td>
            <td>
                <input type="text" id="txtDataText" />
            </td>
            <td>序号:</td>
            <td>
                <input type="text" id="txtSortNumt" />
            </td>
            <td><a href="#" class="easyui-linkbutton" data-options="iconCls:'icon-search'" id="btnSearch" plain="true">查询</a></td>
        </tr>
    </table>

  按钮事件:

      //查询
        $("#btnSearch").click(function () {
            var jdata = { "[EQUAL][And]ParentID": 1, "[LIKE][And]DataText": "abc", "[EQUAL][And]SortNum": 789};
            $("#DataDicList").datagrid("load", { queryJson: JSON.stringify(jdata) });  
        });

  

  这里就是运用的方法

1    var jdata = { "[EQUAL][And]ParentID": $('#SelParentID').combobox("getValue"), "[LIKE][And]DataText": $("#txtDataText").val(), "[EQUAL][And]SortNum": $("#txtSortNumt").val() };  

 

在页面只要求内定询问的主意,查询连接的法门,查询字段和字段的值,后台调用的时候再加上要询问的实业,动态查询艺术本身去分析就行了,直接把分析的Lambda返给笔者,小编拿着他去数据库取多少就行了。

 

接下去看一下后台,加载列表数据的法子:

   public ActionResult GetAll(int page, int rows, string value, string queryJson)
        {
            var query = SpecificationBuilder.Create<DataDictionary>();
            query.Equals(t => t.DelFlag, 0);
            if (!string.IsNullOrEmpty(queryJson))
            {
                var predicate = HelpClass.GetSerchExtensions<DataDictionary>(queryJson);
                query.Predicate = query.Predicate.And(predicate);
            }
            var allCount = 0;
            var listModel = _dataDictionaryBLL.GetAllPage(query.Predicate, a => a.CreateDate, page, rows, out allCount);
            var dateInfo = "{\"total\":" + allCount + ",\"rows\":" + JsonConvert.SerializeObject(listModel) + "}";
            return Content(dateInfo);
        }

  这里

 var query = SpecificationBuilder.Create<DataDictionary>();

  是本身别的1个动态查询的例证【那里正是索要明白并钦命询问的列名和数据值】,先不用管,关键就只用这样的一句

   var predicate = HelpClass.GetSerchExtensions<DataDictionary>(queryJson);

  获得的正是1个一直能够查询的Lambda表明式【如:{a => (True And
(a.ParentID == 00000000-0000-0000-0000-000000000000))}】

上边就看一下以此点子的完成

  #region 把查询条件拼接为Extensions
        /// <summary>
        /// 把查询条件拼接为Extensions
        /// </summary>
        /// <typeparam name="TEntity">查询实体</typeparam>
        /// <param name="searchJson">查询条件,例如:[like][or]name:123</param>
        /// <returns></returns>
        public static Expression<Func<TEntity, bool>> GetSerchExtensions<TEntity>(String searchJson) where TEntity : class, new()
        {
            try
            {
                var ja = (JArray)JsonConvert.DeserializeObject("[" + searchJson + "]");                     //把查询条件转换为Json格式
                var enumerableQuery = new EnumerableQuery<KeyValuePair<string, JToken>>(ja[0] as JObject);  
                return GetSerchExtensions<TEntity>(enumerableQuery);
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// 把查询条件拼接为Extensions
        /// </summary>
        /// <typeparam name="TEntity">查询实体</typeparam>
        /// <param name="enumerableQuery"></param>
        /// <returns></returns>
        public static Expression<Func<TEntity, bool>> GetSerchExtensions<TEntity>(EnumerableQuery<KeyValuePair<string, JToken>> enumerableQuery) where TEntity : class,new()
        {
            ParameterExpression paramExp = Expression.Parameter(typeof(TEntity), "a");
            if (null == enumerableQuery || !enumerableQuery.Any())
            {
                var valueEqual = Expression.Constant(1);
                var expEqual = Expression.Equal(valueEqual, valueEqual);
                return Expression.Lambda<Func<TEntity, bool>>(expEqual, paramExp);  //如果参数为空,返回一个a=>1=1 的值

            }
            var modeltypt = typeof(TEntity);  //实体类型
            var keyList = enumerableQuery.Select(e => e.Key).ToList();      //取出Json 的每个字符串

            Expression whereExp = null;
            keyList.ForEach(s =>
            {
                var searchTypeStr = s.Substring(1, s.LastIndexOf("][", StringComparison.Ordinal) - 1);   //查询方式   Like
                var ab = s.Substring(s.LastIndexOf("][", StringComparison.Ordinal) + 2);
                var joinTypeStr = ab.Remove(ab.LastIndexOf("]", StringComparison.Ordinal));              //连接方式   or
                var searchField = s.Substring(s.LastIndexOf("]", StringComparison.Ordinal) + 1);         //查询的列名 name 
                var value = enumerableQuery.FirstOrDefault(v => v.Key == s).Value.ToString();            //值         123

                var searchType = LogicOperation.LIKE;       //查询方式
                var joinType = PredicateType.AND;           //连接方式

                if (Enum.TryParse(searchTypeStr.ToUpper(), out searchType) && Enum.TryParse(joinTypeStr.ToUpper(), out joinType) && modeltypt.GetProperties().Any(p => String.Equals(p.Name, searchField,
 StringComparison.CurrentCultureIgnoreCase)))  //这个实体有这个列名
                {
                    var firstOrDefault = modeltypt.GetProperties().FirstOrDefault(p => String.Equals(p.Name, searchField, StringComparison.CurrentCultureIgnoreCase));
                    if (firstOrDefault != null)
                    {
                        var selCol = firstOrDefault.Name;  //查询的列名
                        var splitList = value.Split(',').ToList();
                        for (var i = 0; i < splitList.Count; i++)
                        {
                            var expressionFuncEquals = PrepareConditionLambda<TEntity>(selCol, splitList[i], paramExp, searchType); //得到这个查询的表达式
                            if (i != 0)  //累加
                            {
                                whereExp = whereExp == null ? expressionFuncEquals : Expression.Or(whereExp, expressionFuncEquals);
                            }
                            else
                            {
                                whereExp = joinType == PredicateType.OR ? (whereExp == null ? expressionFuncEquals : Expression.Or(whereExp, expressionFuncEquals)) : (whereExp == null ? 
expressionFuncEquals : Expression.And(whereExp, expressionFuncEquals));
                            }
                        }
                    }
                }
            });
            return Expression.Lambda<Func<TEntity, bool>>(whereExp, paramExp); ;
        }

        /// <summary>
        /// 得到字段查询的表达式
        /// </summary>
        /// <typeparam name="TEntity">实体</typeparam>
        /// <param name="name">查询列名</param>
        /// <param name="dateValue">数据值</param>
        /// <param name="paramExp">参数</param>
        /// <param name="searchType">查询方式(默认是等于查询)</param>
        /// <returns></returns>
        private static Expression PrepareConditionLambda<TEntity>(string name, object dateValue, ParameterExpression paramExp, LogicOperation searchType = LogicOperation.EQUAL)
        {
            if (dateValue == null) throw new ArgumentNullException("dateValue");
            var exp = Expression.Property(paramExp, name);
            var propertyType = typeof(TEntity).GetProperty(name).PropertyType; //得到此字段的数据类型
            var value = propertyType == typeof(Guid?) ? new Guid(dateValue.ToString()) : Convert.ChangeType(dateValue, TypeHelper.GetUnNullableType(propertyType));

            Expression expEqual = null;
            switch (searchType)
            {
                case LogicOperation.EQUAL:   //等于查询
                    var valueEqual = Expression.Constant(value, propertyType); //值
                    expEqual = Expression.Equal(exp, valueEqual); //拼接成 t=>t.name=valueEqual
                    break;
                case LogicOperation.LIKE:   //模糊查询
                    var containsMethod = typeof(string).GetMethod("Contains");
                    var valueLike = Expression.Constant(value, propertyType);
                    expEqual = Expression.Call(exp, containsMethod, valueLike);
                    break;
            }
            return expEqual;
        }
        #endregion

 /// <summary>
    /// 查询方式
    /// </summary>
    public enum PredicateType
    {
        AND, OR
    }

    /// <summary>
    /// 查询方式
    /// </summary>
    public enum SearchType
    {
        Between, Like, Equals
    }
  /// <summary>
    /// 查询方式
    /// </summary>
    public enum LogicOperation
    {
        LIKE,    //包含,模糊查询
        EQUAL,   //等于
        LT,      //小于
        GT,       //大于
        CONTAINS,       //包含,In查询 
        NOTEQUAL       //不等于 
    }

  在拼接查询时候有一个对查询数据值的分开

     var splitList = value.Split(',').ToList();

 查询办法当下先写完了那多少个,其余的方法实际都以相当粗略的了,一时并未用
,也就没有写。 

   
那样做是因为能够兑现多数据查询,例如在查询User的Name字段时,在Name文本框中输入【张三,李四】,那样就足以把”张三”和”李四”值都询问出来【关联是用的O翼虎】,由于地点有注释就不再详细表达了,在那边一是把那个方法和大家大饱眼福一下,在二个正是对友好这两日工作的三个计算,同时也视作工作笔记放在此处,何时遗忘了团结还足以拿出去看看。

那里须求选取的有【json.js】和【Newtonsoft.Json】

 补充:

  明日(2017-03-23)突然见到了投机之前写的那个小例子,想两次三番完善一下,顺便把代码提取出来。

  同时重载了GetSerchExtensions格局,直接解析两个目的,因为作者觉得以前封装的特别只帮助json字符串的询问不太融洽,还余下,因为是指标转json字符串,json字符串转JArray,显得很麻烦,还很不难出错。

 

图片 2图片 3

 1  /// <summary>
 2     /// 查询实体
 3     /// </summary>
 4     public class QueryEntity
 5     {
 6         /// <summary>
 7         /// 查询方式
 8         /// </summary>
 9         public LogicOperation LogicOperation { get; set; }
10 
11         /// <summary>
12         /// 连接方式
13         /// </summary>
14         public PredicateType PredicateType { get; set; }
15 
16         /// <summary>
17         /// 列名
18         /// </summary>
19         public string Column { get; set; }
20 
21         /// <summary>
22         /// 列值
23         /// </summary>
24         public object Value { get; set; }
25     }

View Code

 1        /// <summary>
 2         /// 把查询条件拼接为Extensions
 3         /// </summary>
 4         /// <typeparam name="TEntity">实体类</typeparam>
 5         /// <param name="queryEntitys">查询实体</param>
 6         /// <returns></returns>
 7         public static Expression<Func<TEntity, bool>> GetSerchExtensions<TEntity>(List<QueryEntity> queryEntitys) where TEntity : class, new()
 8         {
 9             var paramExp = Expression.Parameter(typeof(TEntity), "a");
10             if (null == queryEntitys || !queryEntitys.Any())
11             {
12                 var valueEqual = Expression.Constant(1);
13                 var expEqual = Expression.Equal(valueEqual, valueEqual);
14                 return Expression.Lambda<Func<TEntity, bool>>(expEqual, paramExp);  //如果参数为空,返回一个a=>1=1 的值
15 
16             }
17             var modeltypt = typeof(TEntity);  //实体类型
18             Expression whereExp = null;
19 
20             queryEntitys.ForEach(q =>
21             {
22                 LogicOperation searchType = q.LogicOperation;       //查询方式
23                 PredicateType joinType = q.PredicateType;           //连接方式
24                 var searchField = q.Column;         //查询的列名 name 
25                 var value = q.Value;            //值         123
26                 if (modeltypt.GetProperties().Any(p => String.Equals(p.Name, searchField, StringComparison.CurrentCultureIgnoreCase)))  //这个实体有这个列名
27                 {
28                     var firstOrDefault = modeltypt.GetProperties().FirstOrDefault(p => String.Equals(p.Name, searchField, StringComparison.CurrentCultureIgnoreCase));
29                     if (firstOrDefault == null) return;
30                     var selCol = firstOrDefault.Name;  //查询的列名
31                     var splitList = value.ToString().Split(',').ToList();   //这个位置是的处理是默认认为当查询值中包含,的视为或者的查询:例如 A='abc,def' 处理成 (A='def' OR  A='abc'),但是时间上这块无法满足就要查询包含,的数据的求
32                     for (var i = 0; i < splitList.Count; i++)
33                     {
34                         if (splitList[i] == null || string.IsNullOrWhiteSpace(splitList[i])) continue;
35                         var expressionFuncEquals = PrepareConditionLambda<TEntity>(selCol, splitList[i], paramExp, searchType); //得到这个查询的表达式
36                         whereExp = i != 0
37                             ? (whereExp == null ? expressionFuncEquals : Expression.Or(whereExp, expressionFuncEquals))
38                             : (joinType == PredicateType.OR ? (whereExp == null ? expressionFuncEquals : Expression.Or(whereExp, expressionFuncEquals))
39                                 : (whereExp == null ? expressionFuncEquals : Expression.And(whereExp, expressionFuncEquals)));
40                     }
41                 }
42             });
43             return Expression.Lambda<Func<TEntity, bool>>(whereExp, paramExp); ;
44         }

 

应用示例:

 1 static void Main(string[] args)
 2         {
 3             var query = SpecificationBuilder.Create<VWDepartment>();
 4             query.Equals(d => d.DeptDelFlag, 0);
 5             query.Equals(d => d.DeptParentID, Guid.NewGuid());
 6 
 7             #region 字符串
 8             string queryJson = "{\"[EQUAL][And]DeptParentID\":\"86EE21E7-81C2-49BC-B7D6-76E865DA1D3A\",\"[EQUAL][And]DeptName\":\"abc,ccccc\",\"[EQUAL][And]DeptSort\":789}";
 9 
10             if (!string.IsNullOrEmpty(queryJson))
11             {
12                 var predicate = Utils.GetSerchExtensions<VWDepartment>(queryJson);
13                 query.Predicate = query.Predicate.And(predicate);
14             }
15             #endregion
16 
17             #region 对象
18 
19             QueryEntity queryEntity = new QueryEntity
20             {
21                 LogicOperation = LogicOperation.EQUAL,
22                 PredicateType = PredicateType.AND,
23                 Column = "DeptParentID",
24                 Value = Guid.NewGuid()
25             };
26             var qqqqq = Utils.GetSerchExtensions<VWDepartment>(new List<QueryEntity>() { queryEntity });
27 
28 
29             var li = new List<QueryEntity>() { };
30             li.Add(new QueryEntity
31             {
32                 LogicOperation = LogicOperation.EQUAL,
33                 PredicateType = PredicateType.AND,
34                 Column = "DeptParentID",
35                 Value = Guid.NewGuid()
36             });
37 
38             li.Add(new QueryEntity
39             {
40                 LogicOperation = LogicOperation.EQUAL,
41                 PredicateType = PredicateType.AND,
42                 Column = "DeptSort",
43                 Value = 123
44             });
45             li.Add(new QueryEntity
46             {
47                 LogicOperation = LogicOperation.LIKE,
48                 PredicateType = PredicateType.AND,
49                 Column = "ParentDeptName",
50                 Value = "大爷"
51             });
52             qqqqq = Utils.GetSerchExtensions<VWDepartment>(li); 
53             #endregion
54         }

 

代码环境

win10 + Visual Studio Community 2017

代码下载