C# – Howto use predicates in LINQ to Entities for Entity Framework objects

centity-frameworklinq-to-entitieslinqkitpredicate

I'm using LINQ to Entities for Entity Framework objects in my Data Access Layer.

My goal is to filter as much as I can from the database, without applying filtering logic to in-memory results.

For that purpose Business Logic Layer passes a predicate to Data Access Layer.

I mean

Func<MyEntity, bool>

So, if I use this predicate directly, like

public IQueryable<MyEntity> GetAllMatchedEntities(Func<MyEntity, Boolean> isMatched)
{
    return qry = _Context.MyEntities.Where(x => isMatched(x));
}

I'm getting the exception

[System.NotSupportedException] — {"The LINQ expression node type
'Invoke' is not supported in LINQ to Entities."}

Solution in that question suggests to use AsExpandable() method from LINQKit library.

But again, using

public IQueryable<MyEntity> GetAllMatchedEntities(Func<MyEntity, Boolean> isMatched)
{
    return qry = _Context.MyEntities.AsExpandable().Where(x => isMatched(x));
}

I'm getting the exception

Unable to cast object of type
'System.Linq.Expressions.FieldExpression' to type
'System.Linq.Expressions.LambdaExpression'

Is there way to use predicate in LINQ to Entities query for Entity Framework objects, so that it is correctly transformed it into a SQL statement.

Thank you.

Best Answer

You don't need LinqKit to do this. Just remember to use 

Expression<Func<MyEntity, bool>>

instead of

Func<MyEntity, bool>

Something like this:

public IQueryable<MyEntity> GetAllMatchedEntities(Expression<Func<MyEntity, Boolean>> predicate)
{
    return _Context.MyEntities.Where(predicate);
}

You have to use Expression because Linq to Entities needs to translate your lambda to SQL.

When you use Func your lambda is compiled to IL but when using Expression it is an expression tree that Linq to Entities can transverse and convert.

This works with expressions that Linq to Entities understands.

If it keeps failing then your expression does something that Linq to Entities can not translate to SQL. In that case I don't think LinqKit will help.

Edit:

There is no conversion needed. Just define the method GetAllMatchedEntities with an Expression parameter and use it in the same way you would with a Func parameter. The compiler does the rest.

There are three ways you can use GetAllMatchedEntities.

1) With an inline lambda expression:

this.GetAllMatchedEntities(x => x.Age > 18)

2) Define your Expression as a field (can be a variable also)

private readonly Expression<Func<MyEntity, bool>> IsMatch = x => x.Age > 18;
...then use it
this.GetAllMatchedEntities(IsMatch)

3) You can create your expression manually. The downsize is more code and you miss the compile-time checks.

public Expression<Func<MyEntity, bool>>  IsMatchedExpression()
{
    var parameterExpression = Expression.Parameter(typeof (MyEntity));
    var propertyOrField = Expression.PropertyOrField(parameterExpression, "Age");
    var binaryExpression = Expression.GreaterThan(propertyOrField, Expression.Constant(18));
    return Expression.Lambda<Func<MyEntity, bool>>(binaryExpression, parameterExpression);
}
Related Topic