如下图查询页面，跟据不同条件动态生成lambda的Where条件和OrderBy,如果要增加或调整查询，只用改前台HTML即可，不用改后台代码
前台代码：
<div style="padding-bottom: 5px;" id="queryForm">
<span>员工姓名：</span><input type="text" emptytext="ddd" data-options="{match:'in'}" class="mini-textbox" id="Age" />
<span>部门：</span><input type="text" class="mini-textbox" data-options="{match:'like'}" id="Sex" />
生日从 <input id="beg" format="yyyy-MM-dd" showokbutton="false" showclearbutton="true" data-options="{match:'from',innerkey:'Birthday'}" class="mini-datepicker" />
至 <input id="end" format="yyyy-MM-dd" showokbutton="false" showclearbutton="true" data-options="{match:'to',innerkey:'Birthday'}" class="mini-datepicker" />
<span>年龄从：</span><input type="text" emptytext="ddd" data-options="{match:'from',innerkey:'Age'}" class="mini-textbox" id="bb" />
<span>至：</span><input type="text" class="mini-textbox" data-options="{match:'to',innerkey:'Age'}" id="bd" />
<a class="mini-button" iconcls="icon-search" onclick="search()">查找</a>
</div>
<div id="datagrid1" ondrawcell="Link" onupdate="load" class="mini-datagrid" style="width: 100%; height: 100%;" allowresize="true"
idfield="Id" sortfield="Age" showpager="false" pagesize="-1" sizelist="[-1]" sortorder="asc" multiselect="true">
<div property="columns">
<!--<div type="indexcolumn"></div> -->
<div type="checkcolumn"></div>
<div field="UserName" data-options="{Func:'test'}" width="120" headeralign="center" allowsort="true">姓名</div>
<div field="Sex" renderer="SexShow" width="120" headeralign="center" allowsort="true">性别</div>
<div field="LoginName" width="120">登录名</div>
<div field="Password" width="120">密码</div>
<div field="Birthday" width="100">生日</div>
<div field="Age" width="100" allowsort="true">年龄</div>
<div field="Remark" align="right" width="100">备注</div>
<div field="Married" renderer="MarriedShow" width="100">婚否</div>
</div>
</div>
查询控件上的 data-options="{match:'from',innerkey:'Birthday'}" 后多个查询条件会组合成一个json数组，传到后台，反序列化成List<QueryItem>,
排序条件Jquery 的Grid控件也会传到后台的，反序列化成SortItem，分页信息 反序列化成 Pager，详见后台代码。
上后台代码：比较复杂
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using App.Core;
using System.Reflection;
using System.Linq.Expressions;
using System.Data.Entity;
using System.Collections.Specialized;
namespace App.PageBase.Query
{
//查询条件模型
public class QueryItem
{
public string match { get; set; }
public string value { get; set; }
public string key { get; set; }
public string innerkey { get; set; } }
//排序模型
public class SortItem
{ public string sortField { get; set; }
public string sortOrder { get; set; }
}
//列表分页模型
public class Pager
{
public int pageSize
{
get;
set;
}
public int pageIndex
{
get;
set;
}
public int totalCount
{
get;
set;
} } //生成查询条件类
public class QueryBulider<TEntity> where TEntity : class,new()
{
private Pager _pager;// = new Pager();
private SortItem _sort;// = new SortItem();
private List<QueryItem> _queryItems;//= new List<QueryItem>();
public Pager pager
{
get { return _pager; }
set { _pager = value; }
}
public SortItem sort
{
get { return _sort; }
set { _sort = value; }
}
public List<QueryItem> queryItems
{
get { return _queryItems; }
set { _queryItems = value; }
}
/// <summary>
/// 根据 分页模型，排序模型，查询条件模型列表构造 自动查询生成器
/// </summary>
/// <param name="pager"></param>
/// <param name="sort"></param>
/// <param name="queryItems"></param>
public QueryBulider(Pager pager, SortItem sort, List<QueryItem> queryItems)
{
this.pager = pager;
this.sort = sort;
this.queryItems = queryItems; }
public QueryBulider()
{ } /// <summary>
/// 根据HTTP实例化
/// </summary>
/// <param name="requstForm"></param>
public QueryBulider(NameValueCollection requstForm)
{
string filter = requstForm["filter"];
string pageIndex = requstForm["pageIndex"];
string pageSize = requstForm["pageSize"];
string sortField = requstForm["sortField"];
string sortOrder = requstForm["sortOrder"]; if (!string.IsNullOrEmpty(pageSize) && !string.IsNullOrEmpty(pageIndex)&&pageSize!="-1")
{
this.pager = new Pager { pageIndex = int.Parse( pageIndex), pageSize = int.Parse( pageSize) };
}
if (!string.IsNullOrEmpty(sortField))
{
this.sort = new SortItem { sortField = sortField, sortOrder = sortOrder };
}
if (!string.IsNullOrEmpty(filter))
{
this.queryItems = JsonHelper.Json2Object<List<QueryItem>>(filter);
} }
//生成常量表达式
private ConstantExpression GetValueConstant(string value, Type type)
{
string typeName = type.Name.ToLower();
ConstantExpression rtn = null;
switch (typeName)
{
case "int32":
int intValue;
if (!int.TryParse(value, out intValue))
{
rtn = Expression.Constant(false);
}
else
{
rtn = Expression.Constant(intValue); }
break;
case "string": rtn = Expression.Constant(value);
break;
case "float":
float fValue;
if (!float.TryParse(value, out fValue))
{
rtn = Expression.Constant(false);
}
else
{
rtn = Expression.Constant(fValue); }
break;
case "single":
Single sgValue;
if (!Single.TryParse(value, out sgValue))
{
rtn = Expression.Constant(false);
}
else
{
rtn = Expression.Constant(sgValue); }
break;
case "decimal":
decimal dcValue;
if (!decimal.TryParse(value, out dcValue))
{
rtn = Expression.Constant(false);
}
else
{
rtn = Expression.Constant(dcValue); }
break;
case "double":
double dbValue;
if (!double.TryParse(value, out dbValue))
{
rtn = Expression.Constant(false);
}
else
{
rtn = Expression.Constant(dbValue); }
break;
case "datetime":
DateTime dateValue;
if (!DateTime.TryParse(value, out dateValue))
{
rtn = Expression.Constant(false);
}
else
{
rtn = Expression.Constant(dateValue); }
break; default:
rtn = Expression.Constant(false);
break; }
return rtn; } //生成列表常量表达式 实现 In ('a','b')
private ConstantExpression GetValueListConstant(string value, Type type)
{
string typeName = type.GenericTypeArguments.Length == ? type.Name : type.GenericTypeArguments[].Name;
ConstantExpression rtn = null;
switch (typeName.ToLower())
{
case "int32":
int intValue;
string[] arrInt = value.Split(',');
List<int> dlInt = new List<int>();
foreach (string a in arrInt)
{
if (int.TryParse(a, out intValue))
{
dlInt.Add(intValue);
} }
if (dlInt.Count == )
{
rtn = Expression.Constant(false); }
else
{
rtn = Expression.Constant(dlInt);
} break;
case "string":
List<string> dlStr = value.Split(',').ToList();
if (dlStr.Count == )
{
rtn = Expression.Constant(false); }
else
{
rtn = Expression.Constant(dlStr);
}
break; default:
rtn = Expression.Constant(false);
break; }
return rtn; } /// <summary>
/// 根据前台查询字段自动生成Lambad(支持=，like,between,in 查询)
/// </summary>
/// <returns></returns>
public Expression<Func<TEntity, bool>> BulidWhere()
{
Type type = typeof(TEntity);
string key = "";
string value = "";
string match = "";
string innerkey = "";
ParameterExpression instance = Expression.Parameter(type);
BinaryExpression result = Expression.Equal(Expression.Constant(true), Expression.Constant(true));
if (queryItems == null) return Expression.Lambda<Func<TEntity, bool>>(result, instance);
foreach (var item in queryItems)
{
key = item.key; value = item.value;//.ToLower();
if (string.IsNullOrEmpty(value)) continue;
match = item.match.ToLower();
innerkey = !string.IsNullOrEmpty(item.innerkey) ? item.innerkey : key;
PropertyInfo propertyInfo = type.GetProperty(innerkey);
var proFullType = propertyInfo.PropertyType;
Type propertyType = proFullType.GenericTypeArguments.Length == ? proFullType : proFullType.GenericTypeArguments[];
var valueExpression = match == "in" ? this.GetValueListConstant(value, propertyType) : this.GetValueConstant(value, propertyType);
MemberExpression propertyExpression = Expression.Property(instance, propertyInfo);
if (proFullType.Name.Contains("Nullable"))
{
propertyExpression = Expression.Property(propertyExpression, "Value");
}
var falseExpression = (Expression)Expression.Constant(false);
if (valueExpression.Value.ToString() == "False")
{ result = Expression.And(result, falseExpression);
continue;
}
switch (match)
{
case "=":
result = Expression.And(result, Expression.Equal(propertyExpression, valueExpression));
break;
case "like":
if (propertyType == typeof(string))
{
var like = Expression.Call(propertyExpression, typeof(string).GetMethod("Contains"), valueExpression);
result = Expression.And(result, like);
}
else
{
result = Expression.And(result, falseExpression); }
break;
case "in":
if (propertyType == typeof(string) || propertyType == typeof(Int32))
{
var inExp = Expression.Call(valueExpression, valueExpression.Type.GetMethod("Contains", new Type[] { propertyType }), propertyExpression);
result = Expression.And(result, inExp);
}
else
{
result = Expression.And(result, falseExpression); } break;
case "from": if (propertyType.IsValueType)
{ var from = Expression.GreaterThanOrEqual(propertyExpression, valueExpression);
result = Expression.And(result, from);
}
else
{
result = Expression.And(result, falseExpression); }
break;
case "to":
if (propertyType.IsValueType)
{ var from = Expression.LessThanOrEqual(propertyExpression, valueExpression);
result = Expression.And(result, from);
}
else
{
result = Expression.And(result, falseExpression); }
break;
}
}
var lambda = Expression.Lambda<Func<TEntity, bool>>(result, instance);
return lambda; }
}
}
这一套自动查询支持对应sql的 In,=,Like ,Between 查询，原理上也支持Not Like, Not In但是一般给用户的查询不会用到这些查询。
扩展EF框架的 DBSet：
namespace App.PageBase.Query
{
// 摘要:
// 提供一组用于查询实现 System.Linq.IQueryable<T> 的数据结构的 static（在 Visual Basic 中为 Shared）方法。
public static class DbSet
{
/// <summary>
/// 按QueryBulider自动生成 过滤，排序,分页
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="qb"></param>
/// <param name="query"></param>
/// <returns></returns>
public static IQueryable<T> Query<T>(this DbSet<T> qb, QueryBulider<T> query) where T : class,new()
{ var IQ = qb.Where(query.BulidWhere());
if (query.sort == null && query.pager != null)
{
throw new Exception("列表分页时必须指定排序字段");
} Type type = typeof(T);
var callWhere = IQ.Expression;
if (query.sort != null)
{
var sortFieldProperty = type.GetProperty(query.sort.sortField);
var instance =Expression.Parameter(type);
var sortFieldExpression = Expression.Property(instance, sortFieldProperty);
string OrderName = query.sort.sortOrder;
if (OrderName.ToLower() == "desc")
{
OrderName = "OrderByDescending";
}
else
{
OrderName = "OrderBy";
} Expression.Lambda(sortFieldExpression, instance);
callWhere = Expression.Call(typeof(Queryable), OrderName, new Type[] { type, sortFieldProperty.PropertyType }, callWhere, Expression.Lambda(sortFieldExpression, instance));
}
if (query.pager != null)
{
IQ = IQ.Provider.CreateQuery<T>(callWhere).Skip(query.pager.pageIndex * query.pager.pageSize).Take(query.pager.pageSize);
} return IQ; } }
后台页面调用：
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 });
}