CODEWITHSUNDEEP
c#entity-frameworklinqpredicatebuilder
Humayun Khan
Humayun Khan

Reputation: 63

Problem with dynamic query building for Entity Framework Core 3.1.6

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:

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.

Upvotes: 1

Views: 937

Answers (1)

Humayun Khan
Humayun Khan

Reputation: 63

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.

Upvotes: 1

Related Questions