CODEWITHSUNDEEP
c#databaseentity-framework-coreazure-cosmosdb
Ivan Kiselychnyk
Ivan Kiselychnyk

Reputation: 43

Entity Framework Core Cosmos Db - How to compare strings, case insensitive?

I tried a few variants:

entity.FirstOrDefaultAsync(x => string.Equals(x.Id, Id, StringComparison.OrdinalIgnoreCase));
entity.FirstOrDefaultAsync(x => x.Id.Equals(Id, StringComparison.OrdinalIgnoreCase));

Also tried ToUpper() method:

var IdUpper = Id.ToUpper();
entity.FirstOrDefaultAsync(x => x.Id.ToUpper() == idUpper);

But all solution throw error:

System.InvalidOperationException: The LINQ expression could not be translated

Is there a solution that would support EF Core for Cosmos Db?

Upvotes: 4

Views: 2150

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131374

Case-insensitive StringEquals was added to CosmosDB just this June. You should update your EF Core Cosmos provider to the latest version. Versions released before June 2020 won't have this feature.

I can't find any release notes or GitHub issues that specify which version first introduced case-invariant comparisons, so I can't say which version introduced this feature. Package dependencies show that v 5.0 RC1 is the first to take an explicit dependency on a CosmosDB SDK with that feature.

In case of problems it's probably better to use the Cosmos .NET SDK directly.

As for why this is an issue at all, string comparisons are performed on the server, based on a field's collation. The collation specifies the sort order to use and which characters are considered equal. AA in Danish is considered a single letter that comes after Z for example. Database indexes are built using that collation, so trying to search for values using a different collation won't be able to use any existing indexes. The server is forced to scan all the data and calculate equality and ordering. Using functions like ToLower() is just as expensive.

Upvotes: 3

Related Questions