Translate ToString() on a string column

[shvmgpt116]

I am running a sample test in SqlServer where it is using ToString() on a string column (COL2) in the LINQ. The test is giving following error when trying to generate SELECT query from the LINQ.

Unhandled exception. System.InvalidOperationException: The LINQ expression 'DbSet .Where(t => t.COL2.ToString().Contains(__url_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().

Not using ToString() for string column works fine. Using any other type (for ex:- int) for COL2 with ToString() also works fine. Using ToString() for string type only gives the issue. I am not sure if this is expected. It seems a basic scenario to support. Please could anyone confirm?

Here is the sample test-

using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Console;
using Microsoft.Extensions.Logging.Debug;

namespace ConsoleApp66
{
  class Program
  {
    static void Main(string[] args)
    {
      Console.WriteLine("Hello World!");
      using (var db = new MyContext())
      {
        db.Database.EnsureDeleted();
        db.Database.EnsureCreated();
        string name = "abc";
        TAB1 tab1Obj = new TAB1 { COL2 = name };
        db.TAB1.Add(tab1Obj);
        db.SaveChanges();
        var result = from b in db.TAB1
                    where b.COL2.ToString().Contains(name)
                    select b;
        foreach (TAB1 item in result)
        {
          Console.WriteLine(item.COL2);
        }
      }
    }
  }
  public class TAB1
  {
    public int COL1 { get; set; }
    public string COL2 { get; set; }
  }
  class MyContext : DbContext
  {
    public DbSet<TAB1> TAB1 { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
      optionsBuilder.UseSqlServer(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=MySqlServerDB1;Integrated Security=True").UseLoggerFactory(_myLoggerFactory);
    }
    public static readonly LoggerFactory _myLoggerFactory = new LoggerFactory(new[] { new Microsoft.Extensions.Logging.Debug.DebugLoggerProvider() });
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
      modelBuilder.Entity<TAB1>(e =>
      {
        e.HasKey(p => p.COL1);
      });
    }
  }
}

Steps to reproduce

Simply run the above-provided test case-

Stacktrace

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.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 Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at ConsoleApp66.Program.Main(String[] args) in C:\Users\shivamg\source\repos\ConsoleApp66\ConsoleApp66\Program.cs:line 28

Further technical details

EF Core version: Microsoft.EntityFrameworkCore 3.1.3 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET Core 3.0 Operating system: Windows 10 IDE: Visual Studio 2019 16.4.2

[smitpatel]

Yes, it is not supported (yet). Though I am trying to understand what query is doing exactly. Why are you calling need to call ToString on a string column?

[shvmgpt116]

@smitpatel thanks for the update. There is probably no need to call ToString(). But calling ToString() on a string column should cause no harm. But in this case, it returns an error.

Could the issue be because there is no mapping available in the _typeMapping dictionary for string type in "SqlServerObjectToStringTranslator" class. https://github.com/dotnet/efcore/blob/release/3.1/src/EFCore.SqlServer/Query/Internal/SqlServerObjectToStringTranslator.cs

And would adding a mapping in this class resolve the issue?

If yes, what shall it map to? Shall it map to VARCHAR or NVARCHAR.

[smitpatel]

It shouldn't be added to dictionary. Rather if ToString is called on a string column then the string column should be returned.

[randalltomes]

I am simply calling .ToString() on an Int and it errors with Framework version 6.44. I rolled back to version 6.20 and no issues. I have been do this for several years with previous versions so this is some new problem introduced after 6.20. There is a valid reason to use .ToString() so text searches of multiple concatenated columns can be performed on that database server using SQL rather than pulling all your data down first to perform searches.