用Linq To Sql或Linq To Entity Framework,在Select方面用起來有如神助飛快的方便,但在批次Update或Delete方面就遜色很多,完全就如Ado.Net時期下SQL一樣,沒有物件導向可言。
本篇將介紹如何用挖出Entity Framework的Metadata,以Expression的方式組成Sql。
古老的Entity Framework下批次Update與Delete幾種方式
假設TableA的Column1='A'有100筆資料,要將Column2值更改。
1.使用DbCommand.ExecuteNonQuery或ObjectContext.ExecuteStoreCommand去執行,使用如下的SQL。
cmd.ExecuteNonQuery("UPDATE TableA Set Column2='B' WHERE Column1='A'");
很簡單,但是以文字的方式去執行,就失去ORM的好處,如以設Property方式給值,型別安全,打錯字在Build時就會失敗等等功能。
2.跑迴圈
foreach (var item in db.TableA.Where(t => t.Column1 == 'A')) { item.Column2 = "B"; } db.SaveChanges();
使用這個方法雖然有ORM的好處,但要先取出100筆,轉成物件,更新,轉成SQL,非常的沒有效率。
Entity Framework的Extention
後來看到國外的一篇文章
Multiple entity updates with Entity Framework – EF Fetch Updates
以Expression的方式,再分析msl檔產生SQL,用ObjectContext.ExecuteStoreCommand作批次Update與Delete,我參考他的概念自己寫了一個Extention
執行範例
//批次Update 第一個Expression是設值,第二個Expression是Where db.Product.Update(() => new Product() { Class = "Test", EndDate = DateTime.Now, StartDate = DateTime.Today, ArabicDescription = new Guid().ToString() }, null); db.Product.Update(() => new Product() { MyKey = int.Parse("12") }, x => x.Class == "A" && x.Color == "B"); db.Product.Update(() => new Product() { ModelName = "XX'X" }, x => x.Class == null); db.Product.Update(() => new Product() { ModelName = "XX'X" }, x => x.Class == x.ProductLine); //批次Delete 參數是Where的Expression db.Product.Delete(x => (x.Status == "A" || x.Status == "B") && x.Style == "C"); //產生的SQL //UPDATE dbo.DimProduct SET Class='Test',EndDate=GETDATE(),StartDate='2010/8/20 上午 12:00:00',ArabicDescription='00000000-0000-0000-0000-000000000000' //UPDATE dbo.DimProduct SET ProductKey=12 WHERE ((Class='A') AND (Color='B')) //UPDATE dbo.DimProduct SET ModelName='XX''X' WHERE (Class IS NULL) //UPDATE dbo.DimProduct SET ModelName='XX''X' WHERE (Class=ProductLine) //DELETE dbo.DimProduct WHERE (((Status='A') OR (Status='B')) AND (Style='C'))
用起來是不是直覺多了。
程式解說
1.了解Entiy Framework Metadata
一個Entiy Framework會有三個檔案
- CSDL : 概念架構定義語言,物件的定義
- SSDL : 存儲架構定義語言,資料的定義
- MSL : 映射規範語言,CSDL與SSDL的「對應」(Mapping)
在Entiy Framework中物件與資料庫的名稱是可以不一樣的,如上一個範例物件的名稱是Product對應資料表DimProduct,屬性MyKey對應資料欄ProductKey,而這些對應存在MSL中。
圖1 edmx內容
我參考的範例是用剖析XML方式去載入MSL,但我想說微軟一定有寫好的,不應該也不想自己又在寫一份,我就嘗試找出微軟的使用方式。
/// <summary> /// 存放資料表與資料欄對應 /// </summary> private class TableMetadata { public EntitySet Table { get; set; } public Dictionary<string, EdmProperty> Properties { get; set; } } /// <summary> /// 取得MSL中Table的資訊 /// </summary> private static TableMetadata GetTableMetadata<TEntity>(ObjectSet<TEntity> source) where TEntity : class { //執行EnsureMetadata後才會載入MSL typeof(ObjectContext).InvokeMember("EnsureMetadata", BindingFlags.InvokeMethod | BindingFlags.NonPublic | BindingFlags.Instance, null, source.Context, null); //CSSpace就是MSL的DataSpace var mapContainer = source.Context.MetadataWorkspace.GetItemCollection(DataSpace.CSSpace)[0]; //因為微軟雖然有寫相關的Class或Method,但都是Internal的,所以只好用Reflection取資料。 var mapSet = mapContainer.GetType().InvokeMember("GetSetMapping", BindingFlags.InvokeMethod | BindingFlags.NonPublic | BindingFlags.Instance, null, mapContainer, new object[] { source.EntitySet.Name }); var mapType = (mapSet.GetType().InvokeMember("TypeMappings", BindingFlags.GetProperty | BindingFlags.NonPublic | BindingFlags.Instance, null, mapSet, null) as IList)[0]; var map = (mapType.GetType().InvokeMember("MappingFragments", BindingFlags.GetProperty | BindingFlags.NonPublic | BindingFlags.Instance, null, mapType, null) as IList)[0]; var tableMetadata = new TableMetadata(); tableMetadata.Table = map.GetType().InvokeMember("TableSet", BindingFlags.GetProperty | BindingFlags.NonPublic | BindingFlags.Instance, null, map, null) as EntitySet; tableMetadata.Properties = new Dictionary<string, EdmProperty>(); PropertyInfo pinfo = null, cpinfo = null; foreach (var item in (map.GetType().InvokeMember("Properties", BindingFlags.GetProperty | BindingFlags.NonPublic | BindingFlags.Instance, null, map, null) as IEnumerable)) { if (pinfo == null) { cpinfo = item.GetType().GetProperty("ColumnProperty", BindingFlags.NonPublic | BindingFlags.Instance); pinfo = item.GetType().GetProperty("EdmProperty", BindingFlags.NonPublic | BindingFlags.Instance); } EdmProperty cprop = cpinfo.GetValue(item, null) as EdmProperty; EdmProperty prop = pinfo.GetValue(item, null) as EdmProperty; tableMetadata.Properties.Add(prop.Name, cprop); } return tableMetadata; }
圖2 用Visual Studio中Debug就算是private還是可以看或呼叫Method
2.了解Expression
Update與Delete的參數都是Expression,Expression可以想成可以執行的數據,如 ()=> 1+1 會變成BinaryExpression,其NodeType是Add,Left與Right是ConstantExpression其Type是Int32,而.Net的所有語法,都可以轉成Expression,所以可以從Expression中分析出操作,然後組出SQL。
Update與Delete的定義,我是Extention ObjectSet<T>,這樣只要下db.Table.Update就可以使用,我覺得挺方便的。
/// <summary> /// 批次更新 /// </summary> public static int Update<TEntity>(this ObjectSet<TEntity> source, Expression<Func<TEntity>> setExpression, Expression<Func<TEntity, bool>> whereExpression) where TEntity : class; /// <summary> /// 批次刪除 /// </summary> public static int Delete<TEntity>(this ObjectSet<TEntity> source, Expression<Func<TEntity, bool>> whereExpression) where TEntity : class;
分析Expression
/// <summary> /// 取得Expression值 /// </summary> /// <param name="expression"></param> /// <returns></returns> private static string GetExpressionValue(Expression expression) { if (expression is ConstantExpression) { //直接設值的Expression var ce = expression as ConstantExpression; return Format(ce.Value, ce.Type); } else if (expression is UnaryExpression) { //表示有一元 (Unary) 運算子的運算式 UnaryExpression ue = expression as UnaryExpression; if (ue.Operand is MemberExpression) { //取屬性值 MemberExpression me = ue.Operand as MemberExpression; if (me.Type == typeof(DateTime)) { //DateTime.Now 直接用SQL的語法 if (me.Member.Name == "Now") { return "GETDATE()"; } else if (me.Member.Name == "UtcNow") { return "GETUTCDATE()"; } } return Format(Expression.Lambda(me).Compile().DynamicInvoke(), me.Type); } else { return Format(Expression.Lambda(ue.Operand).Compile().DynamicInvoke(), ue.Operand.Type); } } return Format(Expression.Lambda(expression).Compile().DynamicInvoke(), expression.Type); }
/// <summary> /// 以遞迴方式解析Where的Expression /// </summary> /// <param name="expression"></param> /// <param name="tableMetadata"></param> /// <returns></returns> private static string GetStringExpression(Expression expression, TableMetadata tableMetadata) { if (expression is BinaryExpression) { //還是比較 And OR string oper, left, right; BinaryExpression binaryExpression = expression as BinaryExpression; left = GetStringExpression(binaryExpression.Left as Expression, tableMetadata); right = GetStringExpression(binaryExpression.Right as Expression, tableMetadata); //=NULL 換成 IS NULL !=NULL 換成 IS NOT NULL if (expression.NodeType == ExpressionType.Equal && right == "NULL") { oper = " IS "; } else if (expression.NodeType == ExpressionType.NotEqual && right == "NULL") { oper = " IS NOT "; }else { oper = GetOperator(expression.NodeType); } return string.Format("({0}{1}{2})", left, oper, right); } else if (expression is MemberExpression) { MemberExpression memberExpression = expression as MemberExpression; if (memberExpression.Expression is ParameterExpression) { //欄位 return tableMetadata.Properties[memberExpression.Member.Name].Name; } } return GetExpressionValue(expression); }
/// <summary> /// 取得操作子 /// </summary> /// <param name="type"></param> /// <returns></returns> private static string GetOperator(ExpressionType type) { switch (type) { case ExpressionType.AndAlso: return " AND "; case ExpressionType.Equal: return "="; case ExpressionType.GreaterThan: return ">"; case ExpressionType.GreaterThanOrEqual: return ">="; case ExpressionType.LessThan: return "<"; case ExpressionType.LessThanOrEqual: return "<="; case ExpressionType.NotEqual: return "<>"; case ExpressionType.OrElse: return " OR "; default: throw new ArgumentException("不支援的Where操作"); } }
下載原始碼
這個Extention在寫這一篇時,只跑過幾次滿足我的需求,事實上還有很多Expression沒有去解析,如In、Like、Sql Function等等,我不保證沒有Bug,所以請當參考就好。