Cosmos: String.ToLower() cannot be translated to SQL

[r3core]

Based on the Cosmos DB LINQ to SQL documentation, I had assumed string functions ToLower and ToUpper were supported during query translation. However the query throws an InvalidOperationException.

Steps to reproduce

To ensure this wasn't an issue with my work project's codebase, I cloned the Cosmos Sample from the Cosmos DB Provider's Get Started and was able to reproduce the issue.

Code used: https://github.com/dotnet/EntityFramework.Docs/tree/master/samples/core/Cosmos

Added a new property named Country to Order in Order.cs

{
    #region Order
    public class Order
    {
        public int Id { get; set; }
        public int? TrackingNumber { get; set; }
        public string PartitionKey { get; set; }
        public string Country { get; set; }
        public StreetAddress ShippingAddress { get; set; }
    }
    #endregion
}

Querying (After saving data)

var countryName = "aMericA";
var countryMatchedOrders = context.Orders.Where(o => o.Country.ToLower() == countryName.ToLower()).ToList();

Expected

SELECT * FROM c
WHERE lower(c.Country) = lower('aMericA')

Actual

System.InvalidOperation

The LINQ expression 'DbSet<Order>()
    .Where(o => o.Country.ToLower() == __ToLower_0)' 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 System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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 System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
   at Cosmos.ModelBuilding.Sample.<Run>d__0.MoveNext() in C:\Users\RavinduN\Downloads\Cosmos\ModelBuilding\Sample.cs:line 94
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Cosmos.Program.<Main>d__0.MoveNext() 

Further technical details

EF Core version: 3.1.3
Database provider: Microsoft.EntityFrameworkCore.Cosmos
Target framework: .NET Core 3.1
Operating system: Windows 10 Enterprise 1909
IDE: Visual Studio 16.5.4

[AndriySvyryd]

Duplicate of #16143

[Marusyk]

There is PR #24203 with implementation