Substring with single parameter throws InvalidOperationException when adding Where clause #20173

xiety · 2020-03-04T14:39:40Z

Steps to reproduce

Execute query:

var result = context.MyTable
    .Select(a => new { Name = a.Name.Substring(1) })
    .Where(a => a.Name == "test")
    .FirstOrDefault();

See the exception:

System.InvalidOperationException: The LINQ expression 'DbSet
.Where(m => m.Name.Substring(1) == "test")' 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.

Additinal information

Without Where the Select with Substring(1) works fine
Substring(1, 1) with two parameters works fine

Further technical details

EF Core version: 3.1.2
Database provider: Microsoft.EntityFrameworkCore.SqlServer

Full exception text

Unhandled exception. System.InvalidOperationException: The LINQ expression 'DbSet<MyTable>
    .Where(m => m.Name.Substring(1) == "test")' 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 System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.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 Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.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 Program.Main()

Source code

// @nuget: Microsoft.EntityFrameworkCore.SqlServer -Version 3.1.2

using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Data.SqlClient;

public class Program
{
    public static void Main()
    {
        using (var context = new MyDbContext())
        {
            var result = context.MyTable
                .Select(a => new { Name = a.Name.Substring(1) })
                .Where(a => a.Name == "test")
                .FirstOrDefault();
        }
    }

    public class MyDbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()));
            base.OnConfiguring(optionsBuilder);
        }

        public DbSet<MyTable> MyTable { get; set; }
    }

    public class MyTable
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
}

https://dotnetfiddle.net/QIWarC

smitpatel · 2020-03-04T15:37:28Z

In T-Sql, SUBSTRING requires the length argument as per https://docs.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-ver15

Hence we don't translate overload of substring with single parameter. You can rewrite the substring method call to have 2nd parameter like a.Name.Substring(1, a.Name.Length) to translate it to server.

xiety · 2020-03-04T16:19:30Z

If you don't translate it, why this code works?

var result = context.MyTable
	.Select(a => new { Name = a.Name.Substring(1) })
	.FirstOrDefault();

smitpatel · 2020-03-04T16:21:08Z

EF Core allows evaluating final projection on client side.

xiety · 2020-03-04T16:30:21Z

Thank you for the clarification. In a real project, queries come to me from another module, and I just add filters to them. And I break them with that. Can I disable client side evaluation for final projections, to prevent this confusion?

ajcvickers · 2020-03-06T21:27:48Z

@xiety We're not planning to allow disabling client-evaluation in the final projection. It is general not the case that composing on an existing translatable query will always result in a translatable query, regardless of client-evaluation.

xiety · 2020-03-06T21:52:12Z

I have a grid control with column filtering and it breaks some working queries only when user filters by specific column. I think such grid is a very common practice in business applications.

snappyfoo · 2020-03-19T12:27:18Z

In T-Sql, SUBSTRING requires the length argument as per https://docs.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-ver15

Hence we don't translate overload of substring with single parameter. You can rewrite the substring method call to have 2nd parameter like a.Name.Substring(1, a.Name.Length) to translate it to server.

Where can one find a list of all methods that are translatable and not translatable?

Could you not translate the Substring without the length by calculating the length in the SQL?

FWIW I came across this issue because there is an OData substring function which becomes the Substring method that can't be translated to SQL.

snappyfoo · 2020-03-19T22:35:06Z

expression.Substring(start) => SUBSTRING(expression, start, LEN(expression))

You could perhaps do something like LEN(expression)-(start-1) but as noted in the T-SQL documentation, the start + length can be greater than the length of expression and it'll behave the same. Not sure if there is a performance or semantic advantage in doing it one way or the other.

ajcvickers · 2020-03-20T18:59:16Z

Moving this to the backlog as something we could translate, especially since it comes from OData.

xiety added the type-bug label Mar 4, 2020

ajcvickers closed this Mar 6, 2020

ajcvickers added closed-by-design and removed type-bug labels Mar 6, 2020

ajcvickers added the customer-reported label Mar 10, 2020

ajcvickers reopened this Mar 19, 2020

snappyfoo mentioned this issue Mar 20, 2020

Problem with applying filter on IQueryable OData/WebApi#2084

Open

ajcvickers added this to the Backlog milestone Mar 20, 2020

ajcvickers added good first issue area-query and removed closed-by-design labels Mar 20, 2020

ajcvickers added the type-enhancement label Mar 20, 2020

Jun	JUL	Aug
	06
2019	2020	2021

dotnet / efcore

Substring with single parameter throws InvalidOperationException when adding Where clause #20173

Substring with single parameter throws InvalidOperationException when adding Where clause #20173

xiety commented Mar 4, 2020

smitpatel commented Mar 4, 2020

xiety commented Mar 4, 2020

smitpatel commented Mar 4, 2020

xiety commented Mar 4, 2020 •

edited

ajcvickers commented Mar 6, 2020

xiety commented Mar 6, 2020

snappyfoo commented Mar 19, 2020 •

edited

snappyfoo commented Mar 19, 2020 •

edited

ajcvickers commented Mar 20, 2020

dotnet / efcore

Join GitHub today

Substring with single parameter throws InvalidOperationException when adding Where clause #20173

Substring with single parameter throws InvalidOperationException when adding Where clause #20173

Comments

xiety commented Mar 4, 2020

Steps to reproduce

Additinal information

Further technical details

Full exception text

Source code

smitpatel commented Mar 4, 2020

xiety commented Mar 4, 2020

smitpatel commented Mar 4, 2020

xiety commented Mar 4, 2020 • edited

ajcvickers commented Mar 6, 2020

xiety commented Mar 6, 2020

snappyfoo commented Mar 19, 2020 • edited

snappyfoo commented Mar 19, 2020 • edited

ajcvickers commented Mar 20, 2020

xiety commented Mar 4, 2020 •

edited

snappyfoo commented Mar 19, 2020 •

edited

snappyfoo commented Mar 19, 2020 •

edited