Entity Framework Core case-sensitive processing

Entity Framework Core case-sensitive processing

Can ‘StringComparison’ be used?

In the database query operation, it is inevitable to consider the issue of letter case. For example, if you want to search for the movie “X-Men” in the Movie table, in order not to distinguish between letter case and case, according to the habit of Linq to memory, you may Will write the following code:

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

However, an error will be reported when the above code is executed, indicating that the ‘StringComparison’ parameter is not supported.

InvalidOperationException: The LINQ expression 'DbSet() .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.

How to solve this problem? If you are using SQL server or MySQL, it is very simple, just remove the “StringComparison.InvariantCultureIgnoreCase” parameter, because they are case-insensitive by default. If you are using a case-sensitive database, such as PostgreSQL, you need to do some special processing, which will be mentioned later.

There is a violent solution to this kind of problem, which is to filter on the query client according to the error prompt:

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

The essence of this is to return all Movies, and then do Title matching query in memory (Linq to memory), instead of doing Title matching query in DB, so the error that the ‘StringComparison’ parameter is not supported will not be reported . This method is not recommended in most scenarios, because 1. The performance is poor, the query is in the memory instead of the DB, 2. The memory is occupied, and the entire table is returned

Why can’t I use ‘StringComparison’?

As mentioned earlier, some databases are case-insensitive by default: SQL server, MySQL; some databases are case-sensitive by default: PostgreSQL. So what controls case sensitivity in the database? Collation (Collation, a set of rules used to determine the sorting and equality comparison of text values, you can create collations on databases, tables and columns, sorting The rules are implicitly inherited. For example, if a collation is created on the database but not on a table, the table will use the database’s collation by default, and the same applies to columns). Because EF core does not know what kind of collation is supported/applied by database, table, column, so ‘StringComparison’ is meaningless, EF core will convert string.Equals into database equal ( = ) operation, which is determined by the database according to the column The collation applied to determine case sensitivity.

How to set case sensitivity in query?

Since case sensitivity is determined by the collation, we can set whether it is case-sensitive by specifying the collation in the query. For example, SQL Server is case-insensitive by default. We can pass EF.Functions .Collate to specify a case-sensitive collation “SQL_Latin1_General_CP1_CS_AS” to achieve exact matching

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

However, this method of displaying the specified collation is not very recommended, because it will cause index matching failure, which will affect query performance. The index implicitly inherits the collation on the column. When the specified collation is displayed and the collation specified when creating the index (PostgreSQL supports specifying the collation when creating the index) or the collation of the column is inconsistent, the index will fail to match, and then The query cannot apply the index, which is another reason why EF Core does not convert ‘StringComparison’ into a collation.

So a relatively complete solution is to specify the appropriate collation on the column according to the business model, rather than setting it in the code. If the database supports creating multiple indexes on the column, you can also switch the collation according to the business scenario by displaying the specified collation to match the correct index.

Referencesfile:

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