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

c# ef-core-2.1 entity-framework-core expression-trees

Question

I can make an invoke expression using SQL functions, 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}%"));

The only thing I need to know is how to leverage the functionality for columns like integer or decimal column to use with similar functions. If I use the aforementioned phrase, I receive the following error. How can I use an expression with EF in the same way as a 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 for replication

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

According to what I can see, there is a choice for doing it in the Ef.Functions Like method in the example below.

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

but, how can I do this using member expressions.

Origin: https://github.com/aspnet/EntityFrameworkCore/issues/9578

The answer to the direct line inquiry is this. https://github.com/aspnet/EntityFrameworkCore/issues/16195

More technical information

Version of EF Core: (ASP.NET Core 2.1) Provider of databases: (e.g. Microsoft.EntityFrameworkCore.SqlServer) System of operation: IDE: (Visual Studio 2017, version 15.4)

1
1
6/22/2019 6:54:26 PM

Accepted Answer

"Double casting"(string)(object)e.IntCol is a technique to "get around" the C# compilerint a method requiring argumentstring parameter (such asEF.Functions.Like ). You will undoubtedly get an incorrect cast error at runtime if the function is in fact invoked.

The approach, however, works because these methods are never "called," but rather translated to SQL, and the SqlServer EF Core provider eliminates these casts and lets you utilize the implicit data conversions offered by SqlServer. In How does EF Core 2.2 translate a JSON VALUE into a DateTime? and EF Core's Expression Tree to SQL, I'm use the same strategy (but in the other direction).

How that translates toExpression given techniquesExpression searchKeyExpression (The cementExpression kind is irrelevant; what matters is theType the return of theExpression.Type property. When it isstring if not, you must apply. Otherwise, you are OK.(string)(object) it with two castings, which is accomplishedExpression.Convert calls.

Possibly 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);
4
12/6/2019 7:34:44 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow