Entity Framework Core: case-insensitive Contains() In Entity Framework, the Contains() method used to be case-insensitive: however, such behavior has been changed in EF Core. Here's how to revert it back and perform case-insensitive searches

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.

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.