資料庫程式有一種常見的應用情境:傳入一筆資料,以 Primary Key 比對,若該筆資料不存在就新增,若已存在則改更新欄位。

這種動作被稱為 INSERT OR UPDATE,或簡稱 UPSERT,資料庫很多會提供對映做法,像是 MySQL 用 INSERT INTO Table (...) VALUES (...) ON DUPLICATE KEY UPDATE PrimKey = ...,Oracle 是用

MERGE INTO Table 
USING dual
ON (Table.PrimKey = ...)
WHEN NOT MATCHED THEN
    INSERT (...) INTO VALUES (...)
WHEND MATCHED THEN
    UPDATE SET ... = ...

參考:

要在 EF Core 實現,直覺會這樣寫:(註:Model、DbContext 借用自借用ASP.NET Core 新增修改刪除(CRUD)介面傻瓜範例)

void Upsert(DailyRecord record) {
    var exist = dbCtx!.Records.FirstOrDefault(o => o.Date == record.Date);
    if (exist == null) {
        dbCtx.Records.Add(record);
    } else {
        exist.Date = record.Date;
        exist.EventSummary = record.EventSummary;
        exist.Remark = record.Remark;
        exist.User = record.User;
    }
    dbCtx.SaveChanges();
}

寫成測試程式實地驗證一下:

using CRUDExample.Models;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

bool enableLog = false;
// 使用記憶體中的 SQLite 資料庫,來去不留痕跡
// https://blog.darkthread.net/blog/ef-core-test-with-in-memory-db/
var cn = new SqliteConnection("Data Source=:memory:");
cn.Open();
var dbOpt = new DbContextOptionsBuilder<JournalDbContext>()
    .UseSqlite(cn)
    // 設定可動態開關的 Log 輸出以觀察 SQL 語法
    .LogTo(s => {
        if (enableLog) Console.WriteLine(s);
    }, Microsoft.Extensions.Logging.LogLevel.Information)
    // 連同寫入資料庫的參數一起顯示,正式環境需留意個資或敏感資料寫入Log
    .EnableSensitiveDataLogging() 
    .Options;
var dbCtx = new JournalDbContext(dbOpt);

Action<string> print = (s) => {
    Console.ForegroundColor = ConsoleColor.Cyan;
    Console.WriteLine(s);
    Console.ResetColor();
};

//準備資料
dbCtx.Database.EnsureCreated();
dbCtx.Records.Add(new DailyRecord{
    Date = new DateTime(2022, 1, 1),
    EventSummary = "BeforeUpdate",
    Remark = "",
    User = "Jeffrey"
});
dbCtx.SaveChanges();

// 測試更新(同日期資料已存在)
print("測試更新");
Upsert(new DailyRecord{
    Date = new DateTime(2022, 1, 1),
    EventSummary = "AfterUpdate",
    Remark = "",
    User = "darkthread"
});

// 測試新增(無相同日期資料)
print("測試新增");
Upsert(new DailyRecord{
    Date = new DateTime(2022, 1, 2),
    EventSummary = "NewEntry",
    Remark = "Hello",
    User = "Jeffrey"
});


void Upsert(DailyRecord record) {
    var exist = dbCtx!.Records.FirstOrDefault(o => o.Date == record.Date);
    if (exist == null) {
        dbCtx.Records.Add(record);
    } else {
        exist.Date = record.Date;
        exist.EventSummary = record.EventSummary;
        exist.Remark = record.Remark;
        exist.User = record.User;
    }
    enableLog = true;
    dbCtx.SaveChanges();
    enableLog = false;
}

實際執行,可以觀察到程式碼有達成預期。第一筆因 2022/1/1 資料已存在,採用 UPDATE,而 EF Core 能偵測到只有 EventSummary、User 有異動,Remark 未變,UPDATE 只 SET EventSummary 及 User;而第二筆 2022/1/2 資料庫沒有,會執行 INSERT:

這個寫法 OK,但有個問題,更新現有資料的 existing.PropName = record.PropName 這段太囉嗦又沒營養,要是 Entity 有 30 個欄位,要手工敲 30 個不同欄位名稱,我無法。

研究了一下,EF Core 有個我之前忽略的方法 - PropertyValues.SetValues,用 dbCtx.Entry(exist).CurrentValues.SetValues(record); 取代一成串 existing.PropName = record.PropName 就可以了。

但這裡有個小眉角,範例 Model 的 Id 為自動跳號,直接 CurrentValues.SetValues(record) 會更動到 Id,觸發以下錯誤:

System.InvalidOperationException: 
The property 'DailyRecord.Id' is part of a key and so cannot be modified or marked as modified. 
To change the principal of an existing entity with an identifying foreign key, 
first delete the dependent and invoke 'SaveChanges', 
and then associate the dependent with the new principal.

避免方法是加上 record.Id = exist.Id;,讓 Id 跟既有資料一致,沒有異動便不會出錯。

修改版本如下:

void Upsert(DailyRecord record) {
    var exist = dbCtx!.Records.FirstOrDefault(o => o.Date == record.Date);
    if (exist == null) {
        dbCtx.Records.Add(record);
    } else {
        //Id 為自動跳號,將待更新資料 Id 設成一致
        record.Id = exist.Id;
        dbCtx.Entry(exist).CurrentValues.SetValues(record);
    }
    enableLog = true;
    dbCtx.SaveChanges();
    enableLog = false;
}

實測成功!

範例程式已放在 Github,有興趣的同學可抓回去玩。

【參考資料】

Example of how to implement insert or update logic in EF Core.


Comments

# by Ted

黑大您好請教一下: record.Id = exist.Id; dbCtx.Entry(exist).CurrentValues.SetValues(record); 可否同樣採以下方式更新,還是說兩者其實不一樣 db.Entry(record).State = EntityState.Modified; var result = db.SaveChanges();

# by Jeffrey

to Ted,關於這個議題,我決定專門寫一篇文章跟大家講解 - https://blog.darkthread.net/blog/ef-core-attach/

# by Jasper

如果只想要更新部分的屬性, 是不是就不能用這個方法? 例如DailyRecord裡面只有Date有值, 其他屬性都沒值(NULL) 那用這方法的話其他屬性也會被覆寫為NULL 算是一個小缺點? XD

# by Jeffrey

to Jasper,.SetValues(record) 適合一次更新多個屬性欄立,省去逐一設定。如果只想改 Date 其他值不動,existing.Date = record.Date 反而比較簡單。

Post a comment