EF core string case sensitivity not working

I have a piece of code which works in EF Core 2.2 used to compare string casing as shown below.

public async Task<bool> DoesItemNumberExists(Guid revisionId, string itemNumber)
{
    var doesExist = await _repository.AnyAsync(a => string.Equals(a.ItemNo, itemNumber, StringComparison.Ordinal) && a.SoqHeading_NP.SoqRevisionId == revisionId);

    return doesExist;
}

I run the same code in EF Core 5 and the application crashes. Any help?

Below is the exception i get

The LINQ expression 'DbSet<SoqItem>()
    .Where(s => s.IsDeleted == False)
    .Join(
        inner: DbSet<SoqHeading>()
            .Where(s0 => s0.SoqRevisionId == __ef_filter__RevisionId_0 && s0.IsDeleted == False), 
        outerKeySelector: s => EF.Property<Nullable<Guid>>(s, "SoqHeadingId"), 
        innerKeySelector: s0 => EF.Property<Nullable<Guid>>(s0, "Id"), 
        resultSelector: (o, i) => new TransparentIdentifier<SoqItem, SoqHeading>(
            Outer = o, 
            Inner = i
        ))
    .Any(s => string.Equals(
        a: s.Outer.ItemNo, 
        b: __itemNumber_0, 
        comparisonType: Ordinal) && s.Inner.SoqRevisionId == __revisionId_1)' could not be translated. Additional information: Translation of the 'string.Equals' overload with a 'StringComparison' parameter is not supported. See https://go.microsoft.com/fwlink/?linkid=2129535 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Solution

Case-sensitivity and collations are explained in the docs, in the docs, in Collations and Case Sensitivity

That's not an EF Core 5 bug. The query always failed to get translated to SQL but EF Core 2 covered this up by loading everything in memory then matching the records on the client without the benefit of indexing. LINQ translation in the first versions of EF Core was so limited that even GROUP BY couldn't be translated. Entity Framework would throw in such cases. To avoid breaking code that worked perfectly in EF 6 though, EF Core 1 and 2 used client-side evaluation: They translated what they could to SQL then loaded the data in-memory, on the client, and execute the rest of the query using LINQ to Objects.

This means that if you wanted to calculate a SUM for 100K rows, EF Core 1-2 would load all 100K rows in memory and proceed to add the values one by one. Never mind joining two tables with 1000 rows each - that's 1M comparisons.

Even in EF Core 2.1 though, client-side evaluation would generate runtime warnings and could be disabled completely. In EF Core 3.1 client-side evaluation was disabled completely.

To get your query to work properly don't try to force the case or the collation. Just use a simple equality :

var itemExists=context.Products.Any(a=>a.ItemNumber == itemNumber && 
                                       a.SoqHeading_NP.SoqRevisionId == revisionId);

This will be translated to WHERE ItemNumber=@itemNumber && SoqHeading_NP.SoqRevisionId = @revisionId. The query will use any indexes that cover the ItemNumber and SoqRevisionId columns to produce a result as fast as possible.

The collation used for the equality match is the column's collation. If that is case sensitive, you get case-sensitive matching. If not, you get case-insensitive matching. Indexes are built using the column's collation, so if you try to use a different collation for matching you'll prevent the server from using any indexes.

If you want to use different case matching in different queries and still use indexes, you need to create different indexes for each case. How you do that depends on the database

In SQL Server, case-insensitive is the most common option. To use both that and case-sensitive search, you can create an index a computed column with a binary (hence case-sensitive) collation, eg:

alter table Table1 add ItemNumberCS as COLLATE ..._BIN;
create index IX_Table1_ItemNumberCS on Table1 (ItemNumberCS);

Case-sensitive queries should use the ItemNumberCS column.

In PostgreSQL all collations are case-sensitive. Since v12 though, you can create a custom collation and use it in a computed index expression. To use a case-insensitive search, you can create a case-insensitive collation and index eg:

CREATE COLLATION case_insensitive (
      provider = icu,
      locale = 'und-u-ks-level2',
      deterministic = false
);

CREATE INDEX IX_Table1_ItemNumberCI ON Table1 (title COLLATE "case_insensitive");`

The LINQ query won't have to change.