string.TrimStart not working in where

[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"...

[Matthew-Hiebing]

Was this ever resolved? I'm also interested in using TrimStart() with a characters parameters and having it translate to LTRIM(characters) in SQL with Entity Framework.

[roji]

Look like I forgot to actually reopen this... This should be a pretty easy issue to implement, if someone wants to take a stab at it.

[stevendarby]

@roji

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"...

This might regress use of TrimStart(char) in final projection

[roji]

@stevendarby that's true.

Since we introduced the SQL Server compatibility level config option to EF Core 8.0, it should indeed be quite easy to check that when deciding whether to translate, and refraining from translating for older targeted versions.

[abdielcs]

I have been working in this issue. I already figured out how to verify the compatibility level in the Translator (I hope it's okay) but since the test are failing maybe I should check the compatibility level in the test too. Is that so? If yes, I don't see any other test doing that check. How I can manage that?

[roji]

@abdielcs you can annotate the test method with [SqlServerCondition], passing SupportsFunctions2022 - take a look at other tests doing this.

[abdielcs]

Apparently I was able to break some test in EFCore.Sqlite.FunctionalTests working only in EFCore.SqlServer and EFCore.SqlServer.FunctionalTests projects in PR #33715 . Does that make any sense? If so, I can't find a descriptive log about it. Sorry for being such a newbie.