用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_no25: join c in db.channels.Find<channels>(WhereCh, true)
26: on a.comm_line_code equals c.comm_line_code27: select new TargetaoViewModels
28: { 29: TargetaoM = a, 30: ProjM = b, 31: ChannelsM = c 32: };
第一部份是傳入條件，首先要先把從畫面上的條件傳入與table相同的model中(mapping)，
第二部份就是在每一個table之後，加入一個方法 Find<T>(model,true)，
將條件傳入此物件中，，而範例的作是多TABLE JOIN的寫法，也是可行的。
全站熱搜
留言列表
程式設計 (1)
