Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

string.TrimStart not working in where #22924

Open
Sulexa opened this issue Oct 8, 2020 · 10 comments · May be fixed by #33715
Open

string.TrimStart not working in where #22924

Sulexa opened this issue Oct 8, 2020 · 10 comments · May be fixed by #33715
Labels
area-query area-sqlserver customer-reported good first issue This issue should be relatively straightforward to fix. type-enhancement
Milestone
Backlog

Comments

@Sulexa
Copy link

Sulexa commented Oct 8, 2020 •
edited

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
@Sulexa Sulexa changed the title TrimStart not working in where string.TrimStart not working in where Oct 8, 2020
@roji roji mentioned this issue Oct 8, 2020
Open
@roji
Copy link
Member

roji commented Oct 8, 2020 •
edited

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.

@ajcvickers ajcvickers reopened this Oct 16, 2022
@ajcvickers ajcvickers closed this as not planned Won't fix, can't repro, duplicate, stale Oct 16, 2022
@InspiringCode
Copy link

@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
Copy link
Member

roji commented Feb 14, 2024

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

@roji roji added the good first issue This issue should be relatively straightforward to fix. label Feb 14, 2024
@Matthew-Hiebing
Copy link

Matthew-Hiebing commented May 2, 2024 •
edited

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
Copy link
Member

roji commented May 3, 2024

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.

@roji roji reopened this May 3, 2024
@stevendarby
Copy link
Contributor

@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
Copy link
Member

roji commented May 5, 2024

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

@roji roji added this to the Backlog milestone May 5, 2024
abdielcs pushed a commit to abdielcs/efcore that referenced this issue May 14, 2024
TrimStart and TrimEnd with option char array implementation for SQL S…
4448f35 
…erver.

Fixes dotnet#22924
@abdielcs abdielcs linked a pull request May 14, 2024 that will close this issue
Open
1 task
@abdielcs
Copy link

abdielcs commented May 16, 2024 •
edited

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
Copy link
Member

roji commented May 16, 2024

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

@abdielcs
Copy link

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
area-query area-sqlserver customer-reported good first issue This issue should be relatively straightforward to fix. type-enhancement
Projects
None yet
Milestone
Backlog
Development

Successfully merging a pull request may close this issue.

TrimStart and TrimEnd with optional char array implementation for SQL Server abdielcs/efcore
8 participants
@ajcvickers @roji @abdielcs @AndriySvyryd @InspiringCode @Sulexa @stevendarby @Matthew-Hiebing