Entity Framework Core 大小寫敏感處理

可以使用'StringComparison'嗎？

在資料庫查詢操作中，不可避免去考慮字母大小寫的問題，比如要在Movie表中查找“X-Men”這部電影，為了不區分字母大小寫，按照Linq to memory的習慣，可能會寫出如下代碼：

DbContext.DbSet<Movie>
　　.Where(item => string.Equals(item.Title, "X-Men", StringComparison.InvariantCultureIgnoreCase)

但是上述代碼執行會報錯，提示 'StringComparison'參數不支持。

InvalidOperationException: The LINQ expression 'DbSet<Movie>() .Where(m => string.Equals( a: m.Genre, b: __MovieGenre_0, comparisonType: InvariantCultureIgnoreCase))' could not be translated. 
Additional information: Translation of the 'string.Equals' overload with a 'StringComparison' parameter is not supported. 
See https://go.microsoft.com/fwlink/?linkid=2129535 for more information. 
Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. 
See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

怎麼解決這個問題呢？如果你用的是SQL server或MySQL，那麼很簡單，去掉“StringComparison.InvariantCultureIgnoreCase”這個參數就可以了，因為他們是預設大小寫不敏感的。如果用的是大小寫敏感的資料庫，比如PostgreSQL，就需要做一些特殊處理了，後面會講到。

對於這類問題有一個暴力的解決方法，就是按報錯提示的那樣在查詢客戶端做篩選:

DbContext.DbSet<Movie>
　　.ToList()
　　.Where(item => string.Equals(item.Title, "X-Men", StringComparison.InvariantCultureIgnoreCase)

這樣做的本質是返回所有的Movie，然後在記憶體中做Title匹配查詢（Linq to memory），而不是在DB中做Title的匹配查詢，所以不會報 'StringComparison'參數不支持的錯誤。這種方法在絕大多數場景下是不推薦的，因為 1. 性能差，查詢是在記憶體中不是在DB中，2. 占用記憶體，返回了整張表

為什麼不能使用'StringComparison'？

前面講到有的資料庫是預設大小寫不敏感的：SQL server，MySQL；有的資料庫是預設大小寫敏感的：PostgreSQL。那麼資料庫中大小寫敏感與否是由什麼控制的呢，排序規則（Collation，一組規則，用於確定文本值的排序和相等性比較方式，可以在資料庫，表和列上創建排序規則，排序規則是隱式繼承的，比如在資料庫上創建了排序規則，沒有在表上創建排序規則，那麼表會預設使用資料庫的排序規則，列同理）。因為EF core不知道資料庫，表，列支持/應用了什麼樣的排序規則，所以'StringComparison'是沒有意義的，EF core會將  string.Equals 轉化成資料庫的相等（ = ）操作，由資料庫根據列上應用的排序規則來決定是否區分大小寫。

如何在查詢中設置區分大小寫與否？

既然區分大小寫是由排序規則決定的，我們可以通過在查詢中指定排序規則的方式來來設置是否區分大小寫，例如SQL Server預設是不區分大小寫的，我們可以通過 EF.Functions.Collate 來指定一個大小寫敏感的排序規則"SQL_Latin1_General_CP1_CS_AS"來達到精確匹配的目的

DbContext.DbSet<Movie>
    .Where(m => EF.Functions.Collate(item.Title, "SQL_Latin1_General_CP1_CS_AS") == "X-Men")

但是這種顯示指定排序規則的方法也不是非常推薦的，因為他會導致索引匹配失敗，進而影響查詢性能。索引隱式繼承列上的排序規則，當顯示指定的排序規則和創建索引時指定的排序規則（PostgreSQL支持在創建索引時指定排序規則）或列的排序規則不一致時，會導致索引匹配失敗，進而導致查詢不能應用索引，這也是EF Core沒有將'StringComparison'轉換成排序規則的另一個原因。

因此一個相對完善的解決方案是，根據業務模型在列上指定合適的排序規則，而不是在代碼中設置。如果資料庫支持在列上創建多個索引，你也可以用顯示指定排序規則的方式根據業務場景切換排序規則來匹配正確的索引。

參考文檔：

https://learn.microsoft.com/en-us/ef/core/miscellaneous/collations-and-case-sensitivity

https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver16

https://www.postgresql.org/docs/current/collation.html