[EFCore] FromSql、FromSqlRaw

根據 SQL Queries 文章內容，來記錄 FromSql 和 FromSqlRaw，使用  EFCore 8、AdventureWorks2022 Production.Product Table 為資料來源並開啟 Sql Profile  來觀察產生的 TSQL 語法

FromSql

•  EF Core 7.0 新功能，之前版本請改用 FromSqlInterpolated。
•  只能直接對 DbSet 使用。
• 使用 字串插補 (String interpolation) 來傳遞參數

using EFCoreQuery.Models;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Data;

namespace EFCoreQuery
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            using var dbContext = new AdventureWorks2022Context();
            
            // CASE 1：常數
            var result1 = await dbContext.Product.FromSql(
                $"SELECT * FROM Production.Product WHERE Class = N'H'").ToListAsync();
            Console.WriteLine(result1.Count().ToString());
            // TSQL：SELECT * FROM Production.Product WHERE Class = N'H'

            // CASE2：C# 字串插補傳遞變數，TSQL 會參數化，資料型態、長度為　nvarchar(4000)
            string data = "H";
            var result2 = await dbContext.Product.FromSql(
                $"SELECT * FROM Production.Product WHERE Class = {data}").ToListAsync();
            Console.WriteLine(result2.Count().ToString());
            // TSQL：exec sp_executesql 
            //     N'SELECT * FROM Production.Product WHERE Class = @p0' ,
            //     N'@p0 nvarchar(4000)',@p0=N'H'

            // CASE3：C# 字串插補傳遞 SqlParameter 並對應欄位資料型態、長度 nchar(2)，TSQL 會參數化
            var para = new SqlParameter("@Class", SqlDbType.NChar, 2);
            para.Value = "H";
            var result3 = await dbContext.Product.FromSql(
                $"SELECT * FROM Production.Product WHERE Class = {para}").ToListAsync();
            Console.WriteLine(result3.Count().ToString());
            // TSQL：exec sp_executesql 
            //     N'SELECT * FROM Production.Product WHERE Class = @Class',
            //     N'@Class nchar(2)',@Class=N'H '            

        }
    }
}

CASE2 雖然是字串插補傳遞變數，但會將變數包裝於 SqlParameter 內，產生的 TSQL 會參數化，不會是組合字串傳進去，可以避免 SqlInjection

FromSqlRaw

可以用它動態改變欄位名稱，但有 Sqlnjection 風險，不適合應用在實務上

•  只能直接對 DbSet 使用。
• 使用 字串插補 (String interpolation) 來傳遞欄位名稱

using EFCoreQuery.Models;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Data;

namespace EFCoreQuery
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            using var dbContext = new AdventureWorks2022Context();

            // CASE1：C# 字串插補傳遞欄位名稱、SqlParameter 參數值
            string columnName = "Class";

            var para = new SqlParameter("@Class", SqlDbType.NChar, 2);
            para.Value = "H";

            var result4 = await dbContext.Product.FromSqlRaw(
                $"SELECT * FROM Production.Product WHERE {columnName} = @Class", para).ToListAsync();
            Console.WriteLine(result4.Count().ToString());
            // TSQL：exec sp_executesql
            //     N'SELECT * FROM Production.Product WHERE Class = @Class',
            //     N'@Class nchar(2)',@Class=N'H ' 

            // CASE2：C# 字串插補傳遞變數只能應用在欄位
            string data = "H";
            var result5 = await dbContext.Product.FromSqlRaw(
                $"SELECT * FROM Production.Product WHERE Class = {data}").ToListAsync();
            Console.WriteLine(result5.Count().ToString());
            // Exception：Unhandled exception. 
            // Microsoft.Data.SqlClient.SqlException (0x80131904):
            // 無效的資料行名稱 'H'
        }
    }
}

FromSql 和 FromSqlRaw 限制

官方文件上說明

The SQL query must return data for all properties of the entity type.

The column names in the result set must match the column names that properties are mapped to. Note that this behavior is different from EF6; EF6 ignored property-to-column mapping for SQL queries, and result set column names had to match those property names.

The SQL query can't contain related data. However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data).

個人理解是回傳的 Entity 每個 Property 都要對應到，只要有一個 Property 沒有對應到就會拋出  InvalidOperationException 且抓取關聯 Entity 時一定得透過 Include

namespace EFCoreQuery
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            using var dbContext = new AdventureWorks2022Context();

            // CASE：回傳的 Entity 每個 Property 都要對應到，故意只抓 ProductID 而已
            var result = await dbContext.Product.FromSql(
                $"SELECT ProductID FROM Production.Product WHERE Class = N'H'").ToListAsync();
            Console.WriteLine(result.Count().ToString());
            // Unhandled exception. System.InvalidOperationException:
            // The required column 'Class' was not present in the results of a 'FromSql' operation.
        }
    }
}

函式演變

整理如下

• EFCore 2：只有 FromSql
• EFCore 3：FromSql 被移除，取而代之的是 FromSqlRaw 和 FromSqlInterpolated。
• EFCore 7： FromSql 強勢回歸，用來取代 FromSqlInterpolated，但 FromSqlRaw 仍然存在

EFCore 3 BreakChange 內關於 FromSql 被取代的相關章節

• FromSql, ExecuteSql, and ExecuteSqlAsync have been renamed
• FromSql method when used with stored procedure cannot be composed

EFCore 7 FromSql 回歸取代 FromSqlInterpolated 則是在 SQL Queries 內的備註上看到，EFCore 7 What's New 和 BreakChange 內反而沒有提到

• 參考資料
• Executing Raw SQL Queries