[Entity Framework] 如何讓 Stored Procedure 與原來 Entity Framework 的運作在同一個 Transaction 裡

一. 前言

日前在撰寫 C# + Database First Entity Framework 6.x 程式時, 先呼叫 SP (該 SP 會異動資料庫), 再利用 EF 寫入其它的異動資料; 但在 SaveChanges() 時, 發生例外, 卻發現原來以 SP 異動的資料並未還原 (rollback). 覺得很怪, 於是寫了一支主控台程式, 以探究原因.

註: 為避免文章過於冗長, 本文以 SP 代表 Stored Procedure, EF 代表 Entity Framework. Tx 代表 Transacton

程式邏輯大致如下, 建議下載 github 上分享的原始程式, 實際執行看看.

using (xxEntities ctx = new xxEntities())
{
 //1.. 呼叫 SP, 並取回值
 ObjectParameter orderno = new ObjectParameter("po_order_no", typeof(String));
 ctx.usp_get_order_no(orderno);

 //2.. 新增資料, 以 EF 寫入
 //...
 ctx.SaveChanges();
}

二. 問題重現

1.. 重要程式碼如下:

public void CallSpWithExplicitTx()
{
 using (EFTestDBEntities ctx = new EFTestDBEntities())
 {
  ObjectParameter orderno = new ObjectParameter("po_order_no", typeof(String));
  ctx.usp_get_order_no(orderno);
  Console.WriteLine(orderno.Value);

  ctx.MyOrders.Add(new MyOrder()
  {
   OrderNo = orderno.Value.ToString(),
   ShipName = "jasper",
   ShipAddress = "taipei",
   TotalAmt = 1000
  });
  //故意不作 SaveChange(), 查結果, 可以發現 OrderNoGenerators 這個 table 的資料有異動
  //ctx.SaveChanges();
 }
}

2.. 打開 SQL Server Profiler, 將 Transaction 列入觀察, 相關設定, 可參考 Where Is Transaction Events In SQL Server Profiler?

3.. 執行程式, 可以發現, 在呼叫 SP 時, 會有明確加上 Transaction 的處理, 即使沒有 SaveChanges(), SP 所作的異動, 還是被認可的.

三. 探索方案_1 (StackOverflow)

這個方案是 StackOverflow 查到的, 與 "問題重現" 的程式碼執行相比, 在 SQL Server Profiler 雖然不會出現 Begin Tx, Commit Tx, 但不能解決 "讓 Stored Procedure 與原來 Entity Framework 的運作在同一個 Transaction 裡" 的需求.

1.. 重要程式碼如下:

相較於前述的程式碼, 只多加了 EnsureTransactionsForFunctionsAndCommands = false 的設定.

public void CallSpWithImplicitTx()
{
 using (EFTestDBEntities ctx = new EFTestDBEntities())
 {
  //
  ctx.Configuration.EnsureTransactionsForFunctionsAndCommands = false;
  //
  ObjectParameter orderno = new ObjectParameter("po_order_no", typeof(String));
  ctx.usp_get_order_no(orderno);
  Console.WriteLine(orderno.Value);

  ctx.MyOrders.Add(new MyOrder()
  {
   OrderNo = orderno.Value.ToString(),
   ShipName = "jasper",
   ShipAddress = "taipei",
   TotalAmt = 1000
  });
  //故意不作 SaveChange(), 查結果, 可以發現 OrderNoGenerators 這個 table 的資料有異動
  //ctx.SaveChanges();
 }
}

2.. 執行程式, 可以發現, 在呼叫 SP 時, 不會明確加上 Transaction 的處理, 即使沒有 SaveChanges(), SP 所作的異動, 還是被認可的.

註: 雖然沒有 BEGIN Tx, COMMIT Tx, 但因為 SQL Server 預設沒有 TRANSACTION 的 INSERT / UPDATE / DELETE 就會作 AUTO COMMIT, 所以沒有解決問題

四. 探索方案_2 (MSDN)

這個方案是 MSDN 查到的, 與 "問題重現" 的程式碼執行相比, 可以解決 "讓 Stored Procedure 與原來 Entity Framework 的運作在同一個 Transaction 裡" 的需求.

1.. 重要程式碼如下:

註: using (MyDBEntities ctx = new MyDBEntities()) 還沒有 open connection

註: using (var tx = ctx.Database.BeginTransaction()) 會先 open connection 至 DB, 再 BEGIN TX

public void CallSpWithSameTxInContext()
{
 using (EFTestDBEntities ctx = new EFTestDBEntities())
 {
  using (var tx = ctx.Database.BeginTransaction())
  {
   ObjectParameter orderno = new ObjectParameter("po_order_no", typeof(String));
   ctx.usp_get_order_no(orderno);
   Console.WriteLine(orderno.Value);

   ctx.MyOrders.Add(new MyOrder()
   {
    OrderNo = orderno.Value.ToString(),
    ShipName = "jasper",
    ShipAddress = "taipei",
    TotalAmt = 1000
   });
   //試一下 SaveChanges(), 再 RollbackTransaction(), 看一下結果
   try
   {
    ctx.SaveChanges();
    //tx.Commit();
    tx.Rollback();
   }
   catch (Exception ex)
   {
    tx.Rollback();
    Console.WriteLine(ex.ToString());
   }
  }
 }
}

2.. 執行程式, 可以發現, 在呼叫 SP 時, 會明確加上 Transaction 的處理, 最後會作 Rollback

五. 總結

不是很清楚為何 EF 6.x 在呼叫 SP 時, 會單獨一個 Tx, 但這個功能, 應該可以用在將 Log 寫到 DB 的情境, 總不能將一般的處理與 Log 共用同一個 Tx, 萬一整個 Tx Rollback, 那會連 Log 都 Rollback.

六. 參考資料

1.. Dixin's Blog, Where Is Transaction Events In SQL Server Profiler?

--> 這篇主要描述如何在 SQL Server Profiler 中, 列入 Transaction 的相關指標列為觀察的對象.

2.. StackOverflow, EF6 wraps every single stored procedure call in its own transaction. How to prevent this?

--> 這篇提到 ctx.Configuration.EnsureTransactionsForFunctionsAndCommands = false; 但並無法解決 "如何讓 Stored Procedure 與原來 Entity Framework 的運作在同一個 Transaction 裡" 的問題

3.. MSDN, Entity Framework Working with Transactions (EF6 Onwards)

--> 這篇介紹 EF6 的 Transaction 運作機制, 但只有第1個範例適合 Database First, 其它幾個, 看來都是 Code First

這封郵件來自 Evernote。Evernote 是您專屬的工作空間，免費下載 Evernote