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.

Comments

• Anonymous
  October 14, 2009
  Thanks Alex!The only change I would do isvar movies = dbquery  .AsEnumerable().Where(m => m.Reviews.Count() >1)  .Select(m => m.movie);to avoid empty entries.
• Anonymous
  October 18, 2009
  Hi Alex,it's similar approach I used in http://blog.cincura.net/229660-load-with-filtering-or-limiting/ .
• Anonymous
  November 03, 2009
  Alex,I have done the same with Model First driven but I am getting a wrong count on the orders.Through profiler, I get two queries a filtered one and one for only orders in Console.Writeline.Please help. Below is the codeBlackDiamondContainer bdc = new BlackDiamondContainer();           var dbquery =                  from c in bdc.Customers                  select new                  {                       customer = c,                      ListofOrders = from order in c.Orders                               where order.Total == 120                               select order                  };           var Customers = dbquery              .AsEnumerable()              .Select(m => m.customer);           foreach (Customer cust in Customers)           {               Console.WriteLine(cust.Orders.Count);           }
• Anonymous
  December 13, 2009
  Hi,You said: "Issue two separate queries: one for the Movies, one for the Reviews"Does it mean that Reviews should "include" Movie objects as well to make it work?Thank you
• Anonymous
  December 14, 2009
  @Mark,No when you issue two queries, there should be no need for any includes, because the EF automatically links related entities in memory. So if you issue one query for a movie, and one of the reviews for that movie that also meet some criteria, then after both queries, movie.Reviews should be partially loaded - with just the reviews you want.Alex
• Anonymous
  March 12, 2010
  This is working wonderfully, with one exception: When the relationship between the main entity and its child is many-to-many, the children are coming back null.e.g.var query = ctxt.Orders   .Where(o => o.Id == myOrderId)   .Select(o =>   new   {       o,       products = o.Products           .Where(p => p.Active == true)           .Select(p => new { p })   });This works with one-to-many, where Product has an OrderId FK, but not with many-to-many, where there's a join table in the database.  Order.Products ends up null.  If I call "Include," however, the products are loaded.  Any ideas?Thanks again.
• Anonymous
  March 12, 2010
  Some follow up on my issue above: I tested the query in LINQPad, and the resulting SQL pulls back the child entities.  The problem seems to be that the children are not being enumerated.
• Anonymous
  March 12, 2010
  Apparently my first comment was swallowed -This is working perfectly for me in most cases, but doesn't work when the relationship between the children and the parent is many-to-many.e.g.ctxt.Orders.Where(o => o.Id == 1).Select(o =>   new   {       o,       o.Products           .Where(p => p.Active)           .Select(p => new { p })   });If the Products table has an OrderId FK, o.Products gets loaded; if orders & products go through a join table, o gets loaded but o.Products = null.  Any ideas?Thanks again!
• Anonymous
  July 15, 2010
  @JGruenwald - Did you ever get a workaround for this?  I'm having the exact same issue...this tip is EXCELLENT for one-many, but I can't seem to get it to work for many-many...as you stated, after profiling SQL, it's definitely pulling back the correct data...it just appears that the relationship fixing mechanism isn't binding the data.  Any help would be MUCH appreciated!  Thanks!
• Anonymous
  July 15, 2010
  @JGruenwald - Did you ever get a workaround for this?  I'm having the exact same issue...this tip is EXCELLENT for one-many, but I can't seem to get it to work for many-many...as you stated, after profiling SQL, it's definitely pulling back the correct data...it just appears that the relationship fixing mechanism isn't binding the data.  Any help would be MUCH appreciated!  Thanks!
• Anonymous
  July 17, 2010
  This works for the origianl 5 star parameter value.  If you then update the parameter to 4 star the relationship fix up will will pull the 4 star along with the 5 star entities even thought the parameter is 4 star.I found this using ria services using a datagrid with a domaindatasouce with the parameter souce as a combobox.  Not sure if this is a bug or something i'm doning.
• Anonymous
  August 27, 2010
  @Jake - I never found a really elegant solution for this, so I ended up loading my one-to-many relationships in the first call, and then making an explicit second call to load the many-to-many://Initial queryvar query = ctxt.Orders   .Where(o => o.Id == 1)   .Select(o =>       new       {           o,           o.OrderDetails       });//Fix-upOrder myOrder = query                   .AsEnumerable()                   .Select(order => order.o)                   .FirstOrDefault();//Explicit second queryctxt.LoadProperty(myOrder, o => o.Products);I'd still love to know if there's a better way of achieving this, but making the odd extra call to the database won't usually be a huge performance hit.
• Anonymous
  October 20, 2010
  The comment has been removed