[EF] 物件型別與已知的管理提供者原始型別之間並無對應

使用 Entity Frameworks 6.4 edmx 時突然出現該錯誤訊息

System.ArgumentException: 物件型別 System.Collections.Generic.List`1[[System.Data.SqlClient.SqlParameter, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] 與已知的管理提供者原始型別之間並無對應。

發現是使用 SqlQuery 時，變數參數沒有轉成為 Array 造成，直接把 List 丟進去

List<SqlParameter> parameterList = new List<SqlParameter>();
parameterList.Add(new SqlParameter());
var result = dbContext.Database.SqlQuery<Entity>(TSQL, parameterList);

把 List 轉為 Array 就 pass 該問題

var result = dbContext.Database.SqlQuery<Entity>(TSQL, parameterList.ToArray());

• 參考資料
• StackOverFlow 討論

[SSMS] 效能觀察

以往要觀察 TSQL 執行的 Logical Read 和 CPU Time，都會直接在 Query 開頭直接打 SET STATISTICS IO , TIME ON 來啟用，在社群上看到大神分享才發現，原來 SSMS 內有查詢選項可以直接勾選啟用

開啟 Query 後才會出現 [查詢] Tag，點選進入 [查詢選項]

執行 => 進階內就可以看見 SET STATISTICS IO 和 SET STATISTICS TIME 選項

[C#] 環境變數

要透過 C# 存取 Windows 環境變數，要了解下述函式

• EnvironmentVariableTarget Enum
• Environment.GetEnvironmentVariables
• Environment.SetEnvironmentVariable

EnvironmentVariableTarget

環境變數範圍

• Process：目前 Process
• User：儲存在 HKEY_CURRENT_USER\Environment 
• Machine：儲存在 HKEY_CURRENT_USER\Environment

把環境變數存放在 Process 的話，該 Process 結束後環境變數也就消失囉

Environment.SetEnvironmentVariable

新增或修改

• 環境變數不存在：新增
• 環境變數存在：修改

刪除，值設定為下列三者都算是要刪除該環境變數

• null (該範例使用)
• String.Empty
• U+0000

刪除時假如該環境變數不存在，不會拋錯誤

簡易範例

WinForms 簡易範例來對環境變數進行 CRUD

using System.Collections;

namespace EnvVarSample
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            dgvSearch.AutoGenerateColumns = false;
            cboSearch.DataSource = Enum.GetNames(typeof(EnvironmentVariableTarget));
            cboEnvironmentVariableTarget.DataSource = Enum.GetNames(typeof(EnvironmentVariableTarget));
        }

        private void cboSearch_SelectionChangeCommitted(object sender, EventArgs e)
        {
            var (_, _, target) = GetValue();

            var source = new List<Source>();
            IDictionary vars = Environment.GetEnvironmentVariables(target);
            foreach (DictionaryEntry entry in vars)
                source.Add(new Source() { EnvVar = entry.Key.ToString(), Value = entry.Value.ToString() });

            dgvSearch.DataSource = source;
        }

        private void btnAddOrModify_Click(object sender, EventArgs e)
        {
            var input = GetValue();
            Environment.SetEnvironmentVariable(input.EnvVar, input.Value, input.Target);
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            var (EvnVar, _, target) = GetValue();
            Environment.SetEnvironmentVariable(EvnVar, null, target);
        }

        private (string EnvVar, string Value, EnvironmentVariableTarget Target) GetValue()
        {
            string item = cboEnvironmentVariableTarget.SelectedItem.ToString();
            if (Enum.TryParse(item, out EnvironmentVariableTarget target) == false)
                throw new NotSupportedException(nameof(item));

            return (
                txtEnvVar.Text.Trim(),
                txtValue.Text.Trim(),
                target);
        }

        private void dgvSearch_SelectionChanged(object sender, EventArgs e)
        {
            if (dgvSearch.CurrentRow == null)
                return;

            cboEnvironmentVariableTarget.SelectedIndex = cboEnvironmentVariableTarget.FindStringExact(cboSearch.SelectedItem.ToString());
            txtEnvVar.Text = dgvSearch.CurrentRow.Cells[ColEnvVar.Index].Value?.ToString();
            txtValue.Text = dgvSearch.CurrentRow.Cells[ColValue.Index].Value?.ToString();
        }

        private class Source
        {
            public string EnvVar { get; set; }

            public string Value { get; set; }
        }

    }
}

GetEnvironmentVariables() 和 SetEnvironmentVariable() 沒有指定 EnvironmentVariableTarget 的多載，其 EnvironmentVariableTarget 為 Process 喔

Windows OS GUI 操作

在 Windows OS 的 [系統內容] =>  進階 => 環境變數內，可以直接編修 User 和 Machine 的環境變數

• 延伸閱讀
• [C#] ComboBox 預設值
• 參考資料
• StackOverFlow 討論
• EnvironmentVariablesExtensions.AddEnvironmentVariables Method
• Discard

[EFCore] Scaffold-DbContext

在 [EFCore] 在 Console 專案上安裝設定 內使用 Scaffold-DbContext 產生 DbContext 和 Entity 後，使用時發現有些細節想像中有落差，在官方文章 - Entity Framework Core tools reference - Package Manager Console in Visual Studio 內找到 Scaffold-DbContext 參數說明

必要參數

-Connection 和 -Provider 為必要參數，網路上寫法大多是省略參數標示，寫法如下

-- 完整寫法
Scaffold-DbContext 
    -Connection "Server=.;Database=AsventureWorks2022;Trusted_Connection=True;TrustServerCertificate=true" 
    -Provider Microsoft.EntityFrameworkCore.SqlServer
  
-- 常見寫法
Scaffold-DbContext
    "Server=.;Database=AsventureWorks2022;Trusted_Connection=True;TrustServerCertificate=true"
    Microsoft.EntityFrameworkCore.SqlServer

-Tables

指定要建立 Entity 的 Table，重覆使用該參數，必須每次都明確指定 Table，原因在於 DbContext 會被重建，第一次執行 Scaffold-DbContext 建立的 Entity，在第二次執行 Scaffold-DbContext 後，假如沒有明確指定，Entity 雖然存在，但 DbContext 內是沒有的。

指定多個 Table 的話，要以逗號 (,) 區分，EX：-Tables Purchasing.PurchaseOrderHeader,Purchasing.PurchaseOrderDetail,Purchasing.Vendor,Production.Product

-NoOnConfiguring 

預設會在 DbContext.OnConfiguring 內產生下列 Code，連線字串會 HardCode 寫在裡面

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see https://go.microsoft.com/fwlink/?LinkId=723263.
        => optionsBuilder.UseSqlServer("連線字串");

實務上慣用語法

Scaffold-DbContext 
	"Server=.;Database=AdventureWorks2022;Trusted_Connection=True;TrustServerCertificate=true" 
	Microsoft.EntityFrameworkCore.SqlServer 
	-ContextDir DbContextDir            // 指定 DbContext 資料夾位置    
	-Context AdventureWork2022DbContext // 指定 DbContext 名稱
	-OutputDir EntityDir                // 指定 Entity 資料夾位置，資料夾不存在會自動建立
	-Tables Table1,Table2               // 指定特定 Table，不指定即建立整個 DB 內 Table
	-NoPluralize                        // 不要複數化
	-UseDatabaseNames                   // Entity 名稱和 DB 內一樣
	-Force                              // 覆寫現有檔案

以上語法為方便閱讀，都會換行排列，實際在執行是串接在一起的

[SQL] 彙總函數應用 - 資料分組

要把 txt 檔案匯入系統內做資料檢核，txt 檔案內資料如下圖示意，希望能透過 TSQL 把員工和眷屬群組在一起

使用視窗彙總函數跑 Running Total 來達到該需求

DECLARE @Temp Table (RowNO char(7) , FullName nchar(8) , FName nchar(8))
INSERT INTO @Temp (RowNO , FullName , FName) VALUES('0000001' , N'員工一' , N'')
INSERT INTO @Temp (RowNO , FullName , FName) VALUES('0000002' , N'' , N'眷屬一_1')
INSERT INTO @Temp (RowNO , FullName , FName) VALUES('0000003' , N'' , N'眷屬一_2')
INSERT INTO @Temp (RowNO , FullName , FName) VALUES('0000004' , N'員工二' , N'')
INSERT INTO @Temp (RowNO , FullName , FName) VALUES('0000005' , N'員工三' , N'')
INSERT INTO @Temp (RowNO , FullName , FName) VALUES('0000006' , N'' , N'眷屬三_1')

SELECT * , 
	SUM(IIF([FullName] = '' , 0 , 1)) 
	OVER 
		(
			ORDER BY RowNO
			ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
		) AS GroupNO
FROM @Temp

• 延伸閱讀
• [SQL] 次序函數應用-重覆資料不顯示
• [SQL] 次序函數應用-資料分組

[EFCore] ExecuteUpdate 和 ExecuteDelete

ExecuteUpdate 和 ExecuteDelete 為 EFCore 7 推出功能，可以直接對單一 Table 進行 update 和 delete，不牽涉到 Entity Tracking，且該語法會直接執行，不需要使用 SaveChange()，但SaveChange() 自帶交易，而 ExecuteDelete 和 ExecuteUpdate 沒有，要自行開啟 Transaction 來確保一致性喔

ExecuteDelete

master-detail table 架構 EX：訂單，要使用 ORM 刪除的話，語法會如同下列

internal class Program
{
    static void Main(string[] args)
    {
        using var dbContext = new AdventureWorks2022Context();

        var header = dbContext.SalesOrderHeaders.Single(w => w.SalesOrderId == 43659);
        var detail = dbContext.SalesOrderDetails.Where(w => w.SalesOrderId == 43659).ToList();

        dbContext.SalesOrderDetails.RemoveRange(detail);
        dbContext.SalesOrderHeaders.Remove(header);

        dbContext.SaveChanges();
    }
}

使用 SQL Profile 側錄語法可以看見是先把資料抓出來後，根據 SalesOrderDetail Table PK 一筆一筆資料進行刪除

下列為 SalesOrderDetails 刪除語法，留三個 delete 來示意

exec sp_executesql N'SET NOCOUNT ON;
DELETE FROM [Sales].[SalesOrderDetail]
WHERE [SalesOrderDetailID] = @p0 AND [SalesOrderID] = @p1;
SELECT @@ROWCOUNT;

DELETE FROM [Sales].[SalesOrderDetail]
WHERE [SalesOrderDetailID] = @p2 AND [SalesOrderID] = @p3;
SELECT @@ROWCOUNT;

DELETE FROM [Sales].[SalesOrderDetail]
WHERE [SalesOrderDetailID] = @p4 AND [SalesOrderID] = @p5;
SELECT @@ROWCOUNT;

',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 int,@p5 int,@p0=15,@p1=43661,@p2=16,@p3=43661,@p4=17,@p5=43661'

以往為了提高效能就乾脆直接下 TSQL 根據訂單編號去刪除，現在有 ExecuteDelete 後就可以直接來進行刪除

internal class Program
{
    static void Main(string[] args)
    {
        using var dbContext = new AdventureWorks2022Context();
        using var tran = dbContext.Database.BeginTransaction();
        
        dbContext.SalesOrderDetails
            .Where(w => w.SalesOrderId == 123)
            .ExecuteDelete();
        
        dbContext.SalesOrderHeaders
            .Where(w => w.SalesOrderId == 123)
            .ExecuteDelete();
        
        tran.Commit();
    }
}

ExecuteUpdate

ExecuteUpdate 可以透過 SetProperty 來針對多個欄位進行資料更新，用個範例紀錄

internal class Program
{
    static void Main(string[] args)
    {
         using var dbContext = new AdventureWorks2022Context();
         
         int numUpdated = dbContext.SalesOrderDetails
                .Where(w => w.SalesOrderId == 43664)
                .ExecuteUpdate(e =>
                e.SetProperty(sod => sod.ModifiedDate, DateTime.Now));
         
         Console.WriteLine("影響筆數 : " + numUpdated);
    }
}

• 參考資料
• ExecuteUpdate 和 ExecuteDelete （大量更新）
• ExecuteUpdate 和 ExecuteDelete
• EF Core Execute Update
• Entity Framework（EF）7簡介 - 1
• 在 EF Core 7.0 中針對大量更新與大量刪除的效能改善

[SQL] 群組字串連結 - 換行符號

在 VFP AP 端有個備註欄位讓使用者進行輸入，使用者輸入時有使用到換行，導致使用 for xml path 來群組字串時，報表出現換行符號，如下圖

Product 環境 SQL Server 升級後，有 STRING_AGG 可以取代使用，但還是特別去查 for xml path 要如何避免該問題，從 Handling special characters with FOR XML PATH(”) 內查到解決方式，沒有特別去深入研究，單純紀錄而已

以 [SQL] 群組字串連結 文章內範例來示意並記錄，語法修正如下

SELECT
    Date AS 日期,
    DATENAME(dw,Date) AS 星期 ,  
    (
        STUFF
        (
            (
                SELECT ',' + EmpName 
                FROM Leave T2
                WHERE T2.Date = T1.Date
                FOR XML PATH('')  , ROOT('StringAgg') , TYPE -- 重點
            ).value('/StringAgg[1]' , 'varchar(max)')        -- 重點
        , 1 , 1 , '' 
        )
    ) AS [請假人員]
FROM Leave T1
GROUP BY Date

希望未來沒有機會再使用 for xml path 來進行群組字串

• 參考資料
• [電腦] ASCII 字元與 HTML 編碼對照表

[SQL] 避免 SELECT * - 減少資料量

效能調校建議不要使用 SELECT * 抓全部欄位資料，需要哪些欄位資料就明確寫出來，可以減少回傳資料量，下圖是利用 [用戶端統計資料 (Client Statistics)] 來觀察