Efcore.pg: Use of ILIKE with System.Linq.Expressions

[Luke-1988]

I am building a dynamic LINQ based on annotation - [Searchable] or [Orderable] properties in models - and I am trying to find out how to perform ILIKE using Expressions and Lambda.

Simple working code:

var query = dbContext.countries
                      .Where(w => (w.name != null && 
                               EF.Functions.ILike(w.name, $"{search}%")
                       );

Query is translated into PostgreSQL's ILIKE, so far so good.

However, I query list of [Searchable] properties using Reflextion on class Country then foreach this properties with this code:

// this is working, however, is translated into LIKE, which is not enough
 expr = Expression.Call(property, 
                nameof(string.Contains), 
                new Type[] { }, 
                Expression.Constant(search));

// not working. See exception below
var _ILike = typeof(NpgsqlDbFunctionsExtensions).GetMethod("ILike", 
                                BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic,
                                null, 
                                new[] { 
                                    typeof(Microsoft.EntityFrameworkCore.DbFunctions),  
                                    typeof(string),  
                                    typeof(string) 
                                },
                                null
                                );

                        _expr = Expression.Call(_ILike, property, Expression.Constant(search, typeof(string)));

Exception:
System.ArgumentException: Incorrect number of arguments supplied for call to method 'Boolean ILike(Microsoft.EntityFrameworkCore.DbFunctions, System.String, System.String)'
Parameter name: method

 // also not working, see exception below:
 expr = new Npgsql.EntityFrameworkCore.PostgreSQL.Query.Expressions.Internal.ILikeExpression(property, Expression.Constant(search_query));

Exception:
System.ArgumentException: must be reducible node
Stack Trace:
    at System.Linq.Expressions.Expression.ReduceAndCheck()
   at System.Linq.Expressions.Expression.ReduceExtensions()
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExtensionExpression(Expression expr, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteLogicalBinaryExpression(Expression expr, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.Rewrite[T](Expression`1 lambda)
   at System.Linq.Expressions.Expression`1.Accept(StackSpiller spiller)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteLambdaExpression(Expression expr)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.AddArguments(IArgumentProvider expressions)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.Rewrite[T](Expression`1 lambda)
   at System.Linq.Expressions.Expression`1.Accept(StackSpiller spiller)
   at System.Linq.Expressions.Compiler.LambdaCompiler.Compile(LambdaExpression lambda)
   at System.Linq.Expressions.Expression`1.Compile(Boolean preferInterpretation)
   at System.Linq.Expressions.Expression`1.Compile()
   at System.Linq.EnumerableQuery`1.GetEnumerator()
   at System.Linq.EnumerableQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)

Roji, do you have any recommendation how to use ILikeExpression, or, how to use ILIKE with dynamic LINQ?

Thanks!

[Luke-1988]

Workaround:

var _regex = typeof(System.Text.RegularExpressions.Regex).GetMethod("IsMatch", 
                                BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic,
                                null, 
                                new[] { 
                                    typeof(string),  
                                    typeof(string),
                                    typeof(System.Text.RegularExpressions.RegexOptions)
                                },
                                null
                                );

                        expr = Expression.Call(_regex, property, Expression.Constant(search, typeof(string)), Expression.Constant(System.Text.RegularExpressions.RegexOptions.IgnoreCase));

[austindrenski]

@Luke-1988 It looks like you've omitted a parameter, specifically a null representing the DbFunctions _instance_ on which that extension method is defined.

Try this:

_expr = Expression.Call(_ILike, null, property, Expression.Constant(search, typeof(string)));

[Luke-1988]

Austin, thanks for your effort, but 'null' did not help. I am getting this exception:

System.ArgumentNullException: Value cannot be null.
Parameter name: arg0
Stack Trace:
at System.Linq.Expressions.Expression.Call(MethodInfo method, Expression arg0, Expression arg1, Expression arg2)

However, similar call for Regex (see comment above) works.

[austindrenski]

@Luke-1988 Apologies, that should be:

c# Expression.Call( _ILike, Expression.Constant(null, typeof(DbFunctions)), property, Expression.Constant(search, typeof(string)));

[Luke-1988]

Thank you sir, works like a charm :)

[barrettg]

Sorry to hijack this closed issue, but I am having the same difficulties and I think this solution is my answer. However, I do not understand how to use the Expression.Call() function. I see that it is getting set to the variable _expr, but what do I do with that? Some sort of dbContext.countries.Where(q => _expr(q)) or something?

[austindrenski]

@barrettg Apologies for the delayed response, I missed the initial notification last month.

If you're still having issues with this, would you mind opening a new issue with a fuller description of what you're trying to accomplish?

[barrettg]

@austindrenski
I was unfamiliar with how to call an expression in this way, but did get some help from Stack Overflow. Here is how I am currently using it and welcome any suggestions:

            string search = "search string";
            var param = Expression.Parameter(typeof(Contractor), "c");
            var property = Expression.Property(param, "Name");
            var expr = Expression.Call(
                           typeof(NpgsqlDbFunctionsExtensions),
                           nameof(NpgsqlDbFunctionsExtensions.ILike),
                           Type.EmptyTypes,
                           Expression.Property(null, typeof(EF), nameof(EF.Functions)),
                           property,
                           Expression.Constant($"%{search}%"));

            return _db.Contractors
                .Where(Expression.Lambda<Func<Contractor, Boolean>>(expr, param))
                .ToListAsync();

[roji]

@barrettg (and others) I'm curious, is there any specific reason you're constructing an expression tree yourself rather than just writing LINQ expressions? The above seems like it could be rewritten to something like:

c# string search = "search string"; return _db.Contractors .Where(c => EF.Functions.ILike(c.Name, someSearchPattern)) .ToListAsync();

[barrettg]

Yes - I wanted to be able to dynamically specify the column names that I am searching. I've setup a generic filter function and it takes a list of column names and then I build the expression for each one. That way the client (React in my instance) can control if we are searching a Name or Description for instance. If there is a more simple way of specifying the column name as a string other than building the expression tree, I'd love to know...

[roji]

You can use EF.Property() to dynamically access a property within an EF Core LINQ Query. This is mainly meant for shadow properties but can be used for dynamic scenarios as well:

c# var blogs = ctx.Blogs.Where(b => EF.Property<string>(b, "Name").ToList();