Problem with dynamic query building for Entity Framework Core 3.1.6

Question

I am building LINQ queries dynamically. I use EF Core 3.1.6 (for SQL Server).

I create an IQueryable with a where() clause using a predicate.

EF Core is able to translate the following predicate expression and the query works as expected:

{p => ((p.Address != null) AndAlso p.Address.Contains(Convert("6152 Fames Ro", String)))}

But EF Core is not able to translate the following predicate expression:

{p => (((p.FirstName != null) AndAlso p.FirstName.Contains(Convert("fred", String))) OrElse ((p.MiddleName != null) AndAlso p.MiddleName.Contains(Convert("fred", String))))}

It throws the following exception:

The LINQ expression 'DbSet\r\n .Where(p => p.FirstName != null && p.FirstName.Contains("fred") || p.MiddleName != null && p.MiddleName.Contains("fred"))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

I looked into the following questions:

• EF Linq Error after change from dotnet Core 2.2.6 to 3.0.0
• https://entityframeworkcore.com/knowledge-base/58166970/migrating-from-ef-core-2-to-ef-core-3

As suggested in the exception message, I tried doing AsEnumerable(), ToList() etc on the final IQueryable and that did not work. This I guess will rule out the client evaluation problem.

I am sure that I am doing something wrong; not sure what.

Can someone help? I can give more information if required.

Answer 1

I found the fix for my problem.

The predicate should be constructed as follows:

{p => (((p.FirstName != null) AndAlso p.FirstName.Contains(Convert("fred", String))) OrElse Invoke(p => ((p.MiddleName != null) AndAlso p.MiddleName.Contains(Convert("fred", String))), p))}

The above predicate works fine.

To fix this, I did 2 changes:

1. Converted the following extension method from this:

   public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
           Expression<Func<T, bool>> expr2)
       {
           return Expression.Lambda<Func<T, bool>>
               (Expression.OrElse(expr1.Body, expr2.Body), expr1.Parameters);
       }
   

   to this:

   public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
           Expression<Func<T, bool>> expr2)
       {
           var invokedExpr = Expression.Invoke(expr2, expr1.Parameters);
           return Expression.Lambda<Func<T, bool>>
               (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
       }
   

2. Changed the way, I constructed the constant expression from this:

   var member = Expression.Property(parameter, propertyInfo);
               var filterValueConstant = Expression.Convert(Expression.Constant(convertedValue), propertyType);
   

   to this:

   var underlyingTypeConstExpr = Expression.Constant(convertedValue);
               var filterValueConstant = Expression.Convert(underlyingTypeConstExpr, propertyType);
   

This fixed my problem. But I still don't know why the old predicate did not work.