SQL Server key values are compared case-insensitively in EFCore 8

新版本在UAT階段採到這個坑，藉此機會紀錄一下。

Issue

後端API升級至.net 8，相關dependency也一併做升級來相容，因次這次也將EFCore升級到8。
發現在升級的git branch上載入特定模組會回500 error。實際trace後發現是程式寫法會將EFCore query回的結果依照索引來產生dictionary，在產生dictionary時遇到duplicate key的excpetion。

分析

這裡假設table: test中有欄位A並為Primary key。其中有兩筆資料其A的value分別為"Scott"與"SCOTT"。

• 以資料邏輯來說是絕對不會產生這種exception。EFCore做mapping使用的肯定是DB table內的Primary key。

• 這段邏輯不在此次升級的改動範圍內。

• 同樣的程式在Production沒有出現同樣錯誤，且使用master branch的code連UAT的DB發現並沒有出現相同錯誤。

• 兩個branch最大差異就是dotNet版本。

• 確認table所使用排序(Collation)類型為"Latin1_General_BIN"，是case-sensitive。

查詢表格每個欄位的排序類型

select COLUMN_NAME, COLLATION_NAME
  from INFORMATION_SCHMA.COLUMNS
where TABLE_NAME = 'YOUR_TABLE_NAME'

• 在EFCore LINQ的Where()條件內直接指定排列(Collation)後，查詢結果回傳"兩筆"A欄位value皆為"SCOTT"的物件。代表指定有效回傳兩筆(如預期大小寫不敏感視為相同)，但內容肯定在某個階段被改為大寫。

CI: Case-Insensitive

context.YOUR_TYPE.Where(it => EF.Functions.Collate(it.A, "Latin1_General_CI_AI") == "Scott")

• 相反，在EFCore LINQ的Where()條件內直接指定排列(Collation)為大小寫敏感後，查詢結果回傳"一筆"A欄位value皆為"Scott"的物件。再次證明指定有效回傳一筆(如預期大小寫敏感視為不同)且沒有被改為大寫。

CI: Case-Sensitive

context.YOUR_TYPE.Where(it => EF.Functions.Collate(it.A, "Latin1_General_CS_AI") == "Scott")

綜合以上，已經可以將懷疑目標調整至dotNet與EFCore是否在新版中對字串大小寫的比較方面是否有改動了。

調查/蒐集資料

直接上結果:
https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/breaking-changes#casekeys