SQL Server: Translate string.TrimStart/End overloads accepting characters to trim

[Sulexa]

string.TrimStart not working in where

Using string.TrimStart inside a where close not working
(But it's work in a select)

Code

var testData = "1";
var testRequest = await this._context.Table
       .Where(q => q.Code.TrimStart('F') == testData)
       .FirstOrDefaultAsync();

Stack traces

System.InvalidOperationException: The LINQ expression 'DbSet<Table>
    .Where(f => f.Code.TrimStart(F) == __testData_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|8_0(ShapedQueryExpression translated, <>c__DisplayClass8_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   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__DisplayClass12_0`1.<ExecuteAsync>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.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.FirstOrDefaultAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

Provider and version information

EF Core version: 3.1.8
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 3.1
Operating system: Windows 10
IDE: Visual Studio 2019 16.7.3

[roji]

AFAIK there isn't a function we can translate this to on SQL Server - LTRIM only works on whitespace (no characters argument). It may be possible to hack something very complicated, but which would be bad for perf.

[InspiringCode]

@roji SQL Server 2022 finally seems to support LTRIM with a list of characters (see docs). Would it be an option to translate TrimStart to this version if characters are specified? It would fail on old versions, but might still be better than throwing an NotSupportedException in any case?

[roji]

@InspiringCode thanks for pointing to the new version of LTRIM - I'm reopening this issue.

I'm not sure it makes sense to have special logic distinguishing the SQL Server versions in EF for this case - just generating the SQL may be enough; it would fail on older versions, but that doesn't seem really worse than EF throwing "can't translate"...