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