Cosmos: Translate string.Contains, StartsWith, EndsWith with ComparisonType

[aguang-xyz]

Description

The CONTAINS function of Cosmos DB supports case-insensitive searching. So I'm assuming that in EF Core with Cosmos Provider, we should be able to use string.contains with comparisionType specified. But when I try to do that, I got a translation failure.

My code

var keyword = "xxx";
var count = _dbContext<ProductDAO>()
                      .Where(product => product.Name.Contains(keyword, StringComparison.OrdinalIgnoreCase))
                      .Count();

Stack traces

System.InvalidOperationException
The LINQ expression 'DbSet<ProductDAO>()
    .Where(d => d.Name.Contains(
        value: __word_0, 
        comparisonType: OrdinalIgnoreCase))' could not be translated. Additional information: Translation of method 'string.Contains' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 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.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Cosmos.Query.Internal.CosmosQueryableMethodTranslatingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Cosmos.Query.Internal.CosmosQueryableMethodTranslatingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.Count[TSource](IQueryable`1 source)

Include provider and version information

EF Core version: 5.0.7
Database provider: Microsoft.EntityFrameworkCore.Cosmos:5.0.7
Target framework: NET 5.0
Operating system: macOS Big Sur 11.4
IDE: Rider 2021.1

[roji]

We should probably take a look at Cosmos case sensitivity in general.

For relational databases we don't support StringComparison since collations are a much better fit. However, in Cosmos there's no concept of a column collation, and functions such as CONTAINS and even STRINGEQUALS support specifying case-sensitivity as a flag.

One thing I'm not sure about, is whether indexes are used for both case-sensitive and insensitive matching (double-indexing?). Some resources include this and this.

Bottom line - it seems right to translate StringComparison.OrdinalIgnoreCase as proposed.

[svrooij]

I just stumbled upon this same problem, maybe there is a solution around the corner. Since Cosmos DB CONTAINS actually has support for case-insensitivity, as the third argument.

This pr added support for the current CONTAINS implementation.

Mind that this page states it's cheaper (RU wise) to use StartsWith but that should be up to yourself.

[roji]

Mind that this page states it's cheaper (RU wise) to use StartsWith but that should be up to yourself.

Thanks for this link - that's helpful. Though CONTAINS and StartsWith are two different functions, with different usages.

[chajere]

Is there any progress on this?
I'm having an issue where I'm using ODATA and EF:
OData query is: $filter=(contains(Field,'hol'))
EF generated Cosmos Query is: CONTAINS(c["Field"], "hol")

I need to enable the case-insensitive flag. CONTAINS(c["Field"], "hol", true)