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

Question

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

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}%"));

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.

Source :- https://github.com/aspnet/EntityFrameworkCore/issues/9578

This is the solution for direct line query. https://github.com/aspnet/EntityFrameworkCore/issues/16195

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)

Answer 1

The "double cast" (string)(object)e.IntCol is a way to trick the C# compiler to "pass" int argument to a method expecting string parameter (like EF.Functions.Like). Of course if the method is actually called, you'll get invalid cast exception at runtime.

But the trick works because methods like this are never "called", but translated to SQL, and SqlServer EF Core provider removes such casts and allows you to use the SqlServer implicit data conversions. I'm using the same technique (although in opposite direction) in How can a JSON_VALUE be converted to a DateTime with EF Core 2.2? and Expression tree to SQL with EF Core.

Here is how that maps to Expression methods. Given Expression searchKeyExpression (the concrete Expression type doesn't matter), the important is the Type returned by the Expression.Type property. If it is string, you are fine, otherwise you need to apply (string)(object) casts to it, which is achieved with two Expression.Convert calls.

Something like this:

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);