[EFCore] SqlQuery、SqlQueryRaw

延續 [EFCore] FromSql、FromSqlRaw 筆記，根據 SQL Queries 和 Raw SQL queries for unmapped types 文章內容，來記錄 SqlQuery 和 SqlQueryRaw，使用 EFCore 8、AdventureWorks2022 Purchasing 相關 Table 為資料來源並開啟 Sql Profile 來觀察產生的 TSQL 語法。

SqlQuery 和 SqlQueryRaw

• 兩者都是在 EFore 7.0 新功能
• EFCore 7.0：只能回傳 Scalar Value，EX：string、datetime、int
• EFCore 8.0：能回傳 unmapped types，EX：DTO、ViewModel

PurchaseOrderDetailViewModel

用來承接 SqlQuery、SqlQueryRaw 結果

namespace EFCoreQuery.Models
{
    public class PurchaseOrderDetailViewModel
    {
        public int ProductID { get; set; }

        /// <summary>
        /// 把 Name 轉為 ProductName
        /// </summary>
        public string ProductName { get; set; }

        public Int16 OrderQty { get; set; }

        public decimal UnitPrice { get; set; }

        public decimal LineTotal { get; set; }
    }
}

EFCore 7.0 實際測試

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

            // CASE 1：使用 EFCore 7.0，只能回傳 Scalar Value
            var productID = new SqlParameter("@ProductID", SqlDbType.Int);
            productID.Value = 403;

            var result6 = await dbContext.Database.SqlQuery<int>(
                $"SELECT ProductID FROM Production.[Product] WHERE ProductID = {productID}")
                .ToListAsync();
            Console.WriteLine(result6.Count().ToString());
            // TSQL：exec sp_executesql
            //     N'SELECT ProductID FROM Production.[Product] WHERE ProductID = @ProductID',
            //     N'@ProductID int',@ProductID=403

            // CASE 2：EFCore 7.0 不支援回傳 unmapped types
            var purchaseOrderID = new SqlParameter("@PurchaseOrderID", SqlDbType.SmallInt);
            purchaseOrderID.Value = 8;

            string TSQL7 = @"
                SELECT 
                    P.ProductID ,
                    P.[Name] AS ProductName , 
                    POD.OrderQty ,
                    POD.UnitPrice ,
                    POD.LineTotal 
                FROM [Purchasing].[PurchaseOrderDetail] AS POD
                    JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID
                WHERE POD.PurchaseOrderID = @PurchaseOrderID
                ";

            var result7 = await dbContext.Database.SqlQueryRaw<PurchaseOrderDetailViewModel>(TSQL7, purchaseOrderID).ToListAsync();
            Console.WriteLine(result7.Count().ToString());
            // EFCore 7.0 會拋出下列錯誤，EFCore 8.0 才能正常執行
            // Unhandled exception. System.InvalidOperationException:
            // The element type 'EFCoreQuery.Models.PurchaseOrderDetailViewModel' used in 'SqlQuery' method is not natively supported by your database provider.
            // Either use a supported element type, or use ModelConfigurationBuilder.DefaultTypeMapping to define a mapping for your type.
        }
    }
}

EFCore 8.0 實際測試

TSQL 和 LINQ WHERE 條件混搭方式在 EFCore 7.0 就可以，該筆記寫在 EFCore 8.0 內紀錄而已，SqlQuery 和 SqlQueryRaw 回傳 IQueryable，所以後續才能再使用 LINQ WHERE 來篩選資料

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

            // CASE 1：：EFCore 8.0 支援 unmapped types
            var orderDate = new SqlParameter("@OrderDate", SqlDbType.DateTime);
            orderDate.Value = new DateTime(2014, 1, 1);

            FormattableString TSQL8 = $@"
                SELECT 
                    P.ProductID ,
                    P.[Name] AS ProductName , 
                    POD.OrderQty ,
                    POD.UnitPrice ,
                    POD.LineTotal 
                FROM [Purchasing].[PurchaseOrderHeader] AS POH
                    JOIN [Purchasing].[PurchaseOrderDetail] AS POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
                    JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID
                WHERE POH.OrderDate >= {orderDate}
                ";

            var result8 = await dbContext.Database
                .SqlQuery<PurchaseOrderDetailViewModel>(TSQL8)
                .Where(vw => vw.UnitPrice > 80)
                .ToListAsync();

            Console.WriteLine(result8.Count().ToString());
            // TSQL：exec sp_executesql
            //     N'SELECT [e].[LineTotal], [e].[OrderQty], [e].[ProductID], [e].[ProductName], [e].[UnitPrice]
            //       FROM(
            //              SELECT
            //                  P.ProductID,
            //                  P.[Name] AS ProductName,
            //                  POD.OrderQty,
            //                  POD.UnitPrice,
            //                  POD.LineTotal
            //              FROM[Purchasing].[PurchaseOrderHeader] AS POH
            //                  JOIN[Purchasing].[PurchaseOrderDetail] AS POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
            //                  JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID
            //              WHERE POH.OrderDate >= @OrderDate
            //           ) AS[e]
            //       WHERE[e].[UnitPrice] > 80.0',
            //     N'@OrderDate datetime',@OrderDate='2014 - 01 - 01 00:00:00'

            // CASE 2：模擬條件串接情況
            int? productID = 1;
            short? orderQty = 4;

            string TSQL9 = @"
                SELECT 
                    P.ProductID ,
                    P.[Name] AS ProductName , 
                    POD.OrderQty ,
                    POD.UnitPrice ,
                    POD.LineTotal 
                FROM [Purchasing].[PurchaseOrderHeader] AS POH
                    JOIN [Purchasing].[PurchaseOrderDetail] AS POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
                    JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID
                ";

            var result = dbContext.Database
                .SqlQueryRaw<PurchaseOrderDetailViewModel>(TSQL9);

            if (productID.HasValue)
                result = result.Where(vw => vw.ProductID == productID.Value);

            if (orderQty.HasValue)
                result = result.Where(vw => vw.OrderQty >= orderQty.Value);

            Console.WriteLine((await result.ToListAsync()).Count().ToString());

            // TSQL：exec sp_executesql 
            //     N'SELECT [e].[LineTotal], [e].[OrderQty], [e].[ProductID], [e].[ProductName], [e].[UnitPrice]
            //       FROM(
            //              SELECT
            //                  P.ProductID,
            //                  P.[Name] AS ProductName,
            //                  POD.OrderQty,
            //                  POD.UnitPrice,
            //                  POD.LineTotal
            //              FROM[Purchasing].[PurchaseOrderHeader] AS POH
            //                  JOIN[Purchasing].[PurchaseOrderDetail] AS POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
            //                  JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID
            //           ) AS[e]
            //       WHERE[e].[ProductID] = @__productID_Value_1
            //              AND[e].[OrderQty] >= @__orderQty_Value_2',
            //       N'@__productID_Value_1 int, @__orderQty_Value_2 smallint',@__productID_Value_1=1,@__orderQty_Value_2=4

        }
    }
}

從上述兩段語法就可以發現 LINQ WHERE 語法產生條件，會在主要的 TSQL 語法外再包一層

• 參考資料
• Raw SQL Query without DbSet - Entity Framework Core