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 (13)

  1. Anonymous says:

    Thanks Alex!

    The only change I would do is

    var movies = dbquery

      .AsEnumerable().Where(m => m.Reviews.Count() >1)

      .Select(m => m.movie);

    to avoid empty entries.

  2. Anonymous says:

    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 code

    BlackDiamondContainer 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);

               }

  3. markgmarkg says:

    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

  4. Alex D James says:

    @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

  5. Anonymous says:

    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.

  6. JGruenwald says:

    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.

  7. JGruenwald says:

    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!

  8. Anonymous says:

    @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!

  9. Anonymous says:

    @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!

  10. Anonymous says:

    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.

  11. JGruenwald says:

    @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 query

    var query = ctxt.Orders

       .Where(o => o.Id == 1)

       .Select(o =>

           new

           {

               o,

               o.OrderDetails

           });

    //Fix-up

    Order myOrder = query

                       .AsEnumerable()

                       .Select(order => order.o)

                       .FirstOrDefault();

    //Explicit second query

    ctxt.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.

  12. TSchmidt says:

    This is great but it seems to kill of other includes you have in your query, example:

    I have a User entity which has a list of ContactForm entities and a list of Relations. I am filtering the relations on some of their fields but ContactForms are not loaded even though i have an Include("ContactForms").

    I looked at tip #22 but i cant really figure out how to cast a query like in this tip to an ObjectQuery<User> so i can actually include my contactforms and load them as well:

    code:

    public something(List<Guid> entityIds)

    {

    return _context.Entities.OfType<UserEntity>()

          //.Include("ContactForms") //this doesnt actually load contactforms

    .WhereIn&lt;UserEntity, Guid&gt;(ue =&gt; ue.EntityId.Value, entityIds) //extension method, found on stackoverflow
    
    .Select(ue =&gt;
    
        new
    
        {
    
            ue = ue,
    
            r = _context.Relations.Where(r =&gt; r.ValidUntil &gt;= DateTime.Now).Select(r =&gt; r),
    
            cf = ue.ContactForms //this actually work, but look at note below
    
        })
    
        .AsEnumerable()
    
        .Select(ue =&gt; ue.ue)
    
        .AsQueryable&lt;UserEntity&gt;();
    

    }

    the cf = ue.ContactForms part actually forces the load of ContactForms but the sql it generates is pretty much unusable in a live production environment, for loading 3 test users where 2 of them have 1 contactform an no relations and 1 of them have 2 contactforms and 1 relation the query produces a horrible union sql that results in 340! rows. I would hate to see what happens when the list of users to be loaded are in the thousands.

    Any pointers on how to fix this?