Entity Framework批次Update與Delete

文章目錄

• 1.使用DbCommand.ExecuteNonQuery或ObjectContext.ExecuteStoreCommand去執行，使用如下的SQL。
• 2.跑迴圈
• 執行範例
• 程式解說
• 下載原始碼

用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是Wheredb.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的Expressiondb.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操作");    }}

 

下載原始碼

ObjectSetExtention.cs

這個Extention在寫這一篇時，只跑過幾次滿足我的需求，事實上還有很多Expression沒有去解析，如In、Like、Sql Function等等，我不保證沒有Bug，所以請當參考就好。