Entity Framework批次Update與Delete v2

2011-04-10

12822
0
.Net
2011-10-15

這是Entity Framework批次Update與Delete這一篇的改進版，程式碼重構與加強了Expression Tree的解析，常見的情況都有支援，改用ADO.NET內建的參數。

這是Entity Framework批次Update與Delete這一篇的改進版，程式碼重構與加強了Expression Tree的解析，常見的情況都有支援，改用ADO.NET內建的參數處理，讓ADO.NET去處理Sql Injection。

主要變更

1.將產生Sql邏輯全部移到SqlBuilder，可以將Expression Tree轉成的Sql快取(目前沒有自動辦判要手動儲存)。

2.增加Let.Add方法，將欄位累加與減少出錯，可以不用取完資料，然後+1，因為讀寫有時間差，在大量資料下很容易髒讀。

3.改用ADO.NET內建的參數處理，讓ADO.NET去處理Sql Injection。

雖然有些東西直接寫T-Sql就可以，只是我不希望自己的專案中，有太多的寫法，用Entity Framework就盡量用Entity Framework的一貴作風，還好現在的C# 4.0與Visual Studio 2010對擴展還算方便，讓這些T-Sql寫法的東西，得以用Entity Framework寫法處理，享Entity Framework的好處。

主要的函式

namespace System.Linq
{
    /// <summary>
    /// 對Entity Framework增加批次處理的功能
    /// </summary>
    public static class ObjectSetExtention
    {  
        /// <summary>
        /// 批次更新
        /// </summary>
        public static int Update<TEntity>(this IObjectSet<TEntity> objextSet, Expression<Func<TEntity>> setExpression, Expression<Func<TEntity, bool>> whereExpression) where TEntity: class;
    
        /// <summary>
        /// 批次刪除
        /// </summary>
        public static int Delete<TEntity>(this IObjectSet<TEntity> objextSet, Expression<Func<TEntity, bool>> whereExpression) where TEntity: class;
    
        /// <summary>
        /// 取得MSL中Table的資訊
        /// </summary>
        public static TableMetadata GetTableMetadata(this ObjectContext context, string setName);
    }

    /// <summary>
    /// 自訂的Method讓SqlBuilder解析ExpressionTree時，做不一樣的處理
    /// </summary>
    public static class Let
    {
        [CustomExpressionAssingmentAttribute(typeof(AddIntExpressionAssingment))]
        public static int Add(int value)        

        [CustomExpressionAssingmentAttribute(typeof(AddIntExpressionAssingment))]
        public static long Add(long value)

        [CustomExpressionAssingmentAttribute(typeof(AddIntExpressionAssingment))]
        public static short Add(short value)

        [CustomExpressionAssingmentAttribute(typeof(AddIntExpressionAssingment))]
        public static byte Add(byte value)
    }
}

以下是使用範例

//Update
using (var context = new MyEntities())
{
    //新增的Let.Add用法
    context.商品主檔.Update(() => new 商品主檔 { 點閱率 = Let.Add(1) }, x => x.ID == 10);

    context.商品主檔.Update(() => new 商品主檔 { 庫存 = Let.Add(-5) }, x => x.ID == 15);
    
    context.發票主檔.Update(() => new 發票主檔 { IsEnable = true }, x => x.訂單主檔ID == 123);
    
    context.訂單主檔.Update(() => new 訂單主檔 { IsEnalbe = false }, x => x.會員主檔ID == 0);
}

using (var context = new MyEntities())
{
    var list = new int[]{1,2,3}
    context.訂單主檔.Delete(x => list.Contains(x.ID));
    context.訂單明細.Delete(x => x.訂單主檔ID == 1 && !x.IsEnable);
    context.訂單主檔.Delete(x => x.訂單編號.Contains("TEST"));
}

這是SqlBuilder的單元測試，可以看ExpressionTree解析後的Sql語法

[TestMethod()]
public void SqlBuilder_Build()
{
    var context = new LativEntities();
    var metadata = context.GetTableMetadata(typeof(訂單主檔).Name);

    //Test => SET
    using (var scope = new SqlBuilderScope(BuildType.Update, metadata))
    {
        scope.Builder.AddSet(() => new 訂單主檔 { 商品數量 = 10 });
        scope.Builder.AddWhere<訂單主檔>(x => x.ID >= 1);

        Assert.AreEqual("UPDATE [dbo].[訂單主檔] SET [商品數量]={0} WHERE [ID]>={1}", scope.Builder.Build());
    }

    //Test => No Where
    using (var scope = new SqlBuilderScope(BuildType.Update, metadata))
    {
        scope.Builder.AddSet(() => new 訂單主檔 { 商品數量 = 10 });
        Assert.AreEqual("UPDATE [dbo].[訂單主檔] SET [商品數量]={0}", scope.Builder.Build());
    }

    //Test => SET bool
    using (var scope = new SqlBuilderScope(BuildType.Update, metadata))
    {
        scope.Builder.AddSet(() => new 訂單主檔 { IsEnable = true });

        Assert.AreEqual("UPDATE [dbo].[訂單主檔] SET [IsEnable]={0}", scope.Builder.Build());
    }

    //Test => AND
    using (var scope = new SqlBuilderScope(BuildType.Update, metadata))
    {
        scope.Builder.AddSet(() => new 訂單主檔 { 商品數量 = 10 });
        scope.Builder.AddWhere<訂單主檔>(x => x.ID >= 1 && x.類別 == "ABC");

        Assert.AreEqual("UPDATE [dbo].[訂單主檔] SET [商品數量]={0} WHERE ([ID]>={1} AND [類別]={2})", scope.Builder.Build());
    }

    //Test => ==
    using (var scope = new SqlBuilderScope(BuildType.Update, metadata))
    {
        scope.Builder.AddSet(() => new 訂單主檔 { 商品數量 = Let.Add(100) });
        scope.Builder.AddWhere<訂單主檔>(x => x.ID == 1);

        Assert.AreEqual("UPDATE [dbo].[訂單主檔] SET [商品數量]=[商品數量]+{0} WHERE [ID]={1}", scope.Builder.Build());
    }

    //Test=> IsEnabule
    using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
    {
        scope.Builder.AddWhere<訂單主檔>(x => x.IsEnable);

        Assert.AreEqual("DELETE FROM [dbo].[訂單主檔] WHERE [IsEnable]={0}", scope.Builder.Build());
        Assert.AreEqual(scope.Builder.Parameters[0], true);
    }

    //Test=> !IsEnabule
    using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
    {
        scope.Builder.AddWhere<訂單主檔>(x => !x.IsEnable);

        Assert.AreEqual("DELETE FROM [dbo].[訂單主檔] WHERE [IsEnable]={0}", scope.Builder.Build());
        Assert.AreEqual(scope.Builder.Parameters[0], false);
    }

    //Test=> !IsEnabule
    using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
    {
        scope.Builder.AddWhere<訂單主檔>(x => x.IsEnable == false);

        Assert.AreEqual("DELETE FROM [dbo].[訂單主檔] WHERE [IsEnable]={0}", scope.Builder.Build());
    }

    //Test=> More
    using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
    {
        scope.Builder.AddWhere<訂單主檔>(x => x.IsEnable == false && x.IsEnable && x.ID > 0);

        Assert.AreEqual("DELETE FROM [dbo].[訂單主檔] WHERE (([IsEnable]={0} AND [IsEnable]={1}) AND [ID]>{2})", scope.Builder.Build());
        Assert.AreEqual(scope.Builder.Parameters[0], false);
        Assert.AreEqual(scope.Builder.Parameters[1], true);
        Assert.AreEqual(scope.Builder.Parameters[2], 0);
    }

    //Test=> Group
    using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
    {
        scope.Builder.AddWhere<訂單主檔>(x => !x.IsEnable && (x.ID == 1 || x.Group != "123"));

        Assert.AreEqual("DELETE FROM [dbo].[訂單主檔] WHERE ([IsEnable]={0} AND ([ID]={1} OR [Group]<>{2}))", scope.Builder.Build());
    }

    //Test=> Object
    using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
    {
        var obj = new 訂單主檔() { ID = 100 };
        scope.Builder.AddWhere<訂單主檔>(x => x.ID == obj.ID);

        Assert.AreEqual("DELETE FROM [dbo].[訂單主檔] WHERE [ID]={0}", scope.Builder.Build());
        Assert.AreEqual(scope.Builder.Parameters[0], 100);
    }

    //Test=> 相反
    using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
    {
        var obj = new 訂單主檔() { ID = 100 };
        scope.Builder.AddWhere<訂單主檔>(x => obj.ID == x.ID);

        Assert.AreEqual("DELETE FROM [dbo].[訂單主檔] WHERE [ID]={0}", scope.Builder.Build());
        Assert.AreEqual(scope.Builder.Parameters[0], 100);
    }

    //Test=> LIKE
    using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
    {
        scope.Builder.AddWhere<訂單主檔>(x => x.類別.Contains("Credit"));

        Assert.AreEqual("DELETE FROM [dbo].[訂單主檔] WHERE [類別] LIKE '%'+{0}+'%'", scope.Builder.Build());
        Assert.AreEqual(scope.Builder.Parameters[0], "Credit");
    }

    //Test=> LIKE Error
    try
    {
        using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
        {
            scope.Builder.AddWhere<訂單主檔>(x => "AAA".Contains(x.類別));
        }

        Assert.Fail();
    }
    catch (NotSupportedException)
    {
    }

    //Test=> LIKE Error
    try
    {
        using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
        {
            var obj = new 訂單主檔() { ID = 100 };

            scope.Builder.AddWhere<訂單主檔>(x => obj.類別.Contains(x.類別));
        }

        Assert.Fail();
    }
    catch (NotSupportedException)
    {
    }

    //Test=> StartsWith
    using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
    {
        scope.Builder.AddWhere<訂單主檔>(x => x.類別.StartsWith("Credit"));

        Assert.AreEqual("DELETE FROM [dbo].[訂單主檔] WHERE [類別] LIKE {0}+'%'", scope.Builder.Build());
        Assert.AreEqual(scope.Builder.Parameters[0], "Credit");
    }

    //Test=> x.Equals
    using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
    {
        var obj = new 訂單主檔() { ID = 100 };

        scope.Builder.AddWhere<訂單主檔>(x => x.ID.Equals(10) && obj.ID.Equals(x.ID));

        Assert.AreEqual("DELETE FROM [dbo].[訂單主檔] WHERE ([ID]={0} AND [ID]={1})", scope.Builder.Build());
        Assert.AreEqual(scope.Builder.Parameters[0], 10);
        Assert.AreEqual(scope.Builder.Parameters[1], 100);
    }

    //Test=> object.Equals
    using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
    {
        scope.Builder.AddWhere<訂單主檔>(x => object.Equals(10, x.ID));

        Assert.AreEqual("DELETE FROM [dbo].[訂單主檔] WHERE [ID]={0}", scope.Builder.Build());
        Assert.AreEqual(scope.Builder.Parameters[0], 10);
    }

    //Test=> 自己比自已
    using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
    {
        scope.Builder.AddWhere<訂單主檔>(x => x.ID == x.ID && x.ID.Equals(x.ID));

        Assert.AreEqual("DELETE FROM [dbo].[訂單主檔] WHERE ([ID]=[ID] AND [ID]=[ID])", scope.Builder.Build());
    }

    //Test=> List IS NULL
    using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
    {
        var list = new List<int>();
        scope.Builder.AddWhere<訂單主檔>(x => list.Contains(x.ID));

        Assert.AreEqual("DELETE FROM [dbo].[訂單主檔] WHERE [ID] IS NULL", scope.Builder.Build());
    }

    //Test=> List IS NOT NULL
    using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
    {
        var list = new List<int>();
        scope.Builder.AddWhere<訂單主檔>(x => !list.Contains(x.ID));

        Assert.AreEqual("DELETE FROM [dbo].[訂單主檔] WHERE [ID] IS NOT NULL", scope.Builder.Build());
    }

    //Test=> List
    using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
    {
        var list = new List<int>() { 100, 200 };
        scope.Builder.AddWhere<訂單主檔>(x => list.Contains(x.ID));

        Assert.AreEqual("DELETE FROM [dbo].[訂單主檔] WHERE [ID] IN ({0},{1})", scope.Builder.Build());
    }

    //Test=> List NOT
    using (var scope = new SqlBuilderScope(BuildType.Delete, metadata))
    {
        var list = new List<int>() { 100, 200 };
        scope.Builder.AddWhere<訂單主檔>(x => !list.Contains(x.ID));

        Assert.AreEqual("DELETE FROM [dbo].[訂單主檔] WHERE [ID] NOT IN ({0},{1})", scope.Builder.Build());
    }
}

下載ObjectSetExtentionV2

Entity Framework

回首頁