DEV Community

ScottPony
ScottPony

Posted on

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'
Enter fullscreen mode Exit fullscreen mode
  • 在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")
Enter fullscreen mode Exit fullscreen mode
  • 相反,在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")
Enter fullscreen mode Exit fullscreen mode

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

調查/蒐集資料

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

Do your career a favor. Join DEV. (The website you're on right now)

It takes one minute and it's free.

Get started

Top comments (0)

Cloudinary image

Optimize, customize, deliver, manage and analyze your images.

Remove background in all your web images at the same time, use outpainting to expand images with matching content, remove objects via open-set object detection and fill, recolor, crop, resize... Discover these and hundreds more ways to manage your web images and videos on a scale.

Learn more

👋 Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay