在 Entity Framework Core 下使用純 SQL 方式取得資料庫的資料

自己在使用 Entity Framework 或 Entity Framework Core(以下簡稱 EF Core) 時，需要以純 SQL 方式來操作資料庫的場合還真的不多，而剛好最近在學用 Dapper 時，突發奇想說如果不靠 Dapper 只使用 EF Core 的話，是不是也能達成類似目的。那這邊先說結論，試過後還是 Dapper 比較香XD，但如果真的不想讓專案再多依賴一個外部套件的話，單靠 EF Core 也能辦到就是，但就會比較囉嗦，因此這邊就先筆記一些自己用到的案例及解決方式供以後參考。

##**準備測試資料** 先在資料庫建立一個產品資料表 ```sql CREATE TABLE [dbo].[Product] ( [Id] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY, [Name] [nvarchar](450) NULL, [ListPrice] [decimal](18, 2) NOT NULL, [Price] [decimal](18, 2) NOT NULL, [Quantity] [int] NOT NULL, [EndOn] [datetime2](7) NOT NULL DEFAULT (getutcdate()) [CreatedOn] [datetime2](7) NOT NULL DEFAULT (getutcdate()) ) GO ```

資料表欄位	資料型態	說明
Id	BIGINT	產品編號
Name	NVARCHAR(450)	產品名稱
ListPrice	DECIMAL(18, 2)	牌價
Price	DECIMAL(18, 2)	出售價格
Quantity	INT	售出數量
EndOn	DATETIME2(7)	販賣截止時間
CreatedOn	DATETIME2(7)	建立時間

然後建立以下初始資料等等測試要用 ```sql INSERT INTO dbo.[Product] ([Name], ListPrice, Price, Quantity, EndOn, CreatedOn) VALUES ('產品A', 1000, 1100, 5, '2021-04-05 23:59:59.9999999', SYSDATETIME()); INSERT INTO dbo.[Product] ([Name], ListPrice, Price, Quantity, EndOn, CreatedOn) VALUES ('產品B', 2000, 2400, 10, '2021-04-10 23:59:59.9999999', SYSDATETIME()); INSERT INTO dbo.[Product] ([Name], ListPrice, Price, Quantity, EndOn, CreatedOn) VALUES ('產品C', 600, 650, 30, '2021-05-31 23:59:59.9999999', SYSDATETIME()); INSERT INTO dbo.[Product] ([Name], ListPrice, Price, Quantity, EndOn, CreatedOn) VALUES ('產品D', 3000, 5000, 20, '2021-06-25 23:59:59.9999999', SYSDATETIME()); INSERT INTO dbo.[Product] ([Name], ListPrice, Price, Quantity, EndOn, CreatedOn) VALUES ('產品E', 2000, 2500, 10, '2021-06-30 23:59:59.9999999', SYSDATETIME()); ``` 查詢該資料表應該會有 5 筆資料 ```sql SELECT * FROM Product Id | Name | ListPrice | Price | Quantity | EndOn | CreatedOn ----------------------------------------------------------------------------------------------------------- 1 | 產品A | 1000.00 | 1100.00 | 5 | 2021-04-05 23:59:59.9999999 | 2021-03-25 08:16:45.0433333 2 | 產品B | 2000.00 | 2400.00 | 10 | 2021-04-10 23:59:59.9999999 | 2021-03-25 08:16:45.0433333 3 | 產品C | 600.00 | 650.00 | 30 | 2021-05-31 23:59:59.9999999 | 2021-03-25 08:16:45.0433333 4 | 產品D | 3000.00 | 5000.00 | 20 | 2021-06-25 23:59:59.9999999 | 2021-03-25 08:16:45.0433333 5 | 產品E | 2000.00 | 2500.00 | 10 | 2021-06-30 23:59:59.9999999 | 2021-03-25 08:16:45.0433333 ``` 接下來新增一個`SqlRepository.cs`檔在程式碼這的專案內，作為主要跟資料庫溝通的 DbContext 類別通常都會採 DI 方式先注入到 .NET Core 應用程式裡，但因為不是今天的重點所以在這就不特別說明了，如下所示，透過 DI 可從建構子裡直接取得 DbContext 類別的實體物件 ```csharp using System.Data; using Microsoft.Data.SqlClient; using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.Logging; namespace Sample.Data { public class SqlRepository { private readonly DbContext _context; private readonly ILogger<SqlRepository> _logger; public SqlRepository(MyContext context, ILogger<SqlRepository> logger) { _context = context; _logger = logger; } public void Query() { // We will do something here... } } } ```
##**Case.1 取得單一筆資料的方式** 回到資料庫，每個產品的獲利計算公式為`(Price - ListPrice) * Quantity`，現在我想要知道 7 月以前所有產品的平均獲利，SQL 語法及結果如下 ```sql SELECT Avg((p.Price - p.ListPrice) * p.Quantity) as Profit FROM dbo.Product p WHERE '2021-07-01 00:00:00' > p.EndOn | Profit -------------- 1 | 10200.00 ``` 問題來了，假如我現在想要直接使用 EF Core 以純 SQL 的方式來取得跟上面一樣的計算結果，該怎麼做呢...? 老實說，這一開始還真的讓我撞牆蠻久的，後來才知道要使用 ExecuteSqlRaw 或 ExecuteSqlInterpolated 這兩個 EF Core 本身內建的方法來達成(EF Core 2.x 版本以下需用 ExecuteSqlCommand，此篇不討論)，下面範例皆在`SqlRepository.cs`的 Query() 方法內示範如何使用這兩個方法來取得算出來的獲利值 ```csharp // 1. 使用 ExecuteSqlRaw 方式 public void Query() { SqlParameter[] @params = { new SqlParameter("@Profit", SqlDbType.Decimal) // 計算結果型態為 Decimal { Precision = 18, // 整數可到 18 位 Scale = 2, // 到小數點第 2 位 Direction = ParameterDirection.Output // 變數類型為 Output } }; _context.Database.ExecuteSqlRaw(@"SET @Profit = (SELECT Avg((p.Price - p.ListPrice) * p.Quantity) as Profit FROM dbo.Product p WHERE '2021-07-01 00:00:00' > p.EndOn)", @params); // @params[0].Value 為計算結果 10200.00 _logger.LogInformation(@params[0].Value.ToString()); } // 2. 使用 ExecuteSqlInterpolated 方式 public void Query() { var profit = new SqlParameter { ParameterName = "@Profit", SqlDbType = SqlDbType.Decimal, Precision = 18, Scale = 2, Direction = ParameterDirection.Output }; _context.Database.ExecuteSqlInterpolated($@"SET {profit} = (SELECT Avg((p.Price - p.ListPrice) * p.Quantity) as Profit FROM dbo.Product p WHERE '2021-07-01 00:00:00' > p.EndOn)"); // profit.Value 為計算結果 10200.00 _logger.LogInformation(profit.Value.ToString()); } ``` 但光是只能單純取得輸出值是無法滿足求知慾旺盛的工程師們，有時候就會希望可以從應用端傳入變數來決定篩選的條件，進而取得特定的資料，例如要取得 6 月以前的平均獲利，這時候就要有個變數可以讓我們代入 SQL 字串裡(謎之音...聽說組字串的方式也可以 )，請看以下範例 ```csharp // 1. 使用 ExecuteSqlRaw 方式 public void Query() { SqlParameter[] @params = { new SqlParameter("@EndOn", SqlDbType.DateTime2) // 輸入型態為 DateTime2 { Value = "2021-06-01 00:00:00", Direction = ParameterDirection.Input // 變數類型為 Input }, new SqlParameter("@Profit", SqlDbType.Decimal) { Precision = 18, Scale = 2, Direction = ParameterDirection.Output } }; _context.Database.ExecuteSqlRaw(@"SET @Profit = (SELECT Avg((p.Price - p.ListPrice) * p.Quantity) as Profit FROM dbo.Product p WHERE @EndOn > p.EndOn)", @params); // @params[1].Value 為計算結果 2000.00 _logger.LogInformation(@params[1].Value.ToString()); } // 2. 使用 ExecuteSqlInterpolated 方式 public void Query() { var endOn = new SqlParameter { ParameterName = "@EndOn", SqlDbType = SqlDbType.DateTime2, Value = "2021-06-01 00:00:00", Direction = ParameterDirection.Input }; var profit = new SqlParameter { ParameterName = "@Profit", SqlDbType = SqlDbType.Decimal, Precision = 18, Scale = 2, Direction = ParameterDirection.Output }; _context.Database.ExecuteSqlInterpolated($@"SET {profit} = (SELECT Avg((p.Price - p.ListPrice) * p.Quantity) as Profit FROM dbo.Product p WHERE {endOn} > p.EndOn)"); // profit.Value 為計算結果 2000.00 _logger.LogInformation(profit.Value.ToString()); } ``` 補充：用 Dapper 方式取得 6 月前平均獲利 ```csharp using System.Data; using System.Linq; using Microsoft.Data.SqlClient; using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.Logging; using Dapper; namespace Sample.Data { public class SqlRepository { private readonly DbContext _context; private readonly ILogger<SqlRepository> _logger; public SqlRepository(MyContext context, ILogger<SqlRepository> logger) { _context = context; _logger = logger; } public void Query() { var sql = "SELECT Avg((p.Price - p.ListPrice) * p.Quantity) as Profit FROM dbo.Product p WHERE @endOn > p.EndOn"; using var conn = new SqlConnection(_context.Database.GetDbConnection().ConnectionString); var profit = conn.Query(sql, new { endOn = "2021-06-01 00:00:00" }); // profit.FirstOrDefault() 為計算結果 2000.00 _logger.LogInformation(profit.FirstOrDefault()?.ToString()); } } } ```
##**Case.2 取得資料型態為表格(Table)的方式** 現在我想要知道 5、6、7 各月份以前所有產品的平均獲利，使用的原始 SQL 語法及結果如下 ```sql SELECT 5 as [Month], Avg((p.Price - p.ListPrice) * p.Quantity) as Profit FROM dbo.Product p WHERE '2021-05-01 00:00:00' > p.EndOn UNION SELECT 6 as [Month], Avg((p.Price - p.ListPrice) * p.Quantity) as Profit FROM dbo.Product p WHERE '2021-06-01 00:00:00' > p.EndOn UNION SELECT 7 as [Month], Avg((p.Price - p.ListPrice) * p.Quantity) as Profit FROM dbo.Product p WHERE '2021-07-01 00:00:00' > p.EndOn Month | Profit ---------------- 5 | 2250.00 6 | 2000.00 7 | 10200.00 ``` 原本想說就使用 ExecuteSqlRaw 或 ExecuteSqlInterpolated 這兩個方法來直接取得查詢結果，登愣!!!馬上跳出了各種錯誤，嗯...事情果然沒這麼簡單啊...，主要關鍵點有兩個，如果變數型態是 Table 的話，似乎不能使用 SET 關鍵字來賦與回傳的變數值，需改用 INSERT，然後變數類型指定為 Output 時也無法順利將查詢結果取回，上網翻了一下資料，似乎只能採用傳統 ADO.NET 的方式來達成目的，如下 ```csharp public void Query() { var table = new System.Collections.Generic.List<(int, decimal)>(); using (var command = _context.Database.GetDbConnection().CreateCommand()) { command.CommandText = @"SELECT 5 as [Month], Avg((p.Price - p.ListPrice) * p.Quantity) as Profit FROM dbo.Product p WHERE @p1 > p.EndOn UNION SELECT 6 as [Month], Avg((p.Price - p.ListPrice) * p.Quantity) as Profit FROM dbo.Product p WHERE @p2 > p.EndOn UNION SELECT 7 as [Month], Avg((p.Price - p.ListPrice) * p.Quantity) as Profit FROM dbo.Product p WHERE @p3 > p.EndOn"; command.Parameters.Add(new SqlParameter { ParameterName = "@p1", Value = "2021-05-01 00:00:00" }); command.Parameters.Add(new SqlParameter { ParameterName = "@p2", Value = "2021-06-01 00:00:00" }); command.Parameters.Add(new SqlParameter { ParameterName = "@p3", Value = "2021-07-01 00:00:00" }); _context.Database.OpenConnection(); using var result = command.ExecuteReader(); while (result.Read()) { table.Add((result.GetInt32("Month"), result.GetDecimal("Profit"))); } } foreach (var (month, profit) in table) { _logger.LogInformation($"Month: {month}, Profit: {profit}"); } } // Output Month: 5, Profit: 2250.00 Month: 6, Profit: 2000.00 Month: 7, Profit: 10200.00 ``` 補充：用 Dapper 方式取得 5、6、7 各月以前的平均獲利 ```csharp public void Query() { var sql = @"SELECT 5 as [Month], Avg((p.Price - p.ListPrice) * p.Quantity) as Profit FROM dbo.Product p WHERE @p1 > p.EndOn UNION SELECT 6 as [Month], Avg((p.Price - p.ListPrice) * p.Quantity) as Profit FROM dbo.Product p WHERE @p2 > p.EndOn UNION SELECT 7 as [Month], Avg((p.Price - p.ListPrice) * p.Quantity) as Profit FROM dbo.Product p WHERE @p3 > p.EndOn"; using var conn = new SqlConnection(_context.Database.GetDbConnection().ConnectionString); var result = conn.Query<(int, decimal?)>(sql, new { @p1 = "2021-05-01 00:00:00", @p2 = "2021-06-01 00:00:00", @p3 = "2021-07-01 00:00:00" }); foreach (var (month, profit) in result) _logger.LogInformation($"Month: {month}, Profit: {profit}"); } // Output Month: 5, Profit: 2250.00 Month: 6, Profit: 2000.00 Month: 7, Profit: 10200.00 ```
##**Case.3 呼叫 Stored Procedure 並取得執行後的結果** 上面兩種案例其實直接用 EF Core 內建的計算函式也能辦到就是了，但如果是要呼叫資料庫裡的 Stored Procedure(以下簡稱 SP)，似乎就真的只能靠下純 SQL 的方式來取得 SP 的執行結果，首先在資料庫新增一個 SP，名稱為`sp_ProductProfit`，如下 ```sql CREATE PROC [sp_ProductProfit] @EndOn [DATETIME2](7), @ProfitValue DECIMAL(18,2) OUTPUT AS BEGIN SET NOCOUNT ON SELECT @ProfitValue = (SELECT Avg((p.Price - p.ListPrice) * p.Quantity) AS Profit FROM dbo.Product p WHERE @EndOn > p.EndOn) RETURN END ``` 這邊可以看到剛建立的 SP 需一個輸入參數 @EndOn 及一個輸出變數 @ProfitValue，接著測試一下該 SP 能否正常執行，這邊以取得 6 月前的產品獲利值為例 ```sql // 執行以下命令應可得到 2000.00 DECLARE @EndDateTime [DATETIME2](7), @Profit DECIMAL(18,2) SET @EndDateTime = '2021-06-01 00:00:00' EXEC [sp_ProductProfit] @EndOn = @EndDateTime , @ProfitValue = @Profit OUTPUT PRINT @Profit ``` 該輸出值 2000 即為 SP 的計算結果，現在我需要在應用端透過該 SP 取得跟上面測試一樣的結果，該怎麼做呢? ```csharp // 1. 使用 ExecuteSqlRaw 方式 public void Query() { SqlParameter[] @params = { new SqlParameter("@p1", SqlDbType.DateTime2) // 輸入型態為 DateTime2 { Value = "2021-06-01 00:00:00", Direction = ParameterDirection.Input // 變數類型為 Input }, new SqlParameter("@p2", SqlDbType.Decimal) { Precision = 18, Scale = 2, Direction = ParameterDirection.Output } }; _context.Database.ExecuteSqlRaw(@"EXEC [sp_ProductProfit] @EndOn = @p1, @ProfitValue = @p2 OUTPUT", @params); // @params[1].Value 為計算結果 2000.00 _logger.LogInformation(@params[1].Value.ToString()); } // 2. 使用 ExecuteSqlInterpolated 方式 public void Query() { var endOn = new SqlParameter { ParameterName = "@p1", SqlDbType = SqlDbType.DateTime2, Value = "2021-06-01 00:00:00", Direction = ParameterDirection.Input }; var profit = new SqlParameter { ParameterName = "@p2", SqlDbType = SqlDbType.Decimal, Precision = 18, Scale = 2, Direction = ParameterDirection.Output }; _context.Database.ExecuteSqlInterpolated($@"EXEC [sp_ProductProfit] @EndOn = {endOn}, @ProfitValue = {profit} OUTPUT"); // profit.Value 為計算結果 2000.00 _logger.LogInformation(profit.Value.ToString()); } ``` 可以看到其實不難，透過 ExecuteSqlRaw 或 ExecuteSqlInterpolated 就可以完成我們要的結果，那假設我們進一步想要取得 SP 的回傳值，又該怎麼做呢? ```csharp // 1. 使用 ExecuteSqlRaw 方式 public void Query() { SqlParameter[] @params = { new SqlParameter("@p1", SqlDbType.DateTime2 { Value = "2021-06-01 00:00:00", Direction = ParameterDirection.Input }, new SqlParameter("@p2", SqlDbType.Decimal) { Precision = 18, Scale = 2, Direction = ParameterDirection.Output }, new SqlParameter("@p3", SqlDbType.Int) { Direction = ParameterDirection.Output // 注意! 是 Output 不是 ReturnValue } }; _context.Database.ExecuteSqlRaw(@"EXEC @p3 = [sp_ProductProfit] @EndOn = @p1, @ProfitValue = @p2 OUTPUT", @params); // @params[2].Value 為 0 _logger.LogInformation(@params[2].Value.ToString()); } // 2. 使用 ExecuteSqlInterpolated 方式 public void Query() { var endOn = new SqlParameter { ParameterName = "@p1", SqlDbType = SqlDbType.DateTime2, Value = "2021-06-01 00:00:00", Direction = ParameterDirection.Input }; var profit = new SqlParameter { ParameterName = "@p2", SqlDbType = SqlDbType.Decimal, Precision = 18, Scale = 2, Direction = ParameterDirection.Output }; var returnValue = new SqlParameter { ParameterName = "@p3", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output // 注意! 也是 Output 不是 ReturnValue }; _context.Database.ExecuteSqlInterpolated($@"EXEC {returnValue} = [sp_ProductProfit] @EndOn = {endOn}, @ProfitValue = {profit} OUTPUT"); // returnValue.Value 為 0 _logger.LogInformation(returnValue.Value.ToString()); } // 3. 使用 Dapper 方式 public void Query() { using var conn = new SqlConnection(_context.Database.GetDbConnection().ConnectionString); var parameters = new DynamicParameters(); parameters.Add("EndOn", "2021-06-01 00:00:00"); parameters.Add("ProfitValue", dbType: DbType.Decimal, precision: 18, scale: 2, direction: ParameterDirection.Output); parameters.Add("ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue); var result = conn.Execute("sp_ProductProfit", parameters, commandType: CommandType.StoredProcedure); _logger.LogInformation($"Profit Value: {parameters.Get("ProfitValue")}, Return Value: {parameters.Get("ReturnValue")}"); } // Output Profit Value: 2000.00, Return Value: 0 ``` 唯一要注意的是，除 Dapper 外，其它取得回傳值變數的 Direction 需設定為 Output 而不是 ReturnValue，我在這裡卡了一下...再來 SP 執行成功的回傳值預設為 0 且型態只能整數，這部分就看實際業務需求做調整即可；另外 SP 的 Output 變數不支援資料型態為 Table，如果需要取得該資料型態的回傳值，可考慮上面 [Case.2](https://linmasaki09.blogspot.com/2021/03/entity-framework-core-sql.html#spanstylecolor7c09e0case2tablespan) 的做法或是使用資料表函數(Table-Valued Function)來達成，這部分就等以後我有用到再來補充吧......(遠望


##參考資料 [\[Learn Entity Framework Core\] Executing Raw SQL Queries](https://www.learnentityframeworkcore.com/raw-sql) [\[ErikEJ's blog\] Get a SQL Server stored procedure return value with EF Core](https://erikej.github.io/efcore/2020/11/02/ef-core-sproc-return-value.html) [\[Stack Overflow\] How to utilize output parameters in EF Core 3.0 using ExecuteSqlInterpolatedAsync](https://stackoverflow.com/questions/60382040/how-to-utilize-output-parameters-in-ef-core-3-0-using-executesqlinterpolatedasyn)