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

[ASP.net教程]EF之高级查询


首先我们来看看一个页面

 

这里面有多选的条件,大于,小于等等,包括每个字段都有

 

如此多的查询条件,我们的后台该如何实现呢?

 

 难道我们还得每个参数都去判断吗?

 

那得传多少参数进来才能实现这个页面的功能啊!

 

既然用了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 = 1265   }

 

重中之重还是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           else188           {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           else218           {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           else229           {230             body = Expression.NotEqual(left, right);231           }232         }233         else234         {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           #endregion242 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         else269         {270           psex = Expression.OrElse(psex, body);271         }272       }273 274       #endregion275       if (isaddps)276       {277         return psex;278       }279       else280       {281         return null;282       }283     }284 285   }

 

最后附一些其它用法