Efcore: EF Core SQL Function LIKE Method with Linq Expression not working for Non String types

[akshayjoyinfo]

Issue

I am able to create an invoke Expression with SQL Functions like as shown below

var likeMethod = typeof(DbFunctionsExtensions).GetMethod("Like", new[] { typeof(DbFunctions), typeof(string), typeof(string) });

Expression.Call(null, likeMethod, Expression.Constant(EF.Functions), searchKeyExpression, Expression.Constant($"%{filter.Value}%"));

I just need to understand how can I use the functionality for columns like integer or decimal column to use with Like functions.
If I use the above expression I am getting below error. How can I use expression with ef like with non string datatype

Argument Exeption: argument exception in Expression of System.Int32  can not be used for parameter for type System.String of method Boolean Like (Ef.DBfuntions)

Steps to reproduce

```c#
var likeMethod = typeof(DbFunctionsExtensions).GetMethod("Like", new[] { typeof(DbFunctions), typeof(string), typeof(string) });

Expression.Call(null, likeMethod, Expression.Constant(EF.Functions), searchKeyExpression, Expression.Constant($"%{filter.Value}%"));

```

Further technical details

EF Core version: (ASP.NET Core 2.1)
Database Provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Operating system:
IDE: (e.g. Visual Studio 2017 15.4)

[akshayjoyinfo]

I just need to understand how can I use the functionality for columns like integer or decimal column to use with Like functions.

As I can see there is an option for to do it in Ef.Functions Like method in the below example

context.Set<MyEntity>().Where(e => EF.Functions.Like((string)(object)e.IntCol, "%1%"))

but how can I do this using Member Expressions.

var likeMethod = typeof(DbFunctionsExtensions).GetMethod("Like", new[] { typeof(DbFunctions), typeof(string), typeof(string) });

Expression.Call(null, likeMethod, Expression.Constant(EF.Functions), searchKeyExpression, Expression.Constant($"%{filter.Value}%"));

[akshayjoyinfo]

Found the answer on stackoverflow.

Expression matchExpression = searchKeyExpression;
if (matchExpression.Type != typeof(string))
{
    matchExpression = Expression.Convert(matchExpression, typeof(object));
    matchExpression = Expression.Convert(matchExpression, typeof(string));
}
var pattern = Expression.Constant($"%{filter.Value}%);
var callLike = Expression.Call(
    typeof(DbFunctionsExtensions), "Like", Type.EmptyTypes,
    Expression.Constant(EF.Functions), matchExpression, pattern)

Source :- https://stackoverflow.com/questions/56715091/ef-core-sql-function-like-method-with-linq-expression-not-working-for-non-string?noredirect=1#comment99994677_56715091

[nadeemy]

We were using the same fix as mentioned in the Stackoverflow answer in EF Core 2.2, but it now no longer works in EF Core 3.*.
The exception message is "Object reference not set to an instance of an object."
Stacktrace: at Microsoft.EntityFrameworkCore.Query.ExpressionExtensions.InferTypeMapping(SqlExpression[] expressions)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionFactory.ApplyTypeMappingOnLike(LikeExpression likeExpression)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionFactory.ApplyTypeMapping(SqlExpression sqlExpression, RelationalTypeMapping typeMapping)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionFactory.ApplyDefaultTypeMapping(SqlExpression sqlExpression)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionFactory.Like(SqlExpression match, SqlExpression pattern, SqlExpression escapeChar)
at Microsoft.EntityFrameworkCore.Query.Internal.LikeTranslator.Translate(SqlExpression instance, MethodInfo method, IReadOnlyList1 arguments) at Microsoft.EntityFrameworkCore.Query.RelationalMethodCallTranslatorProvider.<>c__DisplayClass4_0.<Translate>b__1(IMethodCallTranslator t) at System.Linq.Enumerable.SelectEnumerableIterator2.MoveNext()
at System.Linq.Enumerable.TryGetFirstTSource
at System.Linq.Enumerable.FirstOrDefaultTSource
at Microsoft.EntityFrameworkCore.Query.RelationalMethodCallTranslatorProvider.Translate(IModel model, SqlExpression instance, MethodInfo method, IReadOnlyList1 arguments) at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateLambdaExpression(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate) 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.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__DisplayClass12_01.b__0()
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCoreTFunc
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsyncTResult
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsyncTResult
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsyncTSource,TResult
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.CountAsyncTSource

[smitpatel]

@nadeemy - #17389

[nadeemy]

@smitpatel I continue to see the same issue in Member Expressions even after upgrading to EntityFrameworkCore 3.1

[smitpatel]

@nadeemy - Please file a new issue with full repro project which is showing error.

[nadeemy]

@smitpatel sorry, I couldn't get back to you on time. I spent more time on this today and found that I was only updating the Microsoft.EntityFrameworkCore 3.0.0 to 3.1.0. I had to also update the Microsoft.EntityFrameworkCore.SqlServer from 3.0.0 to 3.1.0 in order to get it working.