黃偉榮的學習筆記

軟體的世界變化萬千，小小的我只能在這洪流奮發向上以求立足

2010-08-20

Entity Framework批次Update與Delete

  • 41112
  • 0
  • .Net
  • 2011-04-10

用Linq To Sql或Linq To Entity Framework，在Select方面用起來有如神助飛快的方便，但在批次Update或Delete方面就遜色很多，完全就如Ado.Net時期下SQL一樣，沒有物件導向可言。

本篇將介紹如何用挖出Entity Framework的Metadata，以Expression的方式組成Sql。

用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中。

image

圖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;
}

image

圖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，所以請當參考就好。

目前還有提供第二版

ObjectSetExtention V2

本頁段落