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

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

準備測試資料

先在資料庫建立一個產品資料表

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)	建立時間

然後建立以下初始資料等等測試要用

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 筆資料

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 類別的實體物件

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 語法及結果如下

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() 方法內示範如何使用這兩個方法來取得算出來的獲利值

// 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 字串裡(謎之音…聽說組字串的方式也可以 )，請看以下範例

// 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 月前平均獲利

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<decimal?>(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 語法及結果如下

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 的方式來達成目的，如下

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 各月以前的平均獲利

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，如下

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 月前的產品獲利值為例

// 執行以下命令應可得到 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 取得跟上面測試一樣的結果，該怎麼做呢?

// 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 的回傳值，又該怎麼做呢?

// 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<decimal>("ProfitValue")}, Return Value: {parameters.Get<int>("ReturnValue")}");
}

// Output
Profit Value: 2000.00, Return Value: 0

唯一要注意的是，除 Dapper 外，其它取得回傳值變數的 Direction 需設定為 Output 而不是 ReturnValue，我在這裡卡了一下…再來 SP 執行成功的回傳值預設為 0 且型態只能整數，這部分就看實際業務需求做調整即可；另外 SP 的 Output 變數不支援資料型態為 Table，如果需要取得該資料型態的回傳值，可考慮上面 Case.2 的做法或是使用資料表函數(Table-Valued Function)來達成，這部分就等以後我有用到再來補充吧……(遠望

參考資料

[Learn Entity Framework Core] Executing Raw SQL Queries

[ErikEJ's blog] Get a SQL Server stored procedure return value with EF Core

[Stack Overflow] How to utilize output parameters in EF Core 3.0 using ExecuteSqlInterpolatedAsync