EF core string case sensitivity not working-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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.