Site icon Ryadel

Entity Framework Core: case-insensitive Contains()

BuildWebHost - Unable to create an object of type ApplicationDbContext error in EF Core 2.x - How to Fix

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:

Which translates into the following case-sensitive MySQL query:

To the following one:

Which translates into the following case-insensitive MySQL query:

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.

UPDATE: if you're using the System.Linq.Dynamic.Core library and want to extend such workaround to perform case-insensitive Contains() search using that library as well, read this post.
If you want to know more about .NET Core and Angular check out the ASP.NET Core 5 and Angular book, available as paperback and e-book.
Exit mobile version