Use of ILIKE with System.Linq.Expressions

See original GitHub issue

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!

Issue Analytics

  • State:closed
  • Created 5 years ago
  • Reactions:1
  • Comments:11 (5 by maintainers)

github_iconTop GitHub Comments

3reactions
rojicommented, May 21, 2019

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:

var blogs = ctx.Blogs.Where(b => EF.Property<string>(b, "Name").ToList();
2reactions
austindrenskicommented, Aug 27, 2018

@Luke-1988 Apologies, that should be:

Expression.Call(
    _ILike, 
    Expression.Constant(null, typeof(DbFunctions)), 
    property, 
    Expression.Constant(search, typeof(string)));
Read more comments on GitHub >

github_iconTop Results From Across the Web

How to create a System.Linq.Expressions. ...
@MarcGravell. It does work with LINQ-Entities but it's not ideal. The custom "Like" method is unknown and therefore cannot be translated to SQL....
Read more >
Dynamically Build LINQ Expressions | Developer for Life
This blog post demonstrates how to dynamically build a LINQ expression tree with multiple nested conditions, use the compiled query to ...
Read more >
Query expression basics (LINQ in C#)
A query is a set of instructions that describes what data to retrieve from a given data source (or sources) and what shape...
Read more >
Learn how to use Linq in C# and .NET
Linq enables you to query all sorts of data sources with a SQL like syntax. IMO it's the most powerful feature to ever...
Read more >
How to Use SQL LIKE Operator With LINQ in C# - Code Maze
In this article, we are going to learn how to use the SQL Like Operator with LINQ in C#. We'll see how to...
Read more >

github_iconTop Related Medium Post

No results found

github_iconTop Related StackOverflow Question

No results found

github_iconTroubleshoot Live Code

Lightrun enables developers to add logs, metrics and snapshots to live code - no restarts or redeploys required.
Start Free

github_iconTop Related Reddit Thread

No results found

github_iconTop Related Hackernoon Post

No results found

github_iconTop Related Tweet

No results found

github_iconTop Related Dev.to Post

No results found

github_iconTop Related Hashnode Post

No results found