EF core string case sensitivity not working

Question

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.

Answer 1

Because StringComparison.Ordinal statement can't be translate to SQL query.

You should read data without StringComparison.Ordinal, and when data read from SQL and come to application memory then you can use StringComparison.Ordinal.

public async Task<bool> DoesItemNumberExists(Guid revisionId, string itemNumber)
{
    var selectedRows = await _dbContext.YourTable.Where(a => a.ItemNo == itemNumber  && a.SoqHeading_NP.SoqRevisionId == revisionId).ToListAsync();
    return selectedRows.Any(a =>  string.Equals(a.ItemNo, itemNumber, StringComparison.Ordinal));
}

Microsoft reference:

Before 3.0, when EF Core couldn't convert an expression that was part of a query to either SQL or a parameter, it automatically evaluated the expression on the client. By default, client evaluation of potentially expensive expressions only triggered a warning.

New behavior Starting with 3.0, EF Core only allows expressions in the top-level projection (the last Select() call in the query) to be evaluated on the client. When expressions in any other part of the query can't be converted to either SQL or a parameter, an exception is thrown.

Why?

Automatic client evaluation of queries allows many queries to be executed even if important parts of them can't be translated. This behavior can result in unexpected and potentially damaging behavior that may only become evident in production. For example, a condition in a Where() call which can't be translated can cause all rows from the table to be transferred from the database server, and the filter to be applied on the client. This situation can easily go undetected if the table contains only a few rows in development, but hit hard when the application moves to production, where the table may contain millions of rows. Client evaluation warnings also proved too easy to ignore during development.

Besides this, automatic client evaluation can lead to issues in which improving query translation for specific expressions caused unintended breaking changes between releases.

Answer 2

TLDR: The important part is to compare two strings with EF.Functions.Collate(string1, "SQL_Latin1_General_CP1_CS_AS") == string2

---

As mentionned above by @Panagiotis Kanavos, all relevant info about case-sensitivity and collate are found in Collations and case sensitivity - EF Core | Microsoft Docs

A quick solution that worked for me with minimal code refactoring is with using an explicit collation query and do the following:

public async Task<bool> DoesItemNumberExists(Guid revisionId, string itemNumber) {
    var doesExist = await _repository.AnyAsync(a => EF.Functions.Collate(a.ItemNo, "SQL_Latin1_General_CP1_CS_AS") == itemNumber && a.SoqHeading_NP.SoqRevisionId == revisionId);

    return doesExist; 
}