首先我们来看看一个页面
这里面有多选的条件,大于,小于等等,包括每个字段都有
如此多的查询条件,我们的后台该如何实现呢?
难道我们还得每个参数都去判断吗?
那得传多少参数进来才能实现这个页面的功能啊!
既然用了EF当然不能在用sql拼接了
那么我们就来看看这个页面是怎么实现的吧
首先我们来看看这个页面的参数是怎么传到后台的
这是获取查询条件的脚本
1 var filterRules = new Array(); 2 $(op.toolbarid).find("input[type!='button'][data-disable!='true'],select[data-disable!='true']").each(function () { 3 var isadd = false; 4 var value = $(this).val(); 5 if (this.type == "checkbox" || this.type == "radio") { 6 isadd = this.checked; 7 } else if (value) { 8 isadd = true; 9 } 10 if (isadd) { 11 var field = $(this).data("field"); 12 if (!field) { 13 field = this.id; 14 } 15 var op = $(this).data("op"); 16 var time_add = $(this).data("time-add"); 17 if (time_add || $(this).data("time-today")) { 18 if (time_add) { 19 value = (new Date(Date.parse(value.replace(/-/g, "/")).getTime() + parseInt(time_add) * 86400000)).Format("yyyy-MM-dd"); 20 } else { 21 var new_value = (new Date(Date.parse(value.replace(/-/g, "/")).getTime() + 86400000)).Format("yyyy-MM-dd"); 22 filterRules.push({ group: $(this).data("group"), field: field, op: "<", split: $(this).data("split"), value: new_value }); 23 op = ">="; 24 } 25 } 26 filterRules.push({ group: $(this).data("group"), field: field, op: op, split: $(this).data("split"), value: value }); 27 } 28 }); 29 options.filterRules = filterRules;
这是请求后台传递的参数
1 [{"field":"FILE_TYPE","op":"=","split":",","value":"CAD,AD"}, 2 {"field":"WRITE_DATE","op":">=","value":"2011-01-01"}, 3 {"field":"WRITE_DATE","op":"<","value":"2016-04-14"}, 4 {"field":"STATUS","op":"=","split":",","value":"CLOSE,C/W"}]
后台代码
1 return db.AWF_FILE_MAIN.Join(db.FLOW_TASK_INFO, d => d.FILE_NO, d => d.FILE_NO, (x, y) => new { ... }) 2 .Where(EasyuiFilterRules.GetWPList(wm.filterRules), wm.filterRules_isand) 3 .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对象
1 /// <summary> 2 /// Easyui搜索规则 3 /// </summary> 4 public class EasyuiFilterRules 5 { 6 /// <summary> 7 /// 字段名称 8 /// </summary> 9 public string field { get; set; } 10 11 /// <summary> 12 /// 过滤类型,可选值有(contains-包含,equal-相等,notequal-不相等,beginwith-开头包含,endwith-结尾包含,less-小于,lessorequal-小于等于,greater-大于,greaterorequal-大于等于) 13 /// </summary> 14 public string op { get; set; } 15 16 /// <summary> 17 /// 要搜索的值 18 /// </summary> 19 public string value { get; set; } 20 21 /// <summary> 22 /// 组 23 /// </summary> 24 public int? group { get; set; } 25 26 27 /// <summary> 28 /// 分隔符 29 /// </summary> 30 public string split { get; set; } 31 32 /// <summary> 33 /// 方法 34 /// </summary> 35 public string fun { get; set; } 36 37 /// <summary> 38 /// 获取过滤参数集合根据Easyui搜索规则字符串 39 /// </summary> 40 /// <param name="filterRules">Easyui搜索规则字符串</param> 41 /// <returns>过滤参数集合</returns> 42 public static List<WhereParameters> GetWPList(string filterRules) 43 { 44 var ps = new List<WhereParameters>(); 45 if (string.IsNullOrEmpty(filterRules)) 46 { 47 return ps; 48 } 49 var list = JsonConvert.DeserializeObject<List<EasyuiFilterRules>>(filterRules); 50 if (list.Count == 0) 51 { 52 return ps; 53 } 54 int index = -1; 55 foreach (var item in list) 56 { 57 if (string.IsNullOrEmpty(item.value)) 58 { 59 continue; 60 } 61 var names = item.field.Split(','); 62 63 foreach (var name in names) 64 { 65 var values = item.value.Split(new string[]{item.split},StringSplitOptions.RemoveEmptyEntries); 66 foreach (var value in values) 67 { 68 var wp = new WhereParameters(value, name, group: item.group ?? index,fun:item.fun); 69 if (item.value == "is null") 70 { 71 wp.value = null; 72 wp.isnotnull = false; 73 wp.wherefun = WhereFun.Equal; 74 } 75 else if (item.value == "is not null") 76 { 77 wp.value = null; 78 wp.isnotnull = false; 79 wp.wherefun = WhereFun.NotEqual; 80 } 81 else 82 { 83 if (string.IsNullOrEmpty(item.op)) 84 { 85 item.op = "contains"; 86 } 87 switch (item.op.ToLower()) 88 { 89 case "=": 90 case "==": 91 case "equal": 92 case "eq": wp.wherefun = WhereFun.Equal; break; 93 94 case "!=": 95 case "neq": 96 case "notequal": wp.wherefun = WhereFun.NotEqual; break; 97 98 case "<": 99 case "lt": 100 case "less": wp.wherefun = WhereFun.LessThan; break; 101 102 case "<=": 103 case "lte": 104 case "lessorequal": wp.wherefun = WhereFun.LessThanOrEqual; break; 105 106 case ">": 107 case "gt": 108 case "greater": wp.wherefun = WhereFun.GreaterThan; break; 109 110 case ">=": 111 case "gte": 112 case "greaterorequal": wp.wherefun = WhereFun.GreaterThanOrEqual; break; 113 114 case "!c": 115 case "doesnotcontain": wp.wherefun = WhereFun.NotContains; break; 116 117 case "^c": 118 case "startswith": wp.wherefun = WhereFun.StartsWith; break; 119 120 case "c$": 121 case "endswith": wp.wherefun = WhereFun.EndsWith; break; 122 123 case "like": wp.wherefun = WhereFun.Like; break; 124 125 case "notlike": wp.wherefun = WhereFun.NotLike; break; 126 127 default: wp.wherefun = WhereFun.Contains; break; 128 } 129 } 130 ps.Add(wp); 131 } 132 } 133 index--; 134 } 135 return ps; 136 } 137 }
接着我们看看WhereHelper类
就是这个类让Where方法可以接受.Where(EasyuiFilterRules.GetWPList(wm.filterRules), wm.filterRules_isand)这样的参数
代码也是相对比较简单的
1 /// <summary> 2 /// 搜索条件辅助类 3 /// </summary> 4 public static class WhereHelper 5 { 6 /// <summary> 7 /// 根据条件过滤数据 8 /// </summary> 9 /// <typeparam name="model">指定的模型</typeparam> 10 /// <param name="mls">对象</param> 11 /// <param name="ps">过滤参数集合</param> 12 /// <param name="isand">如果为true则同一组的进行Or运算,不同组的进行And运算,否则同一组的进行And运算,不同组的进行Or运算</param> 13 /// <returns>对象</returns> 14 public static IQueryable<model> Where<model>(this IQueryable<model> mls, List<WhereParameters> ps, bool isand = true) 15 { 16 if (ps.Count == 0) 17 { 18 return mls; 19 } 20 return mls.Where(WhereEx<model>.Create(ps, isand).ToFun()); 21 } 22 }
继续WhereParameters,这个类也只是用来保存一些数据信息,没什么逻辑
1 /// <summary> 2 /// 过滤参数 3 /// </summary> 4 public class WhereParameters 5 { 6 /// <summary> 7 /// 构造函数 8 /// </summary> 9 public WhereParameters() 10 { 11 12 } 13 14 /// <summary> 15 /// 构造函数 16 /// </summary> 17 /// <param name="value">要匹配的值</param> 18 /// <param name="name">要匹配的字段名称</param> 19 /// <param name="wherefun">匹配方法</param> 20 /// <param name="isnotnull">值不为空才执行</param> 21 /// <param name="group">组</param> 22 /// <param name="fun">方法</param> 23 public WhereParameters(object value, string name, WhereFun wherefun = WhereFun.Contains, bool isnotnull = true, int group = 0,string fun="") 24 { 25 this.value = value; 26 this.name = name; 27 this.wherefun = wherefun; 28 this.isnotnull = isnotnull; 29 this.group = group; 30 this.fun = fun; 31 } 32 33 /// <summary> 34 /// 判断是否要添加查询条件 35 /// </summary> 36 /// <returns>是否</returns> 37 public bool IsAddWhere(Type type) 38 { 39 if (this.isnotnull && IsValueNull(type)) 40 { 41 return false; 42 } 43 return true; 44 } 45 46 /// <summary> 47 /// 判断值是否为空 48 /// </summary> 49 /// <returns></returns> 50 public bool IsValueNull(Type type) 51 { 52 return string.IsNullOrEmpty(Convert.ToString(this.value)); 53 } 54 55 /// <summary> 56 /// 要匹配的值 57 /// </summary> 58 public object value { get; set; } 59 60 /// <summary> 61 /// 要匹配的字段名称 62 /// </summary> 63 public string name { get; set; } 64 65 /// <summary> 66 /// 匹配方法 67 /// </summary> 68 public WhereFun wherefun { get; set; } 69 70 /// <summary> 71 /// 值不为空才执行 72 /// </summary> 73 public bool isnotnull { get; set; } 74 75 /// <summary> 76 /// 组 77 /// </summary> 78 public int group { get; set; } 79 80 /// <summary> 81 /// 方法 82 /// </summary> 83 public string fun { get; set; } 84 85 }
WhereParameters类所用到的枚举,也是查询所支持的比较方法
1 /// <summary> 2 /// 匹配方法 3 /// </summary> 4 public enum WhereFun 5 { 6 /// <summary> 7 /// 包含 8 /// </summary> 9 Contains = 1, 10 11 /// <summary> 12 /// 相等 13 /// </summary> 14 Equal = 2, 15 16 /// <summary> 17 /// 不相等 18 /// </summary> 19 NotEqual = 3, 20 21 /// <summary> 22 /// 大于 23 /// </summary> 24 GreaterThan = 4, 25 26 /// <summary> 27 /// 大于等于 28 /// </summary> 29 GreaterThanOrEqual = 5, 30 31 /// <summary> 32 /// 小于 33 /// </summary> 34 LessThan = 6, 35 36 /// <summary> 37 /// 小于等于 38 /// </summary> 39 LessThanOrEqual = 7, 40 41 /// <summary> 42 /// 开始包含 43 /// </summary> 44 StartsWith = 8, 45 46 /// <summary> 47 /// 结束包含 48 /// </summary> 49 EndsWith = 9, 50 51 /// <summary> 52 /// 不包含 53 /// </summary> 54 NotContains = 10, 55 56 /// <summary> 57 /// 包含(支持通配符) 58 /// </summary> 59 Like = 11, 60 61 /// <summary> 62 /// 不包含(支持通配符) 63 /// </summary> 64 NotLike = 12 65 }
重中之重还是WhereEx<T>类了,这是关键,主要是用了Expression表达式树实现的
1 /// <summary> 2 /// Where表达式 3 /// </summary> 4 /// <typeparam name="T">指定的模型</typeparam> 5 public class WhereEx<T> 6 { 7 /// <summary> 8 /// 表达式 9 /// </summary> 10 private Expression ex { get; set; } 11 12 /// <summary> 13 /// 模型参数 14 /// </summary> 15 private ParameterExpression p_model { get; set; } 16 17 /// <summary> 18 /// 构造参数 19 /// </summary> 20 /// <param name="ps">过滤参数</param> 21 /// <param name="isand">如果为true则同一组的进行Or运算,不同组的进行And运算,否则同一组的进行And运算,不同组的进行Or运算</param> 22 /// <returns>Where表达式</returns> 23 public static WhereEx<T> Create(List<WhereParameters> ps, bool isand = true) 24 { 25 var model = new WhereEx<T>(); 26 model.p_model = Expression.Parameter(typeof(T), "p_model_where"); 27 if (ps == null || ps.Count == 0) 28 { 29 return model; 30 } 31 var grouplist = ps.GroupBy(d => d.group); 32 if (isand) 33 { 34 foreach (var item in grouplist) 35 { 36 model.And(item.ToArray()); 37 } 38 } 39 else 40 { 41 foreach (var item in grouplist) 42 { 43 model.Or(item.ToArray()); 44 } 45 } 46 return model; 47 } 48 49 /// <summary> 50 /// 构造参数 51 /// </summary> 52 /// <param name="value">要匹配的值</param> 53 /// <param name="name">要匹配的字段名称</param> 54 /// <param name="wherefun">匹配方法</param> 55 /// <param name="isnotnull">值不为空才执行</param> 56 /// <returns>Where表达式</returns> 57 public static WhereEx<T> Create(object value, string name, WhereFun wherefun = WhereFun.Contains, bool isnotnull = true) 58 { 59 var model = new WhereEx<T>(); 60 model.p_model = Expression.Parameter(typeof(T), "p_model_where"); 61 model.And(new WhereParameters(value, name, wherefun, isnotnull)); 62 return model; 63 } 64 65 /// <summary> 66 /// Where表达式And运算 67 /// </summary> 68 /// <param name="value">要匹配的值</param> 69 /// <param name="name">要匹配的字段名称</param> 70 /// <param name="wherefun">匹配方法</param> 71 /// <param name="isnotnull">值不为空才执行</param> 72 /// <returns>Where表达式</returns> 73 public WhereEx<T> And(object value, string name, WhereFun wherefun = WhereFun.Contains, bool isnotnull = true) 74 { 75 return this.And(new WhereParameters(value, name, wherefun, isnotnull)); 76 } 77 78 /// <summary> 79 /// Where表达式Or运算 80 /// </summary> 81 /// <param name="value">要匹配的值</param> 82 /// <param name="name">要匹配的字段名称</param> 83 /// <param name="wherefun">匹配方法</param> 84 /// <param name="isnotnull">值不为空才执行</param> 85 /// <returns>Where表达式</returns> 86 public WhereEx<T> Or(object value, string name, WhereFun wherefun = WhereFun.Contains, bool isnotnull = true) 87 { 88 return this.Or(new WhereParameters(value, name, wherefun, isnotnull)); 89 } 90 91 /// <summary> 92 /// Where表达式And运算 93 /// </summary> 94 /// <param name="ps">过滤参数(多个参数时先进行Or运算)</param> 95 /// <returns>Where表达式</returns> 96 public WhereEx<T> And(params WhereParameters[] ps) 97 { 98 var psex = this.GetWhereEx(false, ps); 99 if (psex != null) 100 { 101 if (this.ex == null) 102 { 103 this.ex = Expression.Constant(true, typeof(bool)); 104 } 105 this.ex = Expression.AndAlso(this.ex, psex); 106 } 107 return this; 108 } 109 110 /// <summary> 111 /// Where表达式Or运算 112 /// </summary> 113 /// <param name="ps">过滤参数(多个参数时先进行And运算)</param> 114 /// <returns>Where表达式</returns> 115 public WhereEx<T> Or(params WhereParameters[] ps) 116 { 117 var psex = this.GetWhereEx(true, ps); 118 if (psex != null) 119 { 120 if (this.ex == null) 121 { 122 this.ex = Expression.Constant(false, typeof(bool)); 123 } 124 this.ex = Expression.OrElse(this.ex, psex); 125 } 126 return this; 127 } 128 129 130 /// <summary> 131 /// Where表达式转方法 132 /// </summary> 133 /// <returns>方法</returns> 134 public Expression<Func<T, bool>> ToFun() 135 { 136 if (this.ex == null) 137 { 138 this.ex = Expression.Constant(true, typeof(bool)); 139 } 140 return Expression.Lambda<Func<T, bool>>(this.ex, this.p_model); 141 } 142 143 /// <summary> 144 /// 根据过滤参数获取表达式 145 /// </summary> 146 /// <param name="isand">是否是And运算</param> 147 /// <param name="ps">过滤参数集合</param> 148 /// <returns>表达式</returns> 149 private Expression GetWhereEx(bool isand, params WhereParameters[] ps) 150 { 151 Expression psex = Expression.Constant(isand); 152 if (ps.Length == 0) 153 { 154 return psex; 155 } 156 bool isaddps = false; 157 #region 循环参数进行运算 158 159 foreach (var item in ps) 160 { 161 var pro = this.p_model.Type.GetProperty(item.name); 162 if (pro == null) 163 continue; 164 if (!item.IsAddWhere(pro.PropertyType)) 165 { 166 continue; 167 } 168 isaddps = true; 169 var pro_type = pro.PropertyType; 170 Expression left = Expression.Property(this.p_model, pro); 171 if (!string.IsNullOrEmpty(item.fun)) 172 { 173 item.fun = item.fun.ToLower(); 174 if (item.fun == "length") 175 { 176 left = Expression.Property(left, "Length"); 177 pro_type = typeof(int); 178 } 179 } 180 Expression right = Expression.Constant(null); 181 if (item.value!=null) 182 { 183 if (pro_type.IsGenericType && pro_type.GetGenericTypeDefinition() == typeof(Nullable<>)) 184 { 185 right = Expression.Constant(Convert.ChangeType(item.value, (new System.ComponentModel.NullableConverter(pro_type)).UnderlyingType), pro_type); 186 } 187 else 188 { 189 right = Expression.Constant(Convert.ChangeType(item.value, pro_type), pro_type); 190 } 191 } 192 Expression body = null; 193 if (item.wherefun == WhereFun.Contains || item.wherefun == WhereFun.StartsWith || item.wherefun == WhereFun.EndsWith) 194 { 195 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)); 196 } 197 else if (item.wherefun == WhereFun.NotContains) 198 { 199 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))); 200 } 201 //else if (item.wherefun == WhereFun.Like) 202 //{ 203 // var like = typeof(System.Data.Entity.SqlServer.SqlFunctions).GetMethod("PatIndex", new Type[] { typeof(string), typeof(string) }); 204 // body = Expression.GreaterThan(Expression.Call(null, like, right, left), Expression.Constant(0, typeof(int?))); 205 //} 206 //else if (item.wherefun == WhereFun.NotLike) 207 //{ 208 // var like = typeof(System.Data.Entity.SqlServer.SqlFunctions).GetMethod("PatIndex", new Type[] { typeof(string), typeof(string) }); 209 // body = Expression.Equal(Expression.Call(null, like, right, left), Expression.Constant(0, typeof(int?))); 210 //} 211 else if (item.wherefun == WhereFun.Equal) 212 { 213 if (item.IsValueNull(pro_type) && pro_type == typeof(string)) 214 { 215 body = Expression.Call(null, typeof(string).GetMethod("IsNullOrEmpty", new Type[] { typeof(string) }), left); 216 } 217 else 218 { 219 body = Expression.Equal(left, right); 220 } 221 } 222 else if (item.wherefun == WhereFun.NotEqual) 223 { 224 if (item.IsValueNull(pro_type) && pro_type == typeof(string)) 225 { 226 body = Expression.Not(Expression.Call(null, typeof(string).GetMethod("IsNullOrEmpty", new Type[] { typeof(string) }), left)); 227 } 228 else 229 { 230 body = Expression.NotEqual(left, right); 231 } 232 } 233 else 234 { 235 #region 让字符串支持大于小于比较 236 if (pro_type == typeof(string)) 237 { 238 left = Expression.Call(left, pro_type.GetMethod("CompareTo", new Type[] { typeof(string) }), right); 239 right = Expression.Constant(0); 240 } 241 #endregion 242 243 if (item.wherefun == WhereFun.GreaterThan) 244 { 245 body = Expression.GreaterThan(left, right); 246 } 247 else if (item.wherefun == WhereFun.GreaterThanOrEqual) 248 { 249 body = Expression.GreaterThanOrEqual(left, right); 250 } 251 else if (item.wherefun == WhereFun.LessThan) 252 { 253 body = Expression.LessThan(left, right); 254 } 255 else if (item.wherefun == WhereFun.LessThanOrEqual) 256 { 257 body = Expression.LessThanOrEqual(left, right); 258 } 259 if (body != null && pro_type == typeof(string)) 260 { 261 body = Expression.AndAlso(Expression.NotEqual(Expression.Property(this.p_model, pro), Expression.Constant(null, pro_type)), body); 262 } 263 } 264 if (isand) 265 { 266 psex = Expression.AndAlso(psex, body); 267 } 268 else 269 { 270 psex = Expression.OrElse(psex, body); 271 } 272 } 273 274 #endregion 275 if (isaddps) 276 { 277 return psex; 278 } 279 else 280 { 281 return null; 282 } 283 } 284 285 }
最后附一些其它用法
398
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
