Today I was working with EF Core and I was implementing some IQueryable filters using this guide. When I was performing my unit tests to see if I did everything properly I noticed that some search patterns didn't work as espected: after a few attempts I could understand that it was a typical casing problem: more specifically, my test search - which I implemented using a standard DbContext.Entity.Where(i => i.TextField.Contains("Store")) Lambda expression - returned no results because the record that I was hoping to retrieve contained such words in lowercase ("store").
The issue
That seemed odd to me at first, because I remember that Contains() was intended to be case-insensitive in EF Core: after some digging on StackOverflow I eventually found that the string.Contains() method's case-insensitive behaviour used to be the case for Entity Framework and the first versions of Entity Framework Core (1.x or so), but that it was changed later on to a case-sensitive implementation.
More specifically, string.Contains doesn't convert anymore into a generic LIKE expression (which is case insensitive in most DBMS) but to DB-specific functions, such as LOCATE() (for MySQL and MariaDB), CHARINDEX() (for SQL Server), instr() (for Oracle and SQLite) and so on... Which are often case sensitive by default, unless the DB or column collation is defined otherwise.
The fix
Now that I've correctly identified the problem, I could think about how to work around it. Changing the DB / column collation didn't seem quite the right approach, at least for my scenario: luckily enough, I've found that the EF Core MySQL/MariaDB provider which I was using (Pomelo.EntityFrameworkCore.MySql) supported the string.Contains() method overload that accepts a StringComparison parameter.
This allowed me to change my initial Lambda expression:
|
1 2 |
DbContext.Entity.Where(i => i.TextField .Contains("Store")) |
Which translates into the following case-sensitive MySQL query:
|
1 2 |
WHERE (LOCATE(CONVERT(@__value_0 USING utf8mb4) COLLATE utf8mb4_bin, `b`.`Name`) > 0) |
To the following one:
|
1 2 |
DbContext.Entity.Where(i => i.TextField .Contains("Store", StringComparison.InvariantCultureIgnoreCase)) |
Which translates into the following case-insensitive MySQL query:
|
1 2 |
WHERE (LOCATE(CONVERT(LCASE(@__value_0) USING utf8mb4) COLLATE utf8mb4_bin, LCASE(`b`.`Name`)) > 0) |
Thus fixing my issue for good.
Conclusions
That's basically it: I hope that this post will help other ASP.NET Core and Entity Framework Core developers that are looking for a way to perform case-insensitive queries using the string.Contains() method.
