在 EF Core DbContext 使用 Dapper 並參與 Transaction
source link: https://blog.darkthread.net/blog/dapper-with-ef-core/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
寫 .NET 資料庫相關程式該用 EF/ORM 還是自己寫 SQL？就像手排車 vs 自排車，各有優劣及擅長的場合，亦各有支持者，我自己則是瀨尿牛丸派，單純 CRUD 用 EF (或自製 ORM) 享受強型別保護及不沾 SQL 的清爽，至於複雜查詢、批次更新刪除，則回歸自己寫 SQL 以確保執行效能。(這個議題的深入討論可參考舊文：閒聊：用 LINQ 還是自己寫 SQL？)
遇到需要自己寫 SQL 的場合，EF Core 3+ 提供了 FromSqlRaw()、ExecuteSqlRaw()、FromSqlInterpolated() 參考 等方法，可使用 SQL 字串內嵌 @paramName、{0}、 整合參數直接下 SQL 指令，例如：
var user = "johndoe";
var blogs = context.Blogs
.FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user)
.ToList();
var user = "johndoe";
var blogs = context.Blogs
.FromSqlInterpolated($"EXECUTE dbo.GetMostPopularBlogsForUser {user}", user)
.ToList();
var user = new SqlParameter("user", "johndoe");
var blogs = context.Blogs
.FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser @user", user)
.ToList();
注意：前兩種寫法 EF Core 會將 {0}、 轉為 DB 參數，並非直接展開字串，絕不可自作主張改成 String.Format() 或 $"......"，否則會搞出 SQL Injection 神仙難救。
不過，講到寫 SQL 指令整合，Dapper一出，誰與爭鋒。Query()、Execute() 在彈性與功能遙遙領先 EF Core 的 FromSqlRaw() 及 ExecuteSqlRaw()，若不介意專案多參照一個程式庫，直上 Dapper 是更明智的選擇。
在 EF Core DbContext 要使用 Dapper，起手式是用 DbContext.Database.GetConnection() 取得現在的連線字串，剩下就跟一般寫法相同，不需額外學習。我唯一遇到的問題，如果 Dapper 動作要跟 DbContext SaveChanges() 更新包成 Transaction，該怎麼做？
在 EF Core 可用 DbContext.Database.BeginTransaction() 啟用交易，將多個 SaveChanges() 包成交易。(參考：.NET Core 實驗室 - EF Core 與 Transaction) 沿用同一觀念，我們在交易範圍內 DbContext.Database.GetConnection() 取得 IDbConnection，由 EF Core 交易物件 IDbContextTransaction.GetDbTransaction() 取得 DbTransaction (需 using Microsoft.EntityFrameworkCore.Storage 取得擴充方法)，就能將 Dapper 動作納入交易範圍。
範例程式如下：
using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;
using Dapper;
using Microsoft.EntityFrameworkCore.Storage;
namespace Models
{
public class BlogDbContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(
@"Server=(localdb)\mssqllocaldb;Initial Catalog=Blogging;Integrated Security=SSPI");
}
public void TestEFCoreTransactionWithDapper(bool commit = false)
{
using (var trn = Database.BeginTransaction())
{
var blog = new Blog{
Url = "https://blog.darkthread.net",
Blogger = "darkthread"
};
Blogs.Add(blog);
SaveChanges();
// 呼叫 Database.GetDbConnection() 取得 IDbConnection 物件
var cn = Database.GetDbConnection();
// using Microsoft.EntityFrameworkCore.Storage 以取得 GetDbTransaction() 擴充方法
// 取得 IDbTransaction 物件作為 cn.Execute 或 cn.Query 參數
cn.Execute("INSERT INTO Blogs (Url, BLogger) VALUES (@url, @blogger)", new {
url = "https://www.darkthread.net",
blogger = "Jeffrey"
}, transaction: trn.GetDbTransaction());
if (commit) trn.Commit();
else trn.Rollback();
}
}
}
public class Blog
{
public int BlogId { get; set; }
[Required]
[MaxLength(1024)]
public string Url { get; set; }
[Required]
[MaxLength(64)]
public string Blogger { get; set; }
}
}
不過，這樣子每次要 GetDbConnection()、GetDbTransaction() 太囉嗦了，於是我寫了擴充方法為 DbContext.Database 加上 DapperQuery<T>() 與 DapperExecute()，試著讓生活更美好。
using Dapper;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Storage;
using System.Data;
namespace Drk.AspNetCore.Extensions
{
/// <summary>
/// EF Core Database Extensions for Dapper
/// </summary>
public static class EFCoreExtensions
{
/// <summary>
/// Execute with Dapper in EF Core, support transaction if enabled
/// </summary>
/// <param name="database">DatabaseFacade</param>
/// <param name="commandText">The SQL to execute for the query.</param>
/// <param name="param">The parameters to pass, if any.</param>
/// <param name="commandTimeout">The command timeout (in seconds).</param>
/// <param name="commandType">The type of command to execute.</param>
/// <returns>The number of rows affected.</returns>
public static int DapperExecute(this DatabaseFacade database, string commandText, object param = null, int? commandTimeout = null, CommandType? commandType = null)
{
var cn = database.GetDbConnection();
IDbTransaction trn = database.CurrentTransaction?.GetDbTransaction()!;
return cn.Execute(commandText, param, trn, commandTimeout, commandType);
}
/// <summary>
/// Query with Dapper in EF Core, support transaction if enabled
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="database">DatabaseFacade</param>
/// <param name="commandText">The SQL to execute for this query.</param>
/// <param name="param">The parameters to use for this query.</param>
/// <param name="buffered">Whether to buffer the results in memory.</param>
/// <param name="commandTimeout">The command timeout (in seconds).</param>
/// <param name="commandType">The type of command to execute.</param>
/// <returns></returns>
public static IEnumerable<T> DapperQuery<T>(this DatabaseFacade database, string commandText, object param, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
{
var cn = database.GetDbConnection();
IDbTransaction trn = database.CurrentTransaction?.GetDbTransaction()!;
return cn.Query<T>(commandText, param, trn, buffered, commandTimeout, commandType);
}
}
}
如此，原本的
var cn = Database.GetDbConnection();
cn.Execute("INSERT INTO Blogs (Url, BLogger) VALUES (@url, @blogger)", new {
url = "https://www.darkthread.net",
blogger = "Jeffrey"
}, transaction: trn.GetDbTransaction());
Database.DapperExecute("INSERT INTO Blogs (Url, BLogger) VALUES (@url, @blogger)", new {
url = "https://www.darkthread.net",
blogger = "Jeffrey"
});
是不是優雅許多？DapperQuery<T>() 與 DapperExecute() 會偵測 DbContext 是否已啟用 Transaction (由 Database.CurrentTransaction 是否為 null 判斷)，若已啟用還會自動加入 Transaction 範圍，非常方便。
打通這個環節，有種豁然開朗的感覺，未來在 EF Core 要使用 Dapper 就順手囉。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK