[C#][LINQ]動態組Where條件
用LINQ來組條件，只能透過Where來組合，有兩大缺點，
1.每一個條件都必需使用if來判斷是否為空值或null
2.每組合一次都必需查詢一次，
為了解決以上這兩個缺點，
所以我在網上找到一個利用泛型與foreach，來組合Where條件。
1:
2: using System;
3: using System.Collections.Generic;
4: using System.Linq;
5: using System.Text;
6: using System.IO;
7: using System.Data.Linq.Mapping;
8: using System.Reflection;
9: using System.Linq.Expressions;
10: //using EnterpriseTester;
11:
12: namespace MvcBancas.DynamicExtensions
13: {
14: //動態組合 LINQ 條件語法
15: //EX：
16: // ////取得條件
17: // //vwTargetao test = new vwTargetao();
18: // //test.target_ym = vw.target_ym;
19: // //test.id_no = vw.id_no;
20: // //test.comm_line_code = vw.comm_line_code;
21: // ////執行
22: // //var Query = db.vwTargetao.AsExpandable().Find<vwTargetao>(test, true);
23: // TEntity obj： 查詢條件
24: // bool isAnd ： true：AND false：OR
25: public static class DynamicExtensions
26: {
27: public static IQueryable<TEntity> Find<TEntity>(this IQueryable<TEntity> source, TEntity obj, bool isAnd) where TEntity : class
28: {
29: if (source == null)
30: throw new ArgumentNullException("Source can't be null!!");
31: //獲得所有property的信息
32: PropertyInfo[] properties = obj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
33:
34: Expression condition = null;
35: //先構造了一個ParameterExpression對象，這裡的c，就是Lambda表達中的參數。（c=>）
36: //本變量被移出了foreach循環
37: ParameterExpression param = Expression.Parameter(typeof(TEntity), "c");
38: //遍歷每個property
39: foreach (PropertyInfo p in properties)
40: {
41: if (p != null)
42: {
43: Type t = p.PropertyType;
44: //只支持value型和string型的影射
45: if (t.IsValueType || t == typeof(string))
46: {
47: //不為null才算做條件
48: if (p.GetValue(obj, null) != null)
49: {
50: //SQL Server does not support comparison of TEXT, NTEXT, XML and IMAGE ,etc
51: ///Only support BigInt,Bit,Char,Decimal,Money,NChar,Real,
52: ///Int,VarChar,SmallMoney,SmallInt,NVarChar,NVarChar(MAX),VarChar(MAX)
53: Attribute attr = Attribute.GetCustomAttribute(p, typeof(ColumnAttribute));
54: if (attr != null)
55: {
56: string dbType = (attr as ColumnAttribute).DbType;
57: if (dbType.Contains("Text") || dbType.Contains("NText")
58: || dbType.Contains("Xml") || dbType.Contains("Image")
59: || dbType.Contains("Binary") || dbType.Contains("DateTime")
60: || dbType.Contains("sql_variant") || dbType.Contains("rowversion")
61: || dbType.Contains("UniqueIdentifier") || dbType.Contains("VarBinary(MAX)"))
62: {
63: continue;
64: }
65: }
66:
67: if (p.Name == "EntityState") continue; //排除EntityState，執行會發生錯誤
68:
69: //此兩種型態NULL會代入最小預設值
70: if (p.PropertyType.FullName == "System.Decimal" || p.PropertyType.FullName == "System.DateTime") //continue;
71: {
72: //if (p.GetValue(obj,0) == 0)
73: continue;
74: }
75:
76: if (p.PropertyType.FullName == "System.Int32" || p.PropertyType.FullName == "System.Int16")
77: {
78: //if (p.GetValue(obj,0) == 0)
79: continue;
80: }
81:
82: //構造表達式的右邊，值的一邊
83: Expression right = Expression.Constant(p.GetValue(obj, null), p.PropertyType);
84:
85: //構造表達式的左邊，property一端。
86: Expression left = Expression.Property(param, p.Name);
87:
88: //生成篩選表達式。即c.CustomerID == "Tom"
89: Expression filter = Expression.Equal(left, right);
90: if (condition == null)
91: {
92: condition = filter;
93: }
94: else
95: {
96: if (isAnd)
97: condition = Expression.And(condition, filter);
98: else
99: condition = Expression.Or(condition, filter);
100: }
101: }
102: }
103: }
104: }
105: if (condition != null)
106: {
107: Expression<Func<TEntity, bool>> pred = Expression.Lambda<Func<TEntity, bool>>(condition, param);
108: return source.Where(pred);
109: }
110: return source;
111:
112: }
113:
114:
115: public static IQueryable<TEntity> Find<TEntity>(this IQueryable<TEntity> source, TEntity obj) where TEntity : class
116: {
117: return Find<TEntity>(source, obj, true);
118: }
119:
120:
121: //EX：db.Customers.WhereOr("City", new List<string> { "London", "BeiJing" }).ToList();
122: //類似於 in 的用法
123: public static IQueryable<TEntity> WhereOr<TEntity, OrType>(this IQueryable<TEntity> source, string propertyName, IEnumerable<OrType> values)
124: {
125: if (source == null)
126: throw new ArgumentNullException("Source can't be null!!");
127: ParameterExpression param = Expression.Parameter(typeof(TEntity), "p");
128: Expression left = Expression.Property(param, propertyName);
129: Expression condition = null;
130: foreach (OrType value in values)
131: {
132: Expression filter = Expression.Equal(left, Expression.Constant(value));
133: if (condition == null)
134: condition = filter;
135: else
136: condition = Expression.Or(condition, filter);
137: }
138: if (condition != null)
139: return source.Where((Expression<Func<TEntity, bool>>)Expression.Lambda(condition, param));
140:
141: return source;
142: }
143:
144:
145:
146: }
147: }
當然，這也還沒包含『like』的語法，不過依照此架構，其實也不難擴充。
最後，再來介紹，如何使用，範例如下：
1: //取得條件
2: //targetao
3: targetao WhereVw = new targetao();
4: WhereVw.target_ym = vw.target_ym;
5: WhereVw.id_no = vw.id_no;
6: WhereVw.comm_line_code = vw.comm_line_code;
7:
8: //proj
9: proj WherePj = new proj();
10: WherePj.proj_name = vw.proj_name;
11:
12: //channels
13: channels WhereCh = new channels();
14: WhereCh.chan_name = vw.chan_name;
15:
16:
17:
18: TargetaoViewModels ViewModel = new TargetaoViewModels();
19:
20: //執行
21: ////多Table回傳用ViewModels
22: var Query = from a in db.targetao.Find<targetao>(WhereVw, true)
23: join b in db.proj.Find<proj>(WherePj, true)
24: on a.proj_no equals b.proj_no
25: join c in db.channels.Find<channels>(WhereCh, true)
26: on a.comm_line_code equals c.comm_line_code
27: select new TargetaoViewModels
28: {
29: TargetaoM = a,
30: ProjM = b,
31: ChannelsM = c
32: };
第一部份是傳入條件，首先要先把從畫面上的條件傳入與table相同的model中(mapping)，
第二部份就是在每一個table之後，加入一個方法 Find<T>(model,true)，
將條件傳入此物件中，，而範例的作是多TABLE JOIN的寫法，也是可行的。