首先我们来看看一个页面
这里面有多选的条件,大于,小于等等,包括每个字段都有
如此多的查询条件,我们的后台该如何实现呢?
难道我们还得每个参数都去判断吗?
那得传多少参数进来才能实现这个页面的功能啊!
既然用了EF当然不能在用sql拼接了
那么我们就来看看这个页面是怎么实现的吧
首先我们来看看这个页面的参数是怎么传到后台的
这是获取查询条件的脚本
var filterRules = new Array();
$(op.toolbarid).find("input[type!='button'][data-disable!='true'],select[data-disable!='true']").each(function () {
var isadd = false;
var value = $(this).val();
if (this.type == "checkbox" || this.type == "radio") {
isadd = this.checked;
} else if (value) {
isadd = true;
}
if (isadd) {
var field = $(this).data("field");
if (!field) {
field = this.id;
}
var op = $(this).data("op");
var time_add = $(this).data("time-add");
if (time_add || $(this).data("time-today")) {
if (time_add) {
value = (new Date(Date.parse(value.replace(/-/g, "/")).getTime() + parseInt(time_add) * 86400000)).Format("yyyy-MM-dd");
} else {
var new_value = (new Date(Date.parse(value.replace(/-/g, "/")).getTime() + 86400000)).Format("yyyy-MM-dd");
filterRules.push({ group: $(this).data("group"), field: field, op: "<", split: $(this).data("split"), value: new_value });
op = ">=";
}
}
filterRules.push({ group: $(this).data("group"), field: field, op: op, split: $(this).data("split"), value: value });
}
});
options.filterRules = filterRules;
这是请求后台传递的参数
[{"field":"FILE_TYPE","op":"=","split":",","value":"CAD,AD"},
{"field":"WRITE_DATE","op":">=","value":"2011-01-01"},
{"field":"WRITE_DATE","op":"<","value":"2016-04-14"},
{"field":"STATUS","op":"=","split":",","value":"CLOSE,C/W"}]后台代码
return db.AWF_FILE_MAIN.Join(db.FLOW_TASK_INFO, d => d.FILE_NO, d => d.FILE_NO, (x, y) => new { ... })
.Where(EasyuiFilterRules.GetWPList(wm.filterRules), wm.filterRules_isand)
.ToPageJson(wm.page, wm.pageSize, wm.field, wm.order, DateTimeJson.Create(format: "yyyy-MM-dd"));关键代码
.Where(EasyuiFilterRules.GetWPList(wm.filterRules), wm.filterRules_isand)
接下来我们就来看看Where背后的秘密,
我们先来看看EasyuiFilterRules类
EasyuiFilterRules主要的功能就是把JSON字符串转换成WhereParameters对象
/// <summary>
/// Easyui搜索规则
/// </summary>
public class EasyuiFilterRules
{
/// <summary>
/// 字段名称
/// </summary>
public string field { get; set; } /// <summary>
/// 过滤类型,可选值有(contains-包含,equal-相等,notequal-不相等,beginwith-开头包含,endwith-结尾包含,less-小于,lessorequal-小于等于,greater-大于,greaterorequal-大于等于)
/// </summary>
public string op { get; set; } /// <summary>
/// 要搜索的值
/// </summary>
public string value { get; set; } /// <summary>
/// 组
/// </summary>
public int? group { get; set; } /// <summary>
/// 分隔符
/// </summary>
public string split { get; set; } /// <summary>
/// 方法
/// </summary>
public string fun { get; set; } /// <summary>
/// 获取过滤参数集合根据Easyui搜索规则字符串
/// </summary>
/// <param name="filterRules">Easyui搜索规则字符串</param>
/// <returns>过滤参数集合</returns>
public static List<WhereParameters> GetWPList(string filterRules)
{
var ps = new List<WhereParameters>();
if (string.IsNullOrEmpty(filterRules))
{
return ps;
}
var list = JsonConvert.DeserializeObject<List<EasyuiFilterRules>>(filterRules);
if (list.Count == )
{
return ps;
}
int index = -;
foreach (var item in list)
{
if (string.IsNullOrEmpty(item.value))
{
continue;
}
var names = item.field.Split(','); foreach (var name in names)
{
var values = item.value.Split(new string[]{item.split},StringSplitOptions.RemoveEmptyEntries);
foreach (var value in values)
{
var wp = new WhereParameters(value, name, group: item.group ?? index,fun:item.fun);
if (item.value == "is null")
{
wp.value = null;
wp.isnotnull = false;
wp.wherefun = WhereFun.Equal;
}
else if (item.value == "is not null")
{
wp.value = null;
wp.isnotnull = false;
wp.wherefun = WhereFun.NotEqual;
}
else
{
if (string.IsNullOrEmpty(item.op))
{
item.op = "contains";
}
switch (item.op.ToLower())
{
case "=":
case "==":
case "equal":
case "eq": wp.wherefun = WhereFun.Equal; break; case "!=":
case "neq":
case "notequal": wp.wherefun = WhereFun.NotEqual; break; case "<":
case "lt":
case "less": wp.wherefun = WhereFun.LessThan; break; case "<=":
case "lte":
case "lessorequal": wp.wherefun = WhereFun.LessThanOrEqual; break; case ">":
case "gt":
case "greater": wp.wherefun = WhereFun.GreaterThan; break; case ">=":
case "gte":
case "greaterorequal": wp.wherefun = WhereFun.GreaterThanOrEqual; break; case "!c":
case "doesnotcontain": wp.wherefun = WhereFun.NotContains; break; case "^c":
case "startswith": wp.wherefun = WhereFun.StartsWith; break; case "c$":
case "endswith": wp.wherefun = WhereFun.EndsWith; break; case "like": wp.wherefun = WhereFun.Like; break; case "notlike": wp.wherefun = WhereFun.NotLike; break; default: wp.wherefun = WhereFun.Contains; break;
}
}
ps.Add(wp);
}
}
index--;
}
return ps;
}
}
接着我们看看WhereHelper类
就是这个类让Where方法可以接受.Where(EasyuiFilterRules.GetWPList(wm.filterRules), wm.filterRules_isand)这样的参数
代码也是相对比较简单的
/// <summary>
/// 搜索条件辅助类
/// </summary>
public static class WhereHelper
{
/// <summary>
/// 根据条件过滤数据
/// </summary>
/// <typeparam name="model">指定的模型</typeparam>
/// <param name="mls">对象</param>
/// <param name="ps">过滤参数集合</param>
/// <param name="isand">如果为true则同一组的进行Or运算,不同组的进行And运算,否则同一组的进行And运算,不同组的进行Or运算</param>
/// <returns>对象</returns>
public static IQueryable<model> Where<model>(this IQueryable<model> mls, List<WhereParameters> ps, bool isand = true)
{
if (ps.Count == )
{
return mls;
}
return mls.Where(WhereEx<model>.Create(ps, isand).ToFun());
}
}
继续WhereParameters,这个类也只是用来保存一些数据信息,没什么逻辑
/// <summary>
/// 过滤参数
/// </summary>
public class WhereParameters
{
/// <summary>
/// 构造函数
/// </summary>
public WhereParameters()
{ } /// <summary>
/// 构造函数
/// </summary>
/// <param name="value">要匹配的值</param>
/// <param name="name">要匹配的字段名称</param>
/// <param name="wherefun">匹配方法</param>
/// <param name="isnotnull">值不为空才执行</param>
/// <param name="group">组</param>
/// <param name="fun">方法</param>
public WhereParameters(object value, string name, WhereFun wherefun = WhereFun.Contains, bool isnotnull = true, int group = ,string fun="")
{
this.value = value;
this.name = name;
this.wherefun = wherefun;
this.isnotnull = isnotnull;
this.group = group;
this.fun = fun;
} /// <summary>
/// 判断是否要添加查询条件
/// </summary>
/// <returns>是否</returns>
public bool IsAddWhere(Type type)
{
if (this.isnotnull && IsValueNull(type))
{
return false;
}
return true;
} /// <summary>
/// 判断值是否为空
/// </summary>
/// <returns></returns>
public bool IsValueNull(Type type)
{
return string.IsNullOrEmpty(Convert.ToString(this.value));
} /// <summary>
/// 要匹配的值
/// </summary>
public object value { get; set; } /// <summary>
/// 要匹配的字段名称
/// </summary>
public string name { get; set; } /// <summary>
/// 匹配方法
/// </summary>
public WhereFun wherefun { get; set; } /// <summary>
/// 值不为空才执行
/// </summary>
public bool isnotnull { get; set; } /// <summary>
/// 组
/// </summary>
public int group { get; set; } /// <summary>
/// 方法
/// </summary>
public string fun { get; set; } }
WhereParameters类所用到的枚举,也是查询所支持的比较方法
/// <summary>
/// 匹配方法
/// </summary>
public enum WhereFun
{
/// <summary>
/// 包含
/// </summary>
Contains = , /// <summary>
/// 相等
/// </summary>
Equal = , /// <summary>
/// 不相等
/// </summary>
NotEqual = , /// <summary>
/// 大于
/// </summary>
GreaterThan = , /// <summary>
/// 大于等于
/// </summary>
GreaterThanOrEqual = , /// <summary>
/// 小于
/// </summary>
LessThan = , /// <summary>
/// 小于等于
/// </summary>
LessThanOrEqual = , /// <summary>
/// 开始包含
/// </summary>
StartsWith = , /// <summary>
/// 结束包含
/// </summary>
EndsWith = , /// <summary>
/// 不包含
/// </summary>
NotContains = , /// <summary>
/// 包含(支持通配符)
/// </summary>
Like = , /// <summary>
/// 不包含(支持通配符)
/// </summary>
NotLike =
}
重中之重还是WhereEx<T>类了,这是关键,主要是用了Expression表达式树实现的
/// <summary>
/// Where表达式
/// </summary>
/// <typeparam name="T">指定的模型</typeparam>
public class WhereEx<T>
{
/// <summary>
/// 表达式
/// </summary>
private Expression ex { get; set; } /// <summary>
/// 模型参数
/// </summary>
private ParameterExpression p_model { get; set; } /// <summary>
/// 构造参数
/// </summary>
/// <param name="ps">过滤参数</param>
/// <param name="isand">如果为true则同一组的进行Or运算,不同组的进行And运算,否则同一组的进行And运算,不同组的进行Or运算</param>
/// <returns>Where表达式</returns>
public static WhereEx<T> Create(List<WhereParameters> ps, bool isand = true)
{
var model = new WhereEx<T>();
model.p_model = Expression.Parameter(typeof(T), "p_model_where");
if (ps == null || ps.Count == )
{
return model;
}
var grouplist = ps.GroupBy(d => d.group);
if (isand)
{
foreach (var item in grouplist)
{
model.And(item.ToArray());
}
}
else
{
foreach (var item in grouplist)
{
model.Or(item.ToArray());
}
}
return model;
} /// <summary>
/// 构造参数
/// </summary>
/// <param name="value">要匹配的值</param>
/// <param name="name">要匹配的字段名称</param>
/// <param name="wherefun">匹配方法</param>
/// <param name="isnotnull">值不为空才执行</param>
/// <returns>Where表达式</returns>
public static WhereEx<T> Create(object value, string name, WhereFun wherefun = WhereFun.Contains, bool isnotnull = true)
{
var model = new WhereEx<T>();
model.p_model = Expression.Parameter(typeof(T), "p_model_where");
model.And(new WhereParameters(value, name, wherefun, isnotnull));
return model;
} /// <summary>
/// Where表达式And运算
/// </summary>
/// <param name="value">要匹配的值</param>
/// <param name="name">要匹配的字段名称</param>
/// <param name="wherefun">匹配方法</param>
/// <param name="isnotnull">值不为空才执行</param>
/// <returns>Where表达式</returns>
public WhereEx<T> And(object value, string name, WhereFun wherefun = WhereFun.Contains, bool isnotnull = true)
{
return this.And(new WhereParameters(value, name, wherefun, isnotnull));
} /// <summary>
/// Where表达式Or运算
/// </summary>
/// <param name="value">要匹配的值</param>
/// <param name="name">要匹配的字段名称</param>
/// <param name="wherefun">匹配方法</param>
/// <param name="isnotnull">值不为空才执行</param>
/// <returns>Where表达式</returns>
public WhereEx<T> Or(object value, string name, WhereFun wherefun = WhereFun.Contains, bool isnotnull = true)
{
return this.Or(new WhereParameters(value, name, wherefun, isnotnull));
} /// <summary>
/// Where表达式And运算
/// </summary>
/// <param name="ps">过滤参数(多个参数时先进行Or运算)</param>
/// <returns>Where表达式</returns>
public WhereEx<T> And(params WhereParameters[] ps)
{
var psex = this.GetWhereEx(false, ps);
if (psex != null)
{
if (this.ex == null)
{
this.ex = Expression.Constant(true, typeof(bool));
}
this.ex = Expression.AndAlso(this.ex, psex);
}
return this;
} /// <summary>
/// Where表达式Or运算
/// </summary>
/// <param name="ps">过滤参数(多个参数时先进行And运算)</param>
/// <returns>Where表达式</returns>
public WhereEx<T> Or(params WhereParameters[] ps)
{
var psex = this.GetWhereEx(true, ps);
if (psex != null)
{
if (this.ex == null)
{
this.ex = Expression.Constant(false, typeof(bool));
}
this.ex = Expression.OrElse(this.ex, psex);
}
return this;
} /// <summary>
/// Where表达式转方法
/// </summary>
/// <returns>方法</returns>
public Expression<Func<T, bool>> ToFun()
{
if (this.ex == null)
{
this.ex = Expression.Constant(true, typeof(bool));
}
return Expression.Lambda<Func<T, bool>>(this.ex, this.p_model);
} /// <summary>
/// 根据过滤参数获取表达式
/// </summary>
/// <param name="isand">是否是And运算</param>
/// <param name="ps">过滤参数集合</param>
/// <returns>表达式</returns>
private Expression GetWhereEx(bool isand, params WhereParameters[] ps)
{
Expression psex = Expression.Constant(isand);
if (ps.Length == )
{
return psex;
}
bool isaddps = false;
#region 循环参数进行运算 foreach (var item in ps)
{
var pro = this.p_model.Type.GetProperty(item.name);
if (pro == null)
continue;
if (!item.IsAddWhere(pro.PropertyType))
{
continue;
}
isaddps = true;
var pro_type = pro.PropertyType;
Expression left = Expression.Property(this.p_model, pro);
if (!string.IsNullOrEmpty(item.fun))
{
item.fun = item.fun.ToLower();
if (item.fun == "length")
{
left = Expression.Property(left, "Length");
pro_type = typeof(int);
}
}
Expression right = Expression.Constant(null);
if (item.value!=null)
{
if (pro_type.IsGenericType && pro_type.GetGenericTypeDefinition() == typeof(Nullable<>))
{
right = Expression.Constant(Convert.ChangeType(item.value, (new System.ComponentModel.NullableConverter(pro_type)).UnderlyingType), pro_type);
}
else
{
right = Expression.Constant(Convert.ChangeType(item.value, pro_type), pro_type);
}
}
Expression body = null;
if (item.wherefun == WhereFun.Contains || item.wherefun == WhereFun.StartsWith || item.wherefun == WhereFun.EndsWith)
{
body = Expression.AndAlso(Expression.NotEqual(left, Expression.Constant(null, pro_type)), Expression.Call(left, pro_type.GetMethod(item.wherefun.ToString(), new Type[] { typeof(string) }), right));
}
else if (item.wherefun == WhereFun.NotContains)
{
body = Expression.AndAlso(Expression.NotEqual(left, Expression.Constant(null, pro_type)), Expression.Not(Expression.Call(left, pro_type.GetMethod(WhereFun.Contains.ToString(), new Type[] { typeof(string) }), right)));
}
//else if (item.wherefun == WhereFun.Like)
//{
// var like = typeof(System.Data.Entity.SqlServer.SqlFunctions).GetMethod("PatIndex", new Type[] { typeof(string), typeof(string) });
// body = Expression.GreaterThan(Expression.Call(null, like, right, left), Expression.Constant(0, typeof(int?)));
//}
//else if (item.wherefun == WhereFun.NotLike)
//{
// var like = typeof(System.Data.Entity.SqlServer.SqlFunctions).GetMethod("PatIndex", new Type[] { typeof(string), typeof(string) });
// body = Expression.Equal(Expression.Call(null, like, right, left), Expression.Constant(0, typeof(int?)));
//}
else if (item.wherefun == WhereFun.Equal)
{
if (item.IsValueNull(pro_type) && pro_type == typeof(string))
{
body = Expression.Call(null, typeof(string).GetMethod("IsNullOrEmpty", new Type[] { typeof(string) }), left);
}
else
{
body = Expression.Equal(left, right);
}
}
else if (item.wherefun == WhereFun.NotEqual)
{
if (item.IsValueNull(pro_type) && pro_type == typeof(string))
{
body = Expression.Not(Expression.Call(null, typeof(string).GetMethod("IsNullOrEmpty", new Type[] { typeof(string) }), left));
}
else
{
body = Expression.NotEqual(left, right);
}
}
else
{
#region 让字符串支持大于小于比较
if (pro_type == typeof(string))
{
left = Expression.Call(left, pro_type.GetMethod("CompareTo", new Type[] { typeof(string) }), right);
right = Expression.Constant();
}
#endregion if (item.wherefun == WhereFun.GreaterThan)
{
body = Expression.GreaterThan(left, right);
}
else if (item.wherefun == WhereFun.GreaterThanOrEqual)
{
body = Expression.GreaterThanOrEqual(left, right);
}
else if (item.wherefun == WhereFun.LessThan)
{
body = Expression.LessThan(left, right);
}
else if (item.wherefun == WhereFun.LessThanOrEqual)
{
body = Expression.LessThanOrEqual(left, right);
}
if (body != null && pro_type == typeof(string))
{
body = Expression.AndAlso(Expression.NotEqual(Expression.Property(this.p_model, pro), Expression.Constant(null, pro_type)), body);
}
}
if (isand)
{
psex = Expression.AndAlso(psex, body);
}
else
{
psex = Expression.OrElse(psex, body);
}
} #endregion
if (isaddps)
{
return psex;
}
else
{
return null;
}
} }
最后附一些其它用法