Substring with single parameter throws InvalidOperationException when adding Where clause

[xiety]

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]

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]

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

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

[smitpatel]

EF Core allows evaluating final projection on client side.

[xiety]

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]

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

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.

[stevendarby]

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.

[stevendarby]

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]

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