[EF] Update 語法觀察

延續 [SQL] Foreign Key - Update 觀念，該篇筆記是來觀察 EF 不同 update 方式，產生的 TSQL update 語法是否會包含值沒有變化欄位，透過 SQL Profile 來觀察 TSQL 語法

EF Update 資料方式

using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;

namespace EFUpdate
{
    internal class Program
    {
        static void Main(string[] args)
        {
            Random r = new Random(Guid.NewGuid().GetHashCode());
            int days = r.Next(0, 10);

            AdventureWorks2019 context = new AdventureWorks2019();
            
            var entityUpdate = new PurchaseOrderDetail
            {
                PurchaseOrderID = 1,
                PurchaseOrderDetailID = 1,
                DueDate = new DateTime(2011, 4, 30),
                OrderQty = 4,
                ProductID = 1,
                UnitPrice = 58.26m,
                LineTotal = 201.04m,
                ReceivedQty = 3,
                RejectedQty = 0,
                StockedQty = 3,
                // 修改 ModifiedDate
                ModifiedDate = DateTime.Today.AddDays(days)
            };

            #region 方法一：Attach

            context.PurchaseOrderDetail.Attach(entityUpdate);
            context.Entry(entityUpdate).Property(nameof(PurchaseOrderDetail.ModifiedDate)).IsModified = true;
            context.SaveChanges();

            #endregion

            #region 方法二：AddOrUpdate

            context.PurchaseOrderDetail.AddOrUpdate(entityUpdate);
            context.SaveChanges();

            #endregion

            var entityFromDB = context.PurchaseOrderDetail.Single(p => p.PurchaseOrderID == 1);

            #region 方法三：直接更新

            entityFromDB.ProductID = 1;
            entityFromDB.ModifiedDate = DateTime.Today.AddDays(days);
            context.SaveChanges();

            #endregion

            #region 方法四：設定 EntityState

            entityFromDB.ModifiedDate = DateTime.Today.AddDays(days);
            context.Entry(entityFromDB).State = EntityState.Modified;
            context.SaveChanges();

            #endregion

            #region 方法五：SetValue

            context.Entry(entityFromDB).CurrentValues.SetValues(entityUpdate);
            context.SaveChanges();

            #endregion

        }
    }
}

接續會把各 EF update 語法和 Profile 側錄到的 TSQL update 並列

 
方法一：Attach

context.PurchaseOrderDetail.Attach(entityUpdate);
context.Entry(entityUpdate).Property(nameof(PurchaseOrderDetail.ModifiedDate)).IsModified = true;
context.SaveChanges();

exec sp_executesql N'UPDATE [Purchasing].[PurchaseOrderDetail]
    SET[ModifiedDate] = @0
    WHERE(([PurchaseOrderID] = @1) AND([PurchaseOrderDetailID] = @2))

    SELECT[LineTotal], [StockedQty]
FROM[Purchasing].[PurchaseOrderDetail]
    WHERE @@ROWCOUNT > 0 AND[PurchaseOrderID] = @1 AND[PurchaseOrderDetailID] = @2',
    N'@0 datetime2(7),@1 int,@2 int',@0 = '2022 - 08 - 29 00:00:00',@1 = 1,@2 = 1

方法二：AddOrUpdate

context.PurchaseOrderDetail.AddOrUpdate(entityUpdate);
context.SaveChanges();

exec sp_executesql N'UPDATE [Purchasing].[PurchaseOrderDetail]
    SET[ModifiedDate] = @0
    WHERE(([PurchaseOrderID] = @1) AND([PurchaseOrderDetailID] = @2))

    SELECT[LineTotal], [StockedQty]
FROM[Purchasing].[PurchaseOrderDetail]
    WHERE @@ROWCOUNT > 0 AND[PurchaseOrderID] = @1 AND[PurchaseOrderDetailID] = @2',
    N'@0 datetime2(7),@1 int,@2 int',@0 = '2022 - 08 - 26 00:00:00',@1 = 1,@2 = 1

方法三：直接更新

entityFromDB.ProductID = 1;
entityFromDB.ModifiedDate = DateTime.Today.AddDays(days);
context.SaveChanges();

exec sp_executesql N'UPDATE [Purchasing].[PurchaseOrderDetail]
    SET[ModifiedDate] = @0
    WHERE(([PurchaseOrderID] = @1) AND([PurchaseOrderDetailID] = @2))

    SELECT[LineTotal], [StockedQty]
FROM[Purchasing].[PurchaseOrderDetail]
    WHERE @@ROWCOUNT > 0 AND[PurchaseOrderID] = @1 AND[PurchaseOrderDetailID] = @2',
    N'@0 datetime2(7),@1 int,@2 int',@0 = '2022 - 08 - 24 00:00:00',@1 = 1,@2 = 1

方法四：設定 EntityState

entityFromDB.ModifiedDate = DateTime.Today.AddDays(days);
context.Entry(entityFromDB).State = EntityState.Modified;
context.SaveChanges();

exec sp_executesql N'UPDATE [Purchasing].[PurchaseOrderDetail]
    SET[DueDate] = @0, [OrderQty] = @1, [ProductID] = @2, [UnitPrice] = @3, [ReceivedQty] = @4, [RejectedQty] = @5, [ModifiedDate] = @6
    WHERE(([PurchaseOrderID] = @7) AND([PurchaseOrderDetailID] = @8))

    SELECT[LineTotal], [StockedQty]
FROM[Purchasing].[PurchaseOrderDetail]
    WHERE @@ROWCOUNT > 0 AND[PurchaseOrderID] = @7 AND[PurchaseOrderDetailID] = @8',
    N'@0 datetime2(7),@1 smallint,@2 int,@3 decimal(19, 4),@4 decimal(8, 2),@5 decimal(8, 2),@6 datetime2(7),@7 int,@8 int',@0 = '2011 - 04 - 30 00:00:00',@1 = 4,@2 = 1,@3 = 58.2600,@4 = 3.00,@5 = 0,@6 = '2022 - 08 - 23 00:00:00',@7 = 1,@8 = 1

方法五：SetValue

context.Entry(entityFromDB).CurrentValues.SetValues(entityUpdate);
context.SaveChanges();

exec sp_executesql N'UPDATE [Purchasing].[PurchaseOrderDetail]
    SET[ModifiedDate] = @0
    WHERE(([PurchaseOrderID] = @1) AND([PurchaseOrderDetailID] = @2))

    SELECT[LineTotal], [StockedQty]
FROM[Purchasing].[PurchaseOrderDetail]
    WHERE @@ROWCOUNT > 0 AND[PurchaseOrderID] = @1 AND[PurchaseOrderDetailID] = @2',
    N'@0 datetime2(7),@1 int,@2 int',@0 = '2022 - 08 - 23 00:00:00',@1 = 1,@2 = 1

從上述可以發現，只有 [方法四：設定 EntityState] 會產生全部更新欄位，其他都能自動識別資料本身是否有異動

• 參考資料
• [C#.NET][Entity Framework] Update Record
• 使用 EF Core Attach() 與 Entry().State 進行更新