16

Before EF Core 3.0 this worked fine (evaluated on server+client):

var exists = await _context.Countries.AsNoTracking().AnyAsync(x => x.CountryCode.Equals(country.CountryCode, StringComparison.OrdinalIgnoreCase));

What is the best/preferred method to translate the string.Equals(str, StringComparison.OrdinalIgnoreCase)-part now in EF Core 3.0, so that the query evaluates only on the server side.

var exists = await _context.Countries.AsNoTracking().AnyAsync(x => x.CountryCode.ToUpper() == country.CountryCode.ToUpper());

or

var exists = await _context.Countries.AsNoTracking().AnyAsync(x => x.CountryCode.ToLower() == country.CountryCode.ToLower());

or

var exists = await _context.Countries.AsNoTracking().AnyAsync(x => x.CountryCode.ToUpperInvariant() == country.CountryCode.ToUpperInvariant());

or

var exists = await _context.Countries.AsNoTracking().AnyAsync(x => x.CountryCode.ToLowerInvariant() == country.CountryCode.ToLowerInvariant());

or something else?

juFo
  • 17,849
  • 10
  • 105
  • 142
  • That's a bug in the code, not a problem with EF. Case sensitivity is controlled by the *column's* collation. If the column uses a case-*in*sensitive collation, you get case-insensitive matching. Indexes use the collation too, so trying to match using *different* rules or worse, applying any kind of function, prevents the server from using any indexes. – Panagiotis Kanavos Oct 31 '19 at 11:17
  • The *real* solution was posted by L.Trabacchin. *Remove* `Equals* altogether and ensure the column uses a case-insensitive collation. All the snippets posted in the question will result in a bad query. `x => x.CountryCode.==country.CountryCode` will work just fine – Panagiotis Kanavos Oct 31 '19 at 11:17
  • 1
    BTW this means your code had a serious performance bug already that was uncovered because EF Core disabled client-side evaluation. Until now, your query would load *everything* in the client's memory before filtering. – Panagiotis Kanavos Oct 31 '19 at 11:21

1 Answers1

23

You should not do that, nor use the accepted answer method, you should just use String.Equals() without parameters and configure your database collation, during creation or migration.

L.Trabacchin
  • 1,538
  • 1
  • 17
  • 34
  • Looks like today I learned something new. Thanks. Am I correct that the default is already case insensitive ( Latin1_General_CI_AS ) ? – juFo Oct 31 '19 at 11:51
  • 1
    Usually it is, but if it's imperative for you, just make sure it is. Have a look: there is a bunch of way of doing it: https://stackoverflow.com/questions/12054930/set-database-collation-in-entity-framework-code-first-initializer – L.Trabacchin Oct 31 '19 at 12:42
  • 3
    How do we support this scenarios: In one query I need to be Case Sensitive and in another Case Insensitive. eg. our search should be Case Insensitive, but then I want to load by exact match when I work with the entity. – Petr J Feb 03 '21 at 09:17
  • ef core support custom query... configure the default scenario, then use a custom one https://learn.microsoft.com/en-us/ef/core/querying/raw-sql – L.Trabacchin Feb 03 '21 at 11:07