你的位置:首页 > ASP.net教程

[ASP.net教程]自动生成 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="width: 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           else129           {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           else145           {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           else157           {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           else169           {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           else181           {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           else193           {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           else237           {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           else250           {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             else319             {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             else331             {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             else346             {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             else359             {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         else36         {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 });
}