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

原文链接：https://www.cnblogs.com/Tiger-Lu/archive/2023/04/13/17311560.html