Efcore: Support for translating ToString() to SQL

[shargon]

Steps to reproduce

Convert int to String only works in local (by Linq)

[User].Id.ToString().Contains("1")

The issue

Who i can convert int to string (by the server)?

Exception message:
info: Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory[1]
      Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [User].[Id], [User].[Name], [User].[Email]
      FROM [Users] AS [User]
warn: Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[8]
      The LINQ expression '(([User].Id.ToString().Contains("1") OrElse [User].Name.Contains("1")) OrElse [User].Email.Contains("1"))' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.

I use this method

public Expression<Func<T, Boolean>> WhereForSearch<T>(string[] words)
        {
            Expression general = null;
            if (words == null || words.Length == 0)
            {
                general = Expression.Equal(Expression.Constant(1), Expression.Constant(1));
                return Expression.Lambda<Func<T, Boolean>>(general, new ParameterExpression[] { ParameterExpression });
            }

            Dictionary<Type, object>[] vals = new Dictionary<Type, object>[words.Length];

            for (int x = words.Length - 1; x >= 0; x--)
                vals[x] = ParseHelper.ConvertFromString(words[x], Listable.Types);

            for (int x = 0, m = words.Length; x < m; x++)
            {
                Dictionary<Type, object> val = vals[x];
                Expression pe = null;
                foreach (CacheLinqExpresion.Item p in Searchable.Properties)
                {
                    object v1;
                    // Check same type
                    if (!val.TryGetValue(p.Type, out v1)) continue;

                    // Convert to string
                    Expression ex = p.Value;
                    if (p.Type != ParseHelper.TypeString)
                    {
                        ///  x.ToString().Contains(v1)
                        v1 = words[x];
                        ex = Expression.Call(ex, CrudCacheProvider.MethodToString);
                        ex = Expression.Call(ex, CrudCacheProvider.MethodContains, Expression.Constant(v1));
                        //ex = Expression.Equal(ex, Expression.Constant(v1));
                    }
                    else
                    {
                        ex = Expression.Call(ex, CrudCacheProvider.MethodContains, Expression.Constant(v1));
                    }
                    pe = pe == null ? ex : Expression.OrElse(pe, ex);
                }

                if (pe == null) continue;

                general = (general == null) ? pe : Expression.AndAlso(general, pe);
            }

            return Expression.Lambda<Func<T, Boolean>>(general, new ParameterExpression[] { ParameterExpression });
        }

Further technical details

EF Core version: (found in project.json or packages.config)
"Microsoft.AspNetCore.Identity.EntityFrameworkCore": "1.0.0",
"Microsoft.EntityFrameworkCore": "1.0.0",

[zpbappi]

@divega Do you want to support ToString() with no param in general? Or, ToString(<string>)? I think, the latter syntax has very dynamic behavior (thinking about DateTime, decimal, etc.).

In case of ToString(), it has some complexities for data types like DateTime. Someone may want to do: dbContext.Students.Where(s => s.DOB.ToString() == "2016-01-01"), while someone may want to do: dbContext.Students.Where(s => s.DOB.ToString() == "1/1/2016"). Do you guys have a plan to formalize the behavior for ToString() for the supported data types? I am sure you can think of other case of other data types as well. Oh, I didn't bring _current culture_ into play yet...

Now, in the case of ToString(<string>) variant, it gets even more complex. Think about all the standard and standard and custom formatting strings you can have for data types like DateTime, decimal, etc.

A scope definition with the supported data types and expected behavior would be extremely helpful.

[airomero]

Hi, is this the same problem? When i use:

EmpresasContext e = new EmpresasContext(_config);
var res = e.Socios.Where(s => s.CSOCIO.ToString().Contains("130"));

I get a select statement with no filters. Is this ok? Why is this happening?

Property CSOCIO is an int

[zpbappi]

looks like the exact same issue. this is happening because EF is not yet able to translate the ToString method to Sql. You can read the comments above to get some more info.

[los93sol]

I actually have a use case that is not accounted for, I need to be able to pass in the format of a DateTime object instead of using the default and when using that overload in my Expression I get the familiar will be evaluated locally warning

[ajcvickers]

Re-opened to get clarity on what was fixed and in which release.

[smitpatel]

Parameter-less ToString() method are only supported ones at present since we can match with default behavior in database.

For ToString() with parameters see https://github.com/aspnet/EntityFrameworkCore/issues/7364

[ajcvickers]

Thanks @smitpatel!

[himanshukodwani]

Translating ToString(), Contains(), StartsWith(), EndsWith(), ToLower() etc. functions would be a very useful feature.