自动生成 Lambda查询和排序，从些查询列表so easy

如下图查询页面，跟据不同条件动态生成lambda的Where条件和OrderBy,如果要增加或调整查询，只用改前台HTML即可，不用改后台代码

前台代码：

 1     <div style="padding-bottom: 5px;" id="queryForm">
 2 
 3         <span>员工姓名：</span><input type="text" emptytext="ddd" data-options="{match:'in'}" class="mini-textbox" id="Age" />
 4         <span>部门：</span><input type="text" class="mini-textbox" data-options="{match:'like'}" id="Sex" />
 5 
 6         生日从  <input id="beg" format="yyyy-MM-dd" showokbutton="false" showclearbutton="true" data-options="{match:'from',innerkey:'Birthday'}" class="mini-datepicker" />
 7         至  <input id="end" format="yyyy-MM-dd" showokbutton="false" showclearbutton="true" data-options="{match:'to',innerkey:'Birthday'}" class="mini-datepicker" />
 8 
 9         <span>年龄从：</span><input type="text" emptytext="ddd" data-options="{match:'from',innerkey:'Age'}" class="mini-textbox" id="bb" />
10         <span>至：</span><input type="text" class="mini-textbox" data-options="{match:'to',innerkey:'Age'}" id="bd" />
11 
12         <a class="mini-button" iconcls="icon-search" onclick="search()">查找</a>
13 
14 
15     </div>  
16 
17 
18 <div id="datagrid1" ondrawcell="Link" onupdate="load" class="mini-datagrid" style=" 100%; height: 100%;" allowresize="true"
19              idfield="Id" sortfield="Age" showpager="false" pagesize="-1" sizelist="[-1]" sortorder="asc" multiselect="true">
20             <div property="columns">
21                 <!--<div type="indexcolumn"></div>        -->
22                 <div type="checkcolumn"></div>
23                 <div field="UserName" data-options="{Func:'test'}" width="120" headeralign="center" allowsort="true">姓名</div>
24                 <div field="Sex" renderer="SexShow" width="120" headeralign="center" allowsort="true">性别</div>
25 
26                 <div field="LoginName" width="120">登录名</div>
27                 <div field="Password" width="120">密码</div>
28                 <div field="Birthday" width="100">生日</div>
29                 <div field="Age" width="100" allowsort="true">年龄</div>
30                 <div field="Remark" align="right" width="100">备注</div>
31 
32 
33                 <div field="Married" renderer="MarriedShow" width="100">婚否</div>
34 
35             </div>
36         </div>

View Code

查询控件上的 data-options="{match:'from',innerkey:'Birthday'}" 后多个查询条件会组合成一个json数组，传到后台，反序列化成List<QueryItem>,
排序条件Jquery 的Grid控件也会传到后台的，反序列化成SortItem，分页信息 反序列化成 Pager，详见后台代码。



上后台代码：比较复杂

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Threading.Tasks;
  6 using App.Core;
  7 using System.Reflection;
  8 using System.Linq.Expressions;
  9 using System.Data.Entity;
 10 using System.Collections.Specialized;
 11 namespace App.PageBase.Query
 12 {
 13     //查询条件模型
 14     public class QueryItem 
 15     {
 16         public string match { get; set; }
 17         public string value { get; set; }
 18         public string key { get; set; }
 19         public string innerkey { get; set; }
 20 
 21     }
 22     //排序模型
 23     public class SortItem
 24     {
 25 
 26         public string sortField { get; set; }
 27         public string sortOrder { get; set; }
 28     }
 29     //列表分页模型
 30     public class Pager
 31     {
 32         public int pageSize
 33         {
 34             get;
 35             set;
 36         }
 37         public int pageIndex
 38         {
 39             get;
 40             set;
 41         }
 42         public int totalCount
 43         {
 44             get;
 45             set;
 46         }
 47 
 48     }
 49 
 50 
 51     //生成查询条件类
 52     public class QueryBulider<TEntity> where TEntity : class,new()
 53     {
 54         private Pager _pager;// = new Pager();
 55         private SortItem _sort;// = new SortItem();
 56         private List<QueryItem> _queryItems;//= new List<QueryItem>();
 57         public Pager pager
 58         {
 59             get { return _pager; }
 60             set { _pager = value; }
 61         }
 62         public SortItem sort
 63         {
 64             get { return _sort; }
 65             set { _sort = value; }
 66         }
 67         public List<QueryItem> queryItems
 68         {
 69             get { return _queryItems; }
 70             set { _queryItems = value; }
 71         }
 72         /// <summary>
 73         /// 根据 分页模型，排序模型，查询条件模型列表构造 自动查询生成器
 74         /// </summary>
 75         /// <param name="pager"></param>
 76         /// <param name="sort"></param>
 77         /// <param name="queryItems"></param>
 78         public QueryBulider(Pager pager, SortItem sort, List<QueryItem> queryItems)
 79         {
 80             this.pager = pager;
 81             this.sort = sort;
 82             this.queryItems = queryItems;
 83 
 84 
 85         }
 86         public QueryBulider()
 87         { }
 88     
 89         /// <summary>
 90         /// 根据HTTP实例化
 91         /// </summary>
 92         /// <param name="requstForm"></param>
 93         public QueryBulider(NameValueCollection requstForm)
 94         {
 95             string filter = requstForm["filter"];
 96             string pageIndex = requstForm["pageIndex"];
 97             string pageSize = requstForm["pageSize"];
 98             string sortField = requstForm["sortField"];
 99             string sortOrder = requstForm["sortOrder"];
100 
101             if (!string.IsNullOrEmpty(pageSize) && !string.IsNullOrEmpty(pageIndex)&&pageSize!="-1")
102             {
103                 this.pager = new Pager { pageIndex = int.Parse( pageIndex), pageSize = int.Parse( pageSize) };
104             }
105             if (!string.IsNullOrEmpty(sortField))
106             {
107                 this.sort = new SortItem { sortField = sortField, sortOrder = sortOrder };
108             }
109             if (!string.IsNullOrEmpty(filter))
110             {
111                 this.queryItems = JsonHelper.Json2Object<List<QueryItem>>(filter);
112             }
113            
114         }
115         //生成常量表达式
116         private ConstantExpression GetValueConstant(string value, Type type)
117         {
118             string typeName = type.Name.ToLower();
119             ConstantExpression rtn = null;
120             switch (typeName)
121             {
122                 case "int32":
123                     int intValue;
124                     if (!int.TryParse(value, out intValue))
125                     {
126                         rtn = Expression.Constant(false);
127                     }
128                     else
129                     {
130                         rtn = Expression.Constant(intValue);
131 
132                     }
133                     break;
134                 case "string":
135 
136                     rtn = Expression.Constant(value);
137                     break;
138                 case "float":
139                     float fValue;
140                     if (!float.TryParse(value, out fValue))
141                     {
142                         rtn = Expression.Constant(false);
143                     }
144                     else
145                     {
146                         rtn = Expression.Constant(fValue);
147 
148                     }
149                     break;
150                 case "single":
151                     Single sgValue;
152                     if (!Single.TryParse(value, out sgValue))
153                     {
154                         rtn = Expression.Constant(false);
155                     }
156                     else
157                     {
158                         rtn = Expression.Constant(sgValue);
159 
160                     }
161                     break;
162                 case "decimal":
163                     decimal dcValue;
164                     if (!decimal.TryParse(value, out dcValue))
165                     {
166                         rtn = Expression.Constant(false);
167                     }
168                     else
169                     {
170                         rtn = Expression.Constant(dcValue);
171 
172                     }
173                     break;
174                 case "double":
175                     double dbValue;
176                     if (!double.TryParse(value, out dbValue))
177                     {
178                         rtn = Expression.Constant(false);
179                     }
180                     else
181                     {
182                         rtn = Expression.Constant(dbValue);
183 
184                     }
185                     break;
186                 case "datetime":
187                     DateTime dateValue;
188                     if (!DateTime.TryParse(value, out dateValue))
189                     {
190                         rtn = Expression.Constant(false);
191                     }
192                     else
193                     {
194                         rtn = Expression.Constant(dateValue);
195 
196                     }
197                     break;
198 
199                 default:
200                     rtn = Expression.Constant(false);
201                     break;
202 
203 
204 
205 
206             }
207             return rtn;
208 
209         }
210 
211 
212         //生成列表常量表达式  实现 In ('a','b') 
213         private ConstantExpression GetValueListConstant(string value, Type type)
214         {
215             string typeName = type.GenericTypeArguments.Length == 0 ? type.Name : type.GenericTypeArguments[0].Name;
216             ConstantExpression rtn = null;
217             switch (typeName.ToLower())
218             {
219                 case "int32":
220                     int intValue;
221                     string[] arrInt = value.Split(',');
222                     List<int> dlInt = new List<int>();
223                     foreach (string a in arrInt)
224                     {
225                         if (int.TryParse(a, out intValue))
226                         {
227                             dlInt.Add(intValue);
228                         }
229 
230                     }
231                     if (dlInt.Count == 0)
232                     {
233                         rtn = Expression.Constant(false);
234 
235                     }
236                     else
237                     {
238                         rtn = Expression.Constant(dlInt);
239                     }
240 
241                     break;
242                 case "string":
243                     List<string> dlStr = value.Split(',').ToList();
244                     if (dlStr.Count == 0)
245                     {
246                         rtn = Expression.Constant(false);
247 
248                     }
249                     else
250                     {
251                         rtn = Expression.Constant(dlStr);
252                     }
253                     break;
254 
255 
256                 default:
257                     rtn = Expression.Constant(false);
258                     break;
259 
260 
261 
262 
263             }
264             return rtn;
265 
266         }
267 
268 
269         /// <summary>
270         /// 根据前台查询字段自动生成Lambad(支持=，like,between,in 查询)
271         /// </summary>
272         /// <returns></returns>
273         public Expression<Func<TEntity, bool>> BulidWhere()
274         {
275             Type type = typeof(TEntity);
276             string key = "";
277             string value = "";
278             string match = "";
279             string innerkey = "";
280             ParameterExpression instance = Expression.Parameter(type);
281             BinaryExpression result = Expression.Equal(Expression.Constant(true), Expression.Constant(true));
282             if (queryItems == null) return Expression.Lambda<Func<TEntity, bool>>(result, instance);
283             foreach (var item in queryItems)
284             {
285                 key = item.key;
286 
287                 value = item.value;//.ToLower();
288                 if (string.IsNullOrEmpty(value)) continue;
289                 match = item.match.ToLower();
290                 innerkey = !string.IsNullOrEmpty(item.innerkey) ? item.innerkey : key;
291                 PropertyInfo propertyInfo = type.GetProperty(innerkey);
292                 var proFullType = propertyInfo.PropertyType;
293                 Type propertyType = proFullType.GenericTypeArguments.Length == 0 ? proFullType : proFullType.GenericTypeArguments[0];
294                 var valueExpression = match == "in" ? this.GetValueListConstant(value, propertyType) : this.GetValueConstant(value, propertyType);
295                 MemberExpression propertyExpression = Expression.Property(instance, propertyInfo);
296                 if (proFullType.Name.Contains("Nullable"))
297                 {
298                     propertyExpression = Expression.Property(propertyExpression, "Value");
299                 }
300                 var falseExpression = (Expression)Expression.Constant(false);
301                 if (valueExpression.Value.ToString() == "False")
302                 {
303 
304                     result = Expression.And(result, falseExpression);
305                     continue;
306                 }
307                 switch (match)
308                 {
309                     case "=":
310                         result = Expression.And(result, Expression.Equal(propertyExpression, valueExpression));
311                         break;
312                     case "like":
313                         if (propertyType == typeof(string))
314                         {
315                             var like = Expression.Call(propertyExpression, typeof(string).GetMethod("Contains"), valueExpression);
316                             result = Expression.And(result, like);
317                         }
318                         else
319                         {
320                             result = Expression.And(result, falseExpression);
321 
322                         }
323                         break;
324                     case "in":
325                         if (propertyType == typeof(string) || propertyType == typeof(Int32))
326                         {
327                             var inExp = Expression.Call(valueExpression, valueExpression.Type.GetMethod("Contains", new Type[] { propertyType }), propertyExpression);
328                             result = Expression.And(result, inExp);
329                         }
330                         else
331                         {
332                             result = Expression.And(result, falseExpression);
333 
334                         }
335 
336                         break;
337                     case "from":
338 
339                         if (propertyType.IsValueType)
340                         {
341 
342                             var from = Expression.GreaterThanOrEqual(propertyExpression, valueExpression);
343                             result = Expression.And(result, from);
344                         }
345                         else
346                         {
347                             result = Expression.And(result, falseExpression);
348 
349                         }
350                         break;
351                     case "to":
352                         if (propertyType.IsValueType)
353                         {
354 
355                             var from = Expression.LessThanOrEqual(propertyExpression, valueExpression);
356                             result = Expression.And(result, from);
357                         }
358                         else
359                         {
360                             result = Expression.And(result, falseExpression);
361 
362                         }
363                         break;
364                 }
365             }
366             var lambda = Expression.Lambda<Func<TEntity, bool>>(result, instance);
367             return lambda;
368 
369         }
370     }
371 }

View Code

这一套自动查询支持对应sql的 In,=,Like ,Between 查询，原理上也支持Not Like, Not In但是一般给用户的查询不会用到这些查询。

扩展EF框架的 DBSet：

 1 namespace App.PageBase.Query
 2 {
 3     // 摘要: 
 4     //     提供一组用于查询实现 System.Linq.IQueryable<T> 的数据结构的 static（在 Visual Basic 中为 Shared）方法。
 5     public static class DbSet
 6     {
 7         /// <summary>
 8         /// 按QueryBulider自动生成 过滤，排序,分页
 9         /// </summary>
10         /// <typeparam name="T"></typeparam>
11         /// <param name="qb"></param>
12         /// <param name="query"></param>
13         /// <returns></returns>
14         public static IQueryable<T> Query<T>(this DbSet<T> qb, QueryBulider<T> query) where T : class,new()
15         {
16 
17             var IQ = qb.Where(query.BulidWhere());
18             if (query.sort == null && query.pager != null)
19             {
20                 throw new Exception("列表分页时必须指定排序字段");
21             }
22 
23             Type type = typeof(T);
24             var callWhere = IQ.Expression;
25             if (query.sort != null)
26             {
27                 var sortFieldProperty = type.GetProperty(query.sort.sortField);
28                 var instance =Expression.Parameter(type);
29                 var sortFieldExpression = Expression.Property(instance, sortFieldProperty);
30                 string OrderName = query.sort.sortOrder;
31                 if (OrderName.ToLower() == "desc")
32                 {
33                     OrderName = "OrderByDescending";
34                 }
35                 else
36                 {
37                     OrderName = "OrderBy";
38                 }
39 
40                 Expression.Lambda(sortFieldExpression, instance);
41                 callWhere = Expression.Call(typeof(Queryable), OrderName, new Type[] { type, sortFieldProperty.PropertyType }, callWhere, Expression.Lambda(sortFieldExpression, instance));
42             }
43             if (query.pager != null)
44             {
45                 IQ = IQ.Provider.CreateQuery<T>(callWhere).Skip(query.pager.pageIndex * query.pager.pageSize).Take(query.pager.pageSize);
46             }
47 
48             return IQ;
49 
50         }
51 
52 
53     }

View Code

后台页面调用：

var queryBulider = new QueryBulider<UserInfo>(Request.Form);

var dl = db.Set<UserInfo>().Query(queryBulider);

当然前台js组件不同，前台的封装就不一样。MiniUi我是这样弄的。

///组合查询条件

function GetQueryFormData(formId) {
var data = [];
if (!formId) formId = "queryForm";
var form = new mini.Form("#" + formId);
var fields = form.getFields();
var arr = [];
for (var i = 0; i < fields.length; i++) {
var item = {};
item["key"] = fields[i].id;
item["value"] = fields[i].value;
item["match"] = fields[i].match;
item["innerkey"] = fields[i].innerkey;
arr.push(item);
}

return arr;
}

///查询事件

function search() {
var grid = mini.get("datagrid1");
var query = GetQueryFormData();
var json = mini.encode(query);
grid.load({ filter: json });
}