Is there any way to add 'System.StringComparison.OrdinalIgnoreCase' in startsWith in Where clause in linq C#

Question

I'm getting an error

The LINQ expression 'DbSet .Where(t => t.ZipCode.StartsWith( value: __zipCode_0, comparisonType: OrdinalIgnoreCase))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().

and this is my code:

await DbSet
     .Where(x => x.ZipCode.StartsWith(zipCode, System.StringComparison.OrdinalIgnoreCase))
     .OrderBy(n => n.ZipCode)
     .ToListAsync(cancellationToken);

Can anyone help me, please? I have tried removing StringComparison.OrdinalIgnoreCase and it's working fine but it's not the solution I guess.

Answer 1

The case-sensitivity for the results of queries is defined by the Collation property. The collation can be defined at various levels.

• Server Level
• Database Level
• Table Level
• Column Level

The collation of the server is set during the installation of instance. At all other levels if the collation is not defined then the default value from higher level is taken into consideration. To check the current collation of the server we can run the following query:

SELECT Serverproperty('COLLATION')

If the result looks like “SQL_Latin1_General_CP1_CI_AS” then it is case insensitive.

The reason being in the Collation, 'CI' indicates that collation is case insensitive. If 'CS' is present than collation will be case sensitive.

If you find CI in your collation, then the query will generate results ignoring the case. No need to again specify it in 'StartsWith()' method.

Came across this wonderful article which explain collation in more detail:

Collation in sql server

Answer 2

Though I agree with the case insensitivity of the Linq to SQL, But the actual problem seems to be related to Linq query evaluation by the EF core.

On a general note you have to look into the Client vs. Server Evaluation. Entity Framework Core attempts to evaluate a query on the server as much as possible.There are some scenarios where the top level client projections can be evaluated into client side but due to performance issues Entity Framework Core blocks such client evaluation and throws a runtime exception.

You can still run your as is without exception with client side evaluation if your requirement follows below two rules:

1. The amount of data is small so that evaluating on the client doesn't incur a huge performance penalty.
2. The LINQ operator being used has no server-side translation.

For demonstration purpose I used the below sample code using AsEnumerable() where Values is the Dbset in my case:

var orders =  _context.Values.AsEnumerable()
              .Where(x => x.Name.StartsWith("S", System.StringComparison.OrdinalIgnoreCase))
              .OrderBy(n => n.Name)
              .ToList();

As EF core always runs the queries in server side always make sure you are rewriting the queries so that you EF can run them in server side as much as possible!