Entity Framework Code First 4.3 / LINQKit predicate for related table

Question

I am using Entity Framework 4.3.1 with a Code First approach. Also, I am using LinqKit so as to use the PredicateBuilder.

If I have tables like so:

Location, TimeZone (Many:1)

..and I wish to have something like so:

Expression<Func<TimeZone, bool>> pred = PredicateBuilder.True<TimeZone>();
pred = pred.And(tz => tz.TimeZoneCode == "EST");

List<Location> locations = context.Locations
    .AsExpandable().Where(pred)
    .Select(loc => loc).ToList();

This does not work, because the predicate is built to accept a TimeZone, but the Where() method receives a Location.

I can rewrite the predicate like so, but I do not want to, because I want to have a predicate factory that creates predicates for specific types (I do not want to use the Navigator properties in this manner):

Expression<Func<Location, bool>> pred = PredicateBuilder.True<Location>();
pred = pred.And(loc => loc.TimeZone.TimeZoneCode == "EST");

What syntax could I use (if any) to use the predicate as constructed in the first example, where it takes a TimeZone, rather than have it take a Location and walk the tree via the navigation properties (as this is less reusable). It would be nice if there was a way to leverage the knowledge that EF has about the navigation properties in the first place, and be able to use a predicate scoped to the type of the navigation property.

Answer 1

After about a week's worth of struggling, I found out that you can in fact do this. The steps are:

1. Define a Predicate that is the query for your inner property (subPredicate)
2. Invoke that subPredicate from within another Predicate (predicate), against the property of the parent object.
3. Expand your predicate when using it in your Where clause.

Here's the revised code for your example:

var subPredicate = PredicateBuilder.True<TimeZone>();
subPredicate = subPredicate.And(tz => tz.TimeZoneCode == "EST");

var predicate = PredicateBuilder.True<Location>();
predicate = predicate.And(l => subPredicate.Invoke(l.TimeZone));

List<Location> locations = context.Locations
    .AsExpandable().Where(pred.Expand())
    .Select(loc => loc).ToList();

Answer 2

Just to update this: it turns out that the intent of these kinds of predicates is to filter the primary entity. The mental concept is: decide which entities you would like to return, and return them. EF is apparently not designed for this kind of deep predicate application on child entities.

One person (I don't remember where) made a great point: If the children were pre-loaded, you would not expect a partially-loaded collection. Thus, it would not make sense to have an Invoice entity, for example, but only half of the Invoice Lines.

What I wanted to achieve was more along the lines of EXISTS() or IN(), where you could say "Give me all Invoices where there are Invoice Lines for Product Types 'Nuts' and 'Bolts'". This is feasible, but you may have to apply LINQ or compose your own object. The intent of EF out of the box is to hand you the Invoice, and then you may lazily or eagerly load the Invoice Lines, but not using them as a filter straight from the database.

There are some constructs that I saw to accomplish something close, but the syntax gets very unmanageable very quickly.

Don't fight city hall.