如下图查询页面，跟据不同条件动态生成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>
查询控件上的 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 }
这一套自动查询支持对应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 }
后台页面调用：
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 });
}