转载

EF 基本数据过滤

没猜错的话, 你们一定会和一大堆查询条件过不去, 重复的写,反复的写, 写到山崩地裂.

今天看了园友的文字:

实体框架高级应用之动态过滤 EntityFramework DynamicFilters

我把我的处理方式给大家分享一下.

查询条件可以是这样的,有很多:

EF 基本数据过滤

也可以是这样的, 没有几个:

EF 基本数据过滤

我数了一下,我们系统里的Controller , 112个, 68个查询条件.

如果把这 68个查询条件转换为查询代码的话, 想想都蛋疼啊, 那得写多少啊.

比如这样, 重复,机械,没有营养:

 1         public IEnumerable<CREDIT_ASSESS_RESULT> GetCreditResults(CreditAssessResultCondition condition)  2         {  3             using (var db = new Entities())  4             {  5                 IQueryable<CREDIT_ASSESS_RESULT> query = db.CREDIT_ASSESS_RESULT.Where(q => q.STATUS >= 0);  6                 if (condition.CARRIER_ID > 0)  7                 {  8                     query = query.Where(q => q.CARRIER_ID == condition.CARRIER_ID);  9                 } 10  11                 if (condition.COMPANY_ID > 0) 12                 { 13                     query = query.Where(q => q.LOCAL_COMPANY_ID == condition.COMPANY_ID); 14                 } 15  16                 if (condition.ROUTE_ID.HasValue) 17                 { 18                     query = query.Where(q => q.APPLYTO_ROUTEID == condition.ROUTE_ID); 19                 } 20  21                 if (condition.CUSTOMER_TYPE.HasValue) 22                 { 23                     query = query.Where(q => q.APPLYTO_CUSTOMER == condition.CUSTOMER_TYPE); 24                 } 25  26                 if (condition.CREDIT_PERIOD.HasValue) 27                 { 28                     //String period = condition.CREDIT_PERIOD.Value.ToString(); 29                     query = query.Where(q => q.PERIOD == (int)condition.CREDIT_PERIOD.Value); 30                 } 31  32                 if (condition.CREDIT_YEAR.HasValue) 33                 { 34                     query = query.Where(q => q.YEARS.Value == condition.CREDIT_YEAR.Value); 35                 } 36  37                 if (condition.CREDIT_MONTH > 0) 38                 { 39                     query = query.Where(q => q.MONTHS == condition.CREDIT_MONTH); 40                 } 41                 if (condition.CREDIT_SEASON > 0) 42                 { 43                     query = query.Where(q => q.SEASONS == condition.CREDIT_SEASON); 44                 } 45  46                 if (!String.IsNullOrWhiteSpace(condition.CUSTOMER_CODE)) 47                 { 48                     query = query.Where(q => q.COMPANY_ID == condition.CUSTOMER_ID && (q.COMPANY_NAME_CN.ToUpper().Contains(condition.CUSTOMER_CODE.ToUpper()) || 49                         q.COMPANY_NAME_EN.ToUpper().Contains(condition.CUSTOMER_CODE.ToUpper()))); 50                 } 51  52                 if (condition.CustomerSearch.HasValue && condition.CustomerSearch.Value) 53                 { 54                     query = query.Where(q => q.STATUS > 0); 55                 } 56  57                 return query.OrderByDescending(q => q.CREATE_DATETIME).ThenBy(q => q.APPLYTO_COMPANY).ThenBy(q => q.COMPANY_NAME_EN).DoPage(condition.Pager).ToList(); 58             } 59         }

View Code

为了解放同志们, 我借助 DynamicLinq 写了个简单的解决方案:

1, 先看查询条件的写法:

  1     public class RateSearchCondition : BaseQuery<RATES> {   2    3         public decimal? Carrier {   4             get;   5             set;   6         }   7    8         [MapTo("ROUTE_CODE", IgnoreCase = true)]   9         public string Route {  10             get;  11             set;  12         }  13   14         [MapTo("POL_CODE", IgnoreCase = true)]  15         public string Pol {  16             get;  17             set;  18         }  19   20         [MapTo("POD_CODE", IgnoreCase = true)]  21         public string Pod {  22             get;  23             set;  24         }  25   26         /// <summary>  27         /// 启运地  28         /// </summary>  29         [MapTo("FRONT_CODE")]  30         public string OrginCode {  31             get;  32             set;  33         }  34   35         /// <summary>  36         /// 目的地  37         /// </summary>  38         [MapTo("PFD_CODE")]  39         public string DestCode {  40             get;  41             set;  42         }  43   44         [MapTo("EFFECTIVE_DATE")]  45         public DateTime? EffectiveDate {  46             get;  47             set;  48         }  49   50         [MapTo("EXPIRATION_DATE")]  51         public DateTime? ExpirationDate {  52             get;  53             set;  54         }  55   56         public Status? StatusEnum {  57             get;  58             set;  59         }  60   61         [MapTo("STATUS")]  62         public decimal? StatusValue {  63             get {  64                 return (decimal?)this.StatusEnum;  65             }  66         }  67   68         [MapTo("SCHEDULE_ID")]  69         public decimal? ScheduleID {  70             get;  71             set;  72         }  73   74         public string Vessel {  75             get;  76             set;  77         }  78   79         public string Voyage {  80             get;  81             set;  82         }  83   84         /// <summary>  85         /// 销售公司  86         /// </summary>  87         public decimal? Company {  88             get;  89             set;  90         }  91   92         public enum Status {  93             [Description("作废")]  94             Invalid = -1,  95             [Description("草稿")]  96             Draft = 0,  97             [Description("已发布")]  98             Published = 1,  99             [Description("历史")] 100             History = 99, 101         } 102  103         public string VesselName { 104             get; 105             set; 106         } 107  108         public bool IncludeHistory { 109             get; 110             set; 111         } 112  113         [MapTo("RATE_NO", IgnoreCase = true)] 114         public string RateNo { 115             get; 116             set; 117         } 118     }

简单的类定义而已, 只不过某些属性上加了 MapToAttribute 特性, 有些没有加.

这些加上了 MapTo 的, 就是本文说的.

2, 看一下查询的写法:

 1         public IEnumerable<RATES> Search(RateSearchCondition cond) {  2             using (var db = new Entities()) {  3                 var query = cond.Filter(db.RATES.Where(r => !r.DELETE_MARK));  4   5   6                 if (!(cond.IncludeHistory || cond.StatusEnum == RateSearchCondition.Status.History)) {  7                     query = query.Where(m => m.STATUS != (int)RateSearchCondition.Status.History)  8                         .OrderByDescending(r => r.MODIFY_DATETIME);  9                 } else 10                     query = query.OrderByDescending(r => r.CREATEBY ?? 0); 11  12                 return query 13                     .DoPage(cond.Pager) 14                     .ToList(); 15             } 16         }

这样看, 代码是不是清爽很多?

那么多查询条件, 基本都浓缩进了 cond.Filter(...) 中了, 如果要加其它的查询条件, 只需要改一下查询条件的类, 并加上 MapTo 就可以了.

不能浓缩进去的, 直接写在代码里, 反正也没有几个.

3, 看一下 MapTo 的定义:

 1     /// <summary>  2     /// 用于 BaseQuery  3     /// </summary>  4     [AttributeUsage(AttributeTargets.Property)]  5     public class MapToAttribute : Attribute {  6   7   8         /// <summary>  9         /// 映射到的属性 10         /// </summary> 11         public string Field { 12             get; 13             set; 14         } 15  16         /// <summary> 17         /// 比较类型 18         /// </summary> 19         public MapToOpts Opt { 20             get; 21             set; 22         } 23  24         /// <summary> 25         /// 是否忽略大小写 26         /// </summary> 27         public bool IgnoreCase { 28             get; 29             set; 30         } 31  32         /// <summary> 33         ///  34         /// </summary> 35         /// <param name="field"></param> 36         public MapToAttribute(string field) { 37             if (string.IsNullOrEmpty(field)) 38                 throw new ArgumentNullException("field"); 39  40             this.Field = field; 41         } 42  43     } 44  45     /// <summary> 46     /// 比较类型 47     /// </summary> 48     public enum MapToOpts { 49         /// <summary> 50         /// 等于 51         /// </summary> 52         Equal, 53  54         /// <summary> 55         /// 不等于 56         /// </summary> 57         NotEqual, 58  59         /// <summary> 60         /// 大于 61         /// </summary> 62         Gt, 63  64         /// <summary> 65         /// 大于等于 66         /// </summary> 67         GtOrEqual, 68  69         /// <summary> 70         /// 小于 71         /// </summary> 72         Lt, 73  74         /// <summary> 75         /// 小于等 76         /// </summary> 77         LtOrEqual, 78  79         /// <summary> 80         /// 以 XXX 开始(字符串) 81         /// </summary> 82         StartWith, 83  84         /// <summary> 85         /// 以XXX结尾(字符串) 86         /// </summary> 87         EndWith, 88  89         /// <summary> 90         /// 包含XXX(字符串) 91         /// </summary> 92         Include 93     }

从 MapToOpts 可以看出, 基本的大于,小于,等于,不等于, 开始,包含,结束这样的查询条件都可以涵盖到.

但是像是否包含在集合中 / And / Or 操作是不支持的, 它们需要写在查询方法里, 所以标题我就写的是: 基本数据过滤

4, BaseQuery 的定义:

  1     [Serializable]   2     public class BaseQuery {   3    4         private Pager pager = null;   5         public Pager Pager {   6             get {   7                 if (this.pager == null)   8                     this.pager = new Pager();   9                 return this.pager;  10             }  11             set {  12                 this.pager = value;  13             }  14         }  15   16         private bool allowPage = true;  17         /// <summary>  18         /// 是否分页  19         /// </summary>  20         [DisableBinding]  21         public bool AllowPage {  22             get {  23                 return this.allowPage;  24             }  25             set {  26                 this.allowPage = value;  27             }  28         }  29     }  30   31     [Serializable]  32     public class BaseQuery<T> : BaseQuery where T : class {  33   34         public IQueryable<T> Filter(IQueryable<T> source) {  35             var ps = this.GetType()  36                 .GetProperties()  37                 .ToList();  38   39             ps.ForEach(p => {  40                 var mapTo = p.GetCustomAttribute<MapToAttribute>();  41                 if (mapTo != null) {  42                     var st = typeof(T).GetProperty(mapTo.Field);  43                     if (st != null) {  44                         var opt = string.Empty;  45                         switch (mapTo.Opt) {  46                             case MapToOpts.Equal:  47                                 opt = "==";  48                                 break;  49                             case MapToOpts.NotEqual:  50                                 opt = "!=";  51                                 break;  52                             case MapToOpts.Gt:  53                                 opt = ">";  54                                 break;  55                             case MapToOpts.Lt:  56                                 opt = "<";  57                                 break;  58                             case MapToOpts.GtOrEqual:  59                                 opt = ">=";  60                                 break;  61                             case MapToOpts.LtOrEqual:  62                                 opt = "<=";  63                                 break;  64                         }  65   66                         if (!string.IsNullOrEmpty(opt)) {  67                             var v = p.GetValue(this);  68                             if (v != null) {  69                                 string cond = "";  70                                 if (v.GetType() == typeof(string) && mapTo.Opt == MapToOpts.Equal && mapTo.IgnoreCase) {  71                                     if (!string.IsNullOrWhiteSpace(v.ToString()))  72                                     {  73                                         cond = string.Format("{0}.ToUpper() {1} @0", st.Name, opt);  74                                         source = source.Where(cond, ((string)v).ToUpper());  75                                     }  76                                 } else {  77                                     cond = string.Format("{0} {1} @0", st.Name, opt);  78                                     source = source.Where(cond, v);  79                                 }  80   81   82                             }  83                         } else {  84                             var cond = string.Empty;  85   86                             var v = (string)p.GetValue(this);  87                             if (v == null || string.IsNullOrEmpty((string)v))  88                                 return;  89   90                             v = v.Replace("/"", "");  91   92                             if (string.IsNullOrEmpty(v))  93                                 return;  94   95                             var ignoreCaseStr = mapTo.IgnoreCase ? ".ToUpper()" : "";  96                             if (mapTo.IgnoreCase)  97                                 v = v.ToUpper();  98   99                             switch (mapTo.Opt) { 100                                 case MapToOpts.Include: 101                                     cond = string.Format("{0}{1}.IndexOf(/"{2}/") != -1", st.Name, ignoreCaseStr, v); 102                                     break; 103                                 case MapToOpts.StartWith: 104                                     cond = string.Format("{0}{1}.StartsWith(/"{2}/")", st.Name, ignoreCaseStr, v); 105                                     break; 106                                 case MapToOpts.EndWith: 107                                     cond = string.Format("{0}{1}.EndsWith(/"{2}/")", st.Name, ignoreCaseStr, v); 108                                     break; 109                             } 110  111                             if (!string.IsNullOrEmpty(cond)) { 112                                 source = source.Where(cond); 113                             } 114  115                         } 116                     } 117                 } 118             }); 119  120             return source; 121         } 122     }