[Shopify] 新版顧客帳號頁面網域設定

開一個新商城時注意到，在結帳設定有下面訊息提醒

點擊 [變更網域] 過去後被導向 [客戶帳號] => [新版客戶帳號]

點擊 [變更網域] 按鈕就會進入顧客帳號網域設定，預設是 account，可自行修改

決定好子網域後，按下 [繼續] 按鈕，會出現 Shopify DNS CName 設定，看網域是託管在哪，把該 CName 設定進去就行，DNS 設定生效後按下 [驗證] 按鈕，就等兩邊驗證完成

在 Hinet 上輸入 CName 後大概 2 hr 後設定生效，Shopify 驗證大約是 1 hr 完成

驗證完成後就可以在 [網域] 內看到顧客帳號網域設定

最後也是最神奇的地方在於根本就沒有使用新版客戶帳號功能，但是會提醒要進行網域設定

• 參考資料
• 為新版顧客帳號頁面將第三方網域連結至 Shopify

[SSRS] 首頁頂端空白

在 SSRS 報表上常常會發生第一頁控件上方會出現空白，但第二頁又沒有的情況，專屬於第一頁的空白，後來是設定頁首來控制該情況

Tablix 控件離報表上方有段距離

實際執行，第一頁離報表上方有段空白，但第二頁卻又貼合報表上方

把頁首加入並把把背景設定為灰色，Tablix 則是完全貼合頁首下緣

實際執行第一頁空白就不會出現且第一、第二頁就會一致

目前都是是把頁首當成 SSRS 報表一定要存在設計，也一併處理該情況

• 參考資料
• 論壇討論 1、2

[SSMS] 索引標籤文字

發現 SSMS 索引標籤文字是可以設定，下圖為預設顯示，包含

• Login 名稱
• Server 名稱
• Database 名稱
• File 名稱

選項 => 文字編輯器 => 編輯器索引標籤和狀態列 => 索引標籤文字內可以進行更改

下圖為保留 Database 名稱和 File 名稱效果

[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

[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