Use Expression or Predicate for filtering navigation properties?

Question

Say I have a repository which allows my to pass custom expressions to filter on the main object, along with another custom expression that allows me to filter the related objects.

DbContext entities expect an Expression<Func<>> to filter.

One-to-many Navigation properties are generally ICollections<>, and can only have the .Include clause filtered with Func<> predicates.

It seems strange to me that main entity requires an Expression, but the navigation property can only be filtered with a Func<>.

For example, this won't compile, specifically .Include(c => c.Orders.Where(orderCriteria)), because orderCriteria is an expression:

public class Customer { 
    
    public int Id { get; set; }
    
    public virtual ICollection<Order> Orders { get; set;}
}

public class Order { 
    
    public int Id { get; set; }
    
    public int CustomerId { get; set;}
    public Customer Customer { get; set; }
}
    
public class CustomerRepository
{

    private DbContext _dbContext = new DbContext();

    List<Customer> SearchCustomers(
        Expression<Func<Customer, bool>> customerCriteria, 
        Expression<Func<Order, bool>> orderCriteria) {
        
        return _dbContext.Customers.Where(customerCriteria)
            .Include(c => c.Orders.Where(orderCriteria)).ToList(); // won't compile
        
    }
}

Should I just call .Include(c => c.Orders.Where(orderCriteria.Compile())) ? Or just pass orderCriteria as a Func<Order,bool> rather than as an expression?

And will the query on the Order entities still be performed at the database level in SQL?

Answer 1

None of these options will work. Notice that Include itself accepts an Expression. EF will analyze this expression, including part inside Where. For example consider this:

.Include(x => x.Orders.Where(o => o.Amount >= 1000m))

Expression inside Include can be analyzed completely, including logic inside Where call. It's not relevant that Where accepts Func and not expression because this Where call is itself part of expression.

Now if you have this:

.Include(c => c.Orders.Where(orderCriteria.Compile())

Now we also have expression inside Include. However, this expression contains call to Compile which is unknown to EF and cannot be translated to SQL, so it will fail. In theory, EF could extract value from this expression, because the necessary info is available there inside orderCriteria, but it doesn't do that.

You could try something like:

var compiledCriteria = orderCriteria.Compile();
.Include(c => c.Orders.Where(compiledCriteria)

This will not work - now necessary info is not available at all in expression tree, so there is no way to extract original meaning of Amount >= 1000 from this.

To solve this you can use LinqKit or similar libraries. Note how I said before that with this approach:

.Include(c => c.Orders.Where(orderCriteria.Compile())

necessary information is in theory there and can be extracted, but EF doesn't do this. Well, LinqKit extracts this information and basically rewrites your expression in "expected" form. With this library you need to use special marker method AsExpandable:

List<Customer> SearchCustomers(
    Expression<Func<Customer, bool>> customerCriteria, 
    Expression<Func<Order, bool>> orderCriteria) {
    
    return _dbContext.AsExpandable().Customers.Where(customerCriteria)
        .Include(c => c.Orders.Where(orderCriteria.Compile())).ToList(); // won't compile
    
}

Using this AsExpandable method will allow LinqKit to analyze the following query and replace the usage of Expression.Compile with the contents of actual expression to make EF happy.

If you don't want to take dependency on third party library - it's open source so you can check how they do it (it's not too complicated), and do it yourself.