How to make EF.Functions.Like case-insensitive?

Question

I'm using the EF Core 3.1 + MySQL, and using this method for querying:

IQueryable<ApplicationUser> customers = from u in _context.Users where (u.Customer != null && u.IsActive) select u;
if (!String.IsNullOrEmpty(searchString))
{
customers = customers.Where(s => s.Email.Contains(searchString));
}

And I upgrade to using EF.Function.Like for better performance:

if (!String.IsNullOrEmpty(searchString))
{
customers = customers.Where(x => EF.Functions.Like(x.Email, $"%{searchString}%"));
}

But it is case-sensitive, how to make it case-insensitive?

score 4 · Answer 1 · answered Dec 10 '20 at 09:31

From the docs:

Note that if this function is translated into SQL, then the semantics of the comparison will depend on the database configuration. In particular, it may be either case-sensitive or case-insensitive. If this function is evaluated on the client, then it will always use a case-insensitive comparison.

So, it depends on your server.

As a workaround you can make the argument uppercase.

if (!String.IsNullOrEmpty(searchString))
{
    customers = customers.Where(x => EF.Functions.Like(x.Email.ToUpper(), $"%{searchString.ToUpper()}%"));
}

According to this there ought to be a function mapping to UPPER so you get the right SQL.

Watch out for performance degradation when using this particular workaround though. See comments here: https://stackoverflow.com/a/56043524/4458009 — Solomon Metta, Jul 14 '22 at 11:19

score 3 · Accepted Answer · answered Dec 10 '20 at 09:25

3

According to the docs:

Note that if this function is translated into SQL, then the semantics of the comparison will depend on the database configuration. In particular, it may be either case-sensitive or case-insensitive. If this function is evaluated on the client, then it will always use a case-insensitive comparison.

Therefore, it's not the function that is case-sensitive, but your database/column collation settings. You can use the Collate function to specify a different, case-insensitive collation. More on MySQL collations.

answered Dec 10 '20 at 09:25

Riwen

4,734
2
19
31

1

Funny we found the exact same quote. I was a little slow in finishing, though. I'll leave my answer since it's a little different from yours. – Palle Due Dec 10 '20 at 09:33
Thank you, I had change my mind, As your suggestion, I change my DB to make it case-insensitive. – Fair Dec 11 '20 at 01:52

How to make EF.Functions.Like case-insensitive?

2 Answers2