Meta-Me

Tip 37 – How to do a Conditional Include

Problem

Someone asked how to do a Conditional Include a couple of days ago on StackOverflow.

They wanted to query for some entity (lets say Movies) and eager load some related items (lets say Reviews) but only if the reviews match some criteria (i.e. Review.Stars == 5).

Unfortunately though this isn’t strictly supported by EF’s eager loading, i.e. ObjectQuery<Movie>.Include(…) because Include(..) is all or nothing.

Solution

But there is a workaround.

Here is an example scenario to make this ‘real’:

public class Movie
{
    public int ID {get;set;}
    public string Name {get;set;}
    public string Genre {get;set;}
    public List<Review> Reviews {get;set;}
}

public class Review
{
    public int ID {get;set;}
    public int Stars {get;set;} 

    public string Summary {get;set;}
    public Movie Movie {get;set;}
    public User User {get;set;}
}

Imagine if you want to retrieve all ‘Horror’ movies and all of their 5 star reviews.

You can do so like this:

var dbquery =
   from movie in ctx.Movies
   where movie.Genre == “Horror”
   select new {
      movie, 
      reviews = from review in movie.Reviews
                where review.Stars == 5
                select review
   };

var movies = dbquery
   .AsEnumerable()
   .Select(m => m.movie);

Now why does this work?

Well the first query creates a new instance of an anonymous type containing each Horror movie and just it’s 5 star ratings.

The second query runs in memory using LINQ to Objects thanks to the AsEnumerable() call, and simply ‘unwraps’ the movie from its anonymous type wrapper.

And interestingly each movie will also have just it’s five star reviews loaded!

So this code:

foreach(var movie in movies)
{
    foreach(var review in movie.Reviews)
        Assert(review.Rating == 5);
}

Will pass with no Asserts.

This works because EF implements something called relationship fix-up.

Relationship fix-up ensures that related objects are automatically linked when the second entity enters the ObjectContext.

And because we are loading both the Movie and a filtered list of it’s Reviews, both enter the ObjectContext, and EF makes sure they are automatically linked, which means the matching Reviews are present in the appropriate Movie.Reviews collection.

i.e. Conditional Include.

There are number of different twists on this theme:

• Issue two separate queries: one for the Movies, one for the Reviews, and let relationship fix-up do the rest.
• Issue a select many type query as shown here.
• Sorting relationship - see Tip1

Once you understand how relationship fix-up works you can really use it to your advantage.

Enjoy.