[EFCore] SqlQuery、SqlQueryRaw - 欄位對應

延續 [EFCore] SqlQuery、SqlQueryRaw 筆記，在 Raw SQL queries for unmapped types 內還有提到 Data Annotations - ColumnAttribute 應用，之前把 Product Name 欄位在 PurchaseOrderDetailViewModel class 內轉為 ProductName，造成寫 TSQL 時，一定會需要 P.[Name]  AS ProductName 這段轉換，要不然會拋出錯誤。

TSQL Name 欄位未轉換為 ProductName

CASE 1：沒有任何 where 條件

string TSQL10 = @"
    SELECT 
        P.ProductID ,
        P.[Name] ,
        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 result10 = dbContext.Database
    .SqlQueryRaw<PurchaseOrderDetailViewModel>(TSQL10);

var result = await result10.ToListAsync();
Console.WriteLine(result.Count().ToString());
// TSQL 語法正常輸出
//    SELECT
//        P.ProductID ,
//        P.[Name] , 
//        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

執行後會拋出該錯誤，原來 SqlQueryRaw 底層是 FromSql 去執行的，所以每個 Property 都要有值才行，在 TSQL pass 但卡在 EFCore 上。

Unhandled exception. System.InvalidOperationException: The required column 'ProductName' was not present in the results of a 'FromSql' operation.

CASE2：TSQL WHERE 沒有條件，但 LINQ WHERE 內有

string TSQL11 = @"
    SELECT 
        P.ProductID ,
        P.[Name] , 
        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 result11 = dbContext.Database
    .SqlQueryRaw<PurchaseOrderDetailViewModel>(TSQL11)
    .Where(vw => vw.ProductID == productID);

var result = await result11.ToListAsync();
Console.WriteLine(result.Count().ToString());

產生 TSQL

exec sp_executesql N'
	SELECT 
	    [e].[LineTotal], 
	    [e].[OrderQty], 
	    [e].[ProductID], 
	    [e].[ProductName], -- 外層為 ProductName
	    [e].[UnitPrice]
	FROM
		(
			SELECT
				P.ProductID,
				P.[Name], -- 內層為 Name
				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_1',
	N'@__productID_1 int',@__productID_1=1

執行後會拋出該錯誤，從 TSQL 語法可以觀察到，LINQ WHERE 條件，會根據 PurchaseOrderDetailViewModel Property 轉成對應 TSQL 欄位，因為 TSQL 內層沒有把 Name 轉成 ProductName，導致外層抓不到 ProductName。

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): 無效的資料行名稱 'ProductName'。

Data Annotations - ColumnAttribute

在 PurchaseOrderDetailViewModel 上把 ProductName 加上 ColumnAttribute

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

        /// <summary>
        /// 把 Name 轉為 ProductName
        /// </summary>
        [Column("Name")]
        public string ProductName { get; set; }

        public Int16 OrderQty { get; set; }

        public decimal UnitPrice { get; set; }

        public decimal LineTotal { get; set; }
    }
}

CASE 1：沒有任何 where 條件

string TSQL13 = @"
    SELECT 
        P.ProductID ,
        P.[Name] ,
        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 result13 = dbContext.Database
    .SqlQueryRaw<PurchaseOrderDetailViewModel>(TSQL13);

var result = await result13.ToListAsync();
Console.WriteLine(result.Count().ToString());
// TSQL 語法正常輸出
//    SELECT
//        P.ProductID ,
//        P.[Name] , 
//        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

正常執行，沒有拋出 FromSql ProductName 沒有值的錯誤，從中斷點去觀察，也發現值有塞進去，TSQL Name 欄位和 PurchaseOrderDetailViewModel ProudctName 屬性會自動對應。

CASE2：TSQL WHERE 沒有條件，但 LINQ WHERE 內有

int productID = 1;

string TSQL14 = @"
    SELECT 
        P.ProductID ,
        P.[Name] , 
        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 result14 = dbContext.Database
    .SqlQueryRaw<PurchaseOrderDetailViewModel>(TSQL14)
    .Where(vw => vw.ProductID == productID);

var result = await result14.ToListAsync();
Console.WriteLine(result.Count().ToString());

產生 TSQL

exec sp_executesql N'
	SELECT 
	    [e].[LineTotal], 
	    [e].[OrderQty], 
	    [e].[ProductID], 
	    [e].[Name], -- 外層為 Name
	    [e].[UnitPrice]
	FROM
		(
			SELECT
				P.ProductID,
				P.[Name], -- 內層為 Name
				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_1',
	N'@__productID_1 int',@__productID_1=1

外層 TSQL 產生 Name 而不是 ProductName

在 EF 學習 Data Annotations 時，是在 Code First FluentAPI 內設定給 Migration 使用，沒想到在 EFCore DbFirst 已經可以影響 TSQL 產生。

• 參考資料
• 實體屬性
• Data Annotations - Column Attribute in EF 6 & EF Core