解決因對EFCore執行SQL方法不熟練而引起的問題

AZRNG發表於2023-10-08
SQL

前言

本文測試環境：VS2022+.Net7+MySQL

因為我想要實現使用EFCore去執行sql檔案，所以就用到了方法ExecuteSqlAsync，然後就產生了下面的問題，首先因為方法接收的引數是一個FormattableString，它又是一個抽象類，所以我就瞎測試使用下面方式構建

using var db = new OpenDbContext();
var mysqlSql2 = "INSERT INTO test1008.menus (id, name, create_time, modify_time) VALUES (default, '張颯1', '2023-10-08 17:26:45.000000', '2023-10-08 17:26:47.000000');";
var result = await db.Database.ExecuteSqlAsync($"{mysqlSql2}");

編譯沒有報錯，但是一個執行，結果居然報錯了

Unhandled exception. MySqlConnector.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''INSERT INTO test1008.menus (id, name, create_time, modify_time) VALUES (default' at line 1

看著這個錯誤我一直以為是哪個name列的值寫的有問題，去資料庫執行，沒問題成功新增，程式碼中那個值換了好幾次，就是不行，翻翻微軟文件

using (var context = new BloggingContext())
{
    var rowsModified = context.Database.ExecuteSql($"UPDATE [Blogs] SET [Url] = NULL");
}

這不是和官方示例寫的一樣？難道是EFCore的bug？

尋找問題

抱著肯定不是EFCore bug的想法，檢視原始碼吧

public static Task<int> ExecuteSqlAsync(
  this DatabaseFacade databaseFacade,
  FormattableString sql,
  CancellationToken cancellationToken = default (CancellationToken))
{
  return databaseFacade.ExecuteSqlRawAsync(sql.Format, (IEnumerable<object>) sql.GetArguments(), cancellationToken);
}

然後我就發現它原始碼裡面還是從這個入參的sql中獲取到對應的sql以及GetArguments，那麼我就像提前構建一個FormattableString看下取到的值是多少

FormattableString mysqlSql = $"INSERT INTO test1008.menus (id, name, create_time, modify_time) VALUES (default, '張颯1', '2023-10-08 17:26:45.000000', '2023-10-08 17:26:47.000000');";

mysqlSql.Format.Dump();
mysqlSql.GetArguments().Dump();

這裡的dump方法可以檢視：此處

這不是也沒問題嗎，然後突然發現下面程式碼可以正常執行

using var db = new OpenDbContext();
FormattableString mysqlSql = $"INSERT INTO test1008.menus (id, name, create_time, modify_time) VALUES (default, '張颯1', '2023-10-08 17:26:45.000000', '2023-10-08 17:26:47.000000')";

var result = await db.Database.ExecuteSqlAsync(mysqlSql);

那看來問題就出在ExecuteSqlAsync方法的入參上了，然後我這麼測試

FormattableString mysqlSql = $"INSERT INTO test1008.menus (id, name, create_time, modify_time) VALUES (default, '張颯1', '2023-10-08 17:26:45.000000', '2023-10-08 17:26:47.000000');";

mysqlSql.Format.Dump();
mysqlSql.GetArguments().Dump();

FormattableString sql2 = $"{mysqlSql}";
sql2.Format.Dump();
sql2.GetArguments().Dump();

解決問題

到這裡看來原因就出來了，是因為$的問題哦，那麼解決方案就成先定義一個FormattableString型別直接傳進入，或者

using var db = new OpenDbContext();
var name = "李四";
var result = await db.Database.ExecuteSqlAsync(
    $"INSERT INTO test1008.menus (id, name, create_time, modify_time) VALUES (default, '{name}', '2023-10-08 17:26:45.000000', '2023-10-08 17:26:47.000000');");
result.Dump();

不過這裡需要注意，ExecuteSqlAsync方法裡面的sql在EFCore中並沒有給你放到一個事務裡面，所以如果有需要，那麼就只好自己建立事務了

using var db = new OpenDbContext();
var name = "李四";
using var tran = db.Database.BeginTransaction();
var result = await db.Database.ExecuteSqlAsync(
    $"INSERT INTO test1008.menus (id, name, create_time, modify_time) VALUES (default, '{name}', '2023-10-08 17:26:45.000000', '2023-10-08 17:26:47.000000');");
tran.Commit();
result.Dump();

未完

雖然解決了那個報錯的問題，但是還是沒解決我想執行sql檔案，那隻好換個方法去寫了，自己去獲取連線然後操作ADO.NET去執行吧(這裡暫且先不用Dapper)，我麻溜寫下下面示例程式碼，順帶考慮到那個要裹在一個事務裡面的情況（未封裝，僅供參考）

// 模擬sql檔案
var mysqlSql = @"INSERT INTO test1008.menus (id, name, create_time, modify_time) VALUES (default, '張颯1', '2023-10-08 17:26:45.000000', '2023-10-08 17:26:47.000000');
INSERT INTO test1008.menus (id, name, create_time, modify_time) VALUES (default, '張颯1', 'error情況', '2023-10-08 17:26:47.000000');";

using var db = new OpenDbContext();
using var connection = db.Database.GetDbConnection();
using var tran = db.Database.BeginTransaction();
var cmd = connection.CreateCommand();
cmd.CommandText = mysqlSql;
int i = await cmd.ExecuteNonQueryAsync();
await tran.CommitAsync();
i.Dump();

執行居然報錯：The transaction associated with this command is not the connection's active transaction ，還好報錯中給了一個文件網站，網站中說我應該這麼操作，將我開啟的事務傳遞給cmd變數，也就是

// error 不能將源型別 'Microsoft.EntityFrameworkCore.Storage.IDbContextTransaction' 轉換為目標型別 'System.Data.Common.DbTransaction
cmd.Transaction = tran;

一臉懵逼這倆都對不上咋給，然後在看tran.的時候手滑點了一下，出來一個

cmd.Transaction = tran.GetDbTransaction();

原始碼如下

public static DbTransaction GetDbTransaction(this IDbContextTransaction dbContextTransaction) => dbContextTransaction is IInfrastructure<DbTransaction> accessor ? accessor.GetInfrastructure<DbTransaction>() : throw new InvalidOperationException(RelationalStrings.RelationalNotInUse);

這不是巧了，修改上面的程式碼如下

var mysqlSql = @"INSERT INTO test1008.menus (id, name, create_time, modify_time) VALUES (default, '張颯1', '2023-10-08 17:26:45.000000', '2023-10-08 17:26:47.000000');
INSERT INTO test1008.menus (id, name, create_time, modify_time) VALUES (default, '張颯1', '20xxcfdsfs000', '2023-10-08 17:26:47.000000');";

using var db = new OpenDbContext();
using var connection = db.Database.GetDbConnection();
using var tran = db.Database.BeginTransaction();
var cmd = connection.CreateCommand();
cmd.CommandText = mysqlSql;
cmd.Transaction = tran.GetDbTransaction();
int i = await cmd.ExecuteNonQueryAsync();
await tran.CommitAsync();
i.Dump();

因為我的sql第二條是錯誤的，所以執行成功報錯，資料庫中也不存在資料，這就是想要的效果。

再次修改sql後執行成功，資料庫存在兩條資料，實現了我的需求，完成。

FormattableString 介紹

以下內容來自chatgpt

FormattableString 是 C# 中的一個類，用於支援可格式化字串的操作。它是在 .NET Framework 4.6 版本中引入的。

FormattableString 類的目的是提供一種方便的方式來建立可格式化的字串。它可以使用類似於字串插值的語法，但不會立即進行字串插值操作，而是保留可格式化字串的原始形式和引數的值。這使得開發人員可以在稍後的時間點或其他上下文中決定如何格式化字串，以便滿足特定的需求。

在使用 FormattableString 時，可以透過使用 $ 符號字首來建立一個可格式化字串，例如：

FormattableString message = $"Hello, {name}. The current time is {DateTime.Now}.";

在EFCore中ExecuteSql方法使用該型別是用來防止SQL隱碼攻擊的

相關文章