CODEWITHSUNDEEP
c#entity-frameworkasp.net-core-webapi
Fair
Fair

Reputation: 491

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

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?

Upvotes: 1

Views: 6851

Answers (3)

Joshit
Joshit

Reputation: 1325

For postgres use EF.Functions.ILike:

customers = customers.Where(x => EF.Functions.ILike(x.Email, $"%{searchString}%"));

This will be translated to this query:

SELECT c."Email"
FROM yourSchema."Customers" AS c
WHERE c."Email" ILIKE @__Format_1 ESCAPE ''

Whereas toLower (or toUpper) gets called explicitly:

SELECT c."Email"
FROM yourSchema."Customers" AS c
WHERE lower(c."Email") LIKE @__ToLower_0_rewritten ESCAPE '\'

Here is the documentation from npgsql for further reading.

Upvotes: 0

Palle Due
Palle Due

Reputation: 6312

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.

Upvotes: 5

Riwen
Riwen

Reputation: 5200

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.

Upvotes: 3

Related Questions