ODATA Core with Cosmos Core , OData filter functions like startswith is not working

[reddaiahnethi]

most of the ODATA filter functions like startswith , endswith are not working

Assemblies affected

*Microsoft.AspNetCore.OData 7.4.0
Microsoft.EntityFrameworkCore.Cosmos 3.1.3

Reproduce steps

*Simply define a OdataController as below and hit the url //localhost:5001/Candidate?$filter=startswith(firstName,%27Richard1%27)%20eq%20true

  [HttpGet]        
        [EnableQuery(PageSize = 1)]
        public IActionResult GetCandidate(ODataQueryOptions<Candidate> options)
        {
            var cand = _dbContext.Candidates;
            return Ok(cand);

        }

Expected result

should see the candidates matching the firstname

Actual result

Got the below exception

InvalidOperationException: The LINQ expression 'DbSet<Candidate>
.Where(c => __TypedProperty_0 == "" || c.firstName != null && __TypedProperty_0 != null && c.firstName.StartsWith(__TypedProperty_0) == __TypedProperty_1)' 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(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|8_0(ShapedQueryExpression translated, ref <>c__DisplayClass8_0 )
TargetInvocationException: Exception has been thrown by the target of an invocation.
System.RuntimeMethodHandle.InvokeMethod(object target, object[] arguments, Signature sig, bool constructor, bool wrapExceptions)

Additional detail

One more instance with Any

https://localhost:5001/Candidate?$filter=CustomProperties/any(cd:%20cd/Key%20eq%20%27PersonalNumber%27)

public class Candidate
    {
        public int candidateID { get; set; }
       ..
       ..
        public IEnumerable<CustomProps> CustomProperties {set;get;}


    }

  public class CustomProps
    {
        public string Key { get; set; }
        public string Vaule { get; set; }
    }

An unhandled exception occurred while processing the request.
InvalidOperationException: The LINQ expression 'DbSet<Candidate>()
.Where(c => (int)c.candidateType == (int)__candidateType_0)
.Where(c => EF.Property<IEnumerable<CustomProps>>(c, "CustomProperties")
.AsQueryable()
.Any(o => o.Key == __TypedProperty_1))' 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(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|7_0(ShapedQueryExpression translated, ref <>c__DisplayClass7_0 )

TargetInvocationException: Exception has been thrown by the target of an invocation.
System.RuntimeMethodHandle.InvokeMethod(object target, object[] arguments, Signature sig, bool constructor, bool wrapExceptions)

Raw Exception

System.InvalidOperationException: The LINQ expression 'DbSet<Candidate>()
    .Where(c => (int)c.candidateType == (int)__candidateType_0)
    .Where(c => EF.Property<IEnumerable<CustomProps>>(c, "CustomProperties")
        .AsQueryable()
        .Any(o => o.Key == __TypedProperty_1))' 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(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|7_0(ShapedQueryExpression translated, <>c__DisplayClass7_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   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.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   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 Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at Microsoft.AspNet.OData.Query.TruncatedCollection`1..ctor(IQueryable`1 source, Int32 pageSize, Boolean parameterize)
   at Microsoft.AspNet.OData.Query.ODataQueryOptions.LimitResults[T](IQueryable`1 queryable, Int32 limit, Boolean parameterize, Boolean& resultsLimited)

[KanishManuja-MS]

These seem to be integration issues with Cosmos DB in EFCore. I will sync up with them to discuss the path forward.

[smitpatel]

StartsWith translation has been added in 5.0 preview3 dotnet/efcore#19800
Any issue is being tracked here dotnet/efcore#20441

[mikepizzo]

Thanks @smitpatel!

Will close this issue on the OData side. In the meantime, @reddaiahnethi -- if you want to verify StartsWith in 5.0 preview3, you can let us know if you run into any issues.