Tip 22 – How to make Include really Include


This is 22nd post in my ongoing series of Entity Framework Tips.

If you want to do eager loading with the Entity Framework it is generally really easy, you simply write something like this:

var results = 
        from post in ctx.Posts.Include(“Comments”)
        where post.Author.EmailAddress ==
“alexj@microsoft.com
        select post;

In this example for each post that matches you will get back it’s comments, all in one query, aka Eager loading.

Cool. So far so good.

However if you start to do more interesting queries that change the ‘shape’ of the query, either by introducing an intermediary join or something, maybe like this:

var results =
        from post in ctx.Posts.Include(“Comments”)
        from blog in post.Blogs
        where blog.Owner.EmailAddress == “
alexj@microsoft.com
        select post;

The include no longer works.

Why? 

When the first example is translated into a query the columns that are selected remain the same from start to end. The filter introduces a join but it doesn’t effect which columns are selected, so from the time the Include() is applied until the final select the shape of the query is unchanged.

In the second example when the Include() is applied the query includes just post columns, but then the second from changes the shape to include both post and blog columns. i.e. the shape of results changes, albeit temporarily, and this temporary shape change stops Include() working.

Now in this particular example you can re-write the query like this, to get it working again:

var results =
        from post in ctx.Posts.Include(“Comments”)
        where post.Blogs.Any(
             b => b.Owner.EmailAddress == “
alexj@microsoft.com
        )
        select post;

…and it will work again. Because the query has been rewritten to avoid changing the shape, from the time the Include() is applied until the end.

Unfortunately this type of workaround though change is somewhat invasive because it forces you to change how you write your queries just to get include working.

There is however a better option.

Workaround

The workaround is actually pretty simple, you just move the Include to the end of the query.

var results =
        ((from post in ctx.Posts
        from blog in post.Blogs
        where blog.Owner.EmailAddress ==
“alexj@microsoft.com
        select post) as ObjectQuery<Post>).Include(“Comments”);

For this to work your final select must be entities, i.e. select post rather than select new {…}, if so you can cast the result to an ObjectQuery, and then do the include.

This works just fine, because between when the include is applied and the end of the query the shape remains the same.

Comments (22)

  1. simon.segal says:

    Alex

    Any plans on .Include() getting a more strongly typed version that takes an expression perhaps as an argument and not the magic string? Also it would be handy to have sub querying ability on the .Include(p => p.Comments).Where(c => c.DateOfCommnet > 10-10-2009) as an example.

    Thanks,

    Simon

  2. Alex D James says:

    Simon,

    We have definitely been talking about it, but it is too early to tell whether it will make the cut or not.

    My advice it to be loud. The more feedback we get saying this is vital, the more likely it is we will find a way to do it 😉

    Alex

  3. Anonymous says:

    Yes please, strongly typed, no magic strings!

  4. Anonymous says:

    I’m suprised, about the way this topic is solved. Atm, we use ".Include(typeof(MyData).Name)" to get it a little more type safety.

  5. Alex D James says:

    @DH

    using .Include(typeof(MyData).Name) is not a fail safe solution. Include(…) takes the name of the Navigation Property as a parameter not the type name of the property.

    It seems that you have ended in a happy place where those two things are the same.

    But they could be (and generally are in most models I’ve seen) different.

    A type safe way would be this:

    .Include(p => p.PropertyName);

    Which is a pattern supported by LINQ to SQL but not EF.

    • Alex
  6. Anonymous says:

    Ah, ok. You’re right. The names of the navigation properties are the same as the destination table. That explains some curios, that we had.

    Thanks a lot.

  7. Anonymous says:

    _compiledSUPPLIERSGraf = CompiledQuery.Compile((Entities ctx, string IdCliente) =>  

    from c in   ctx.SUPPLIERS.Include("ARTICLE").Include("ARTICLE.FAMILY")

    where c.CODIGO == IdClient

    select c);

    Hi All.

    I wonder if you can help me to solve the problem I’m having with the above query. The query works fine when the supplier entity has associated information (articles) but when it doesn’t I get the an error warning me that the entity parameter cannot be empty.

    When I change the query as follows (deleting the "ARTICLE.FAMILY" Include):

    _compiledSUPPLIERSGraf = CompiledQuery.Compile((Entities ctx, string IdCliente) =>

    from c in ctx.SUPPLIERS.Include("ARTICLE")

    where c.CODIGO == IdClient

    select c);

    it always works (no matter whether the suppliers entity has data), but I cannot access the family information (which is a navigation property).

    Any idea about how to solve this?

    Thanks in advance.

  8. Alex D James says:

    @César

    Hmm that seems very strange. I’ll ask around for you.

    Alex

  9. Anonymous says:

    I’ve got some code that makes a call to a sproc and returns a collection of entities with a subsequent loop over those entities a several .Load()’s on a number of references. This is pretty poor and the performance is going to get worse and worse :

    using (BaseEntityConnection BaseEntityContext = new BaseEntityConnection())

    {

    List&lt;Project_TME&gt; entities = BaseEntityContext.GetProjectTMEByStoreID(storeOrgLevelId, startDate, endDate).ToList&lt;Project_TME&gt;();
    
    foreach (Project_TME entity in entities)
    
    {
    
        entity.Hours_Cat1Reference.Load();
    
        entity.Hours_Cat2Reference.Load();
    
        entity.Project_TME_Launch.Attach(
    
                from pteml in entity.Project_TME_Launch.CreateSourceQuery()
    
                from audience in pteml.AUDIENCE_HEAD.AUDIENCE_DETAIL
    
                where audience.OrganisationLevelValue.ID == storeOrgLevelId
    
                select pteml
    
            );
    
        entity.ProjectCategoryReference.Load();
    
        entity.ProjectReference.Load();
    
    }
    
    return entities;
    

    }

    I thought maybe I could load the references before the call to the sproc and hopefully the tracking manager would implement an ‘Identity Map’ to look up the references without hitting the database but I still see all the hits against the database in SQL Profiler (is this because I haven’t defined a transaction?)

    I saw your blog post but don’t know how to apply the .include against the results of the sproc – I’m guessing it’s not possible because the query as long since been executed.

    Have you got any suggestions for this situation? Ideally I’d like to be able to map several entities against the sproc results and return the references that I know I need but obviously that isn’t possible (although at present my EF knowledge is very sketchy so I could easily be wrong!!)

    Many thanks

  10. Anonymous says:

    Many Thanks for this Tip!

    It saved us a lot of time to figure out the way JOIN works with Iclude.

  11. Anonymous says:

    Hi Alex

    I have tried the above work around however i still dont get the include data.

    I have ported your solution to vb.net

    Dim forumThreadPosts As ObjectQuery(Of POST) = From po In ef.POST _

    From forumPostIsPlacedOn In po.ELEMENT.Places_Element _

    Where forumPostIsPlacedOn.Placed.COMMUNITY_CONTAINER.ID = ForumThreadID _

    Select po

    forumThreadPosts.Include("ELEMENT").Include("ELEMENT_TYPE")

    Yours Ken

  12. Alex D James says:

    @Ken

    Are you re-assigning?

    i.e.

    forumThreadPosts = forumThreadPosts.Include("ELEMENT").Include("ELEMENT_TYPE")

    Calling .Include() doesn’t modify the current query it returns a new one, so you have to make sure you query the results?

    Alex

  13. serge.mueller.zh.ch@gmail.com says:

    Hello there,

    i’ve got a related question.. is there a way to retrieve the includes that were made on a ObjectQuery<> so i may reapply them when needed (at the latest possible moment) ?

    We use some basic functionality that predefines includes which get lost along the way..

    that would be very helpful… otherwise we have a huge design breaking change ahead of us..

    Yours serge

  14. Anonymous says:

    Just an FYI for anyone trying to do the "as ObjectQuery<Post>" in a compiled query: it won’t work.

    However, if you do a normal cast instead of using the "as" keyword, it will work. The syntax isn’t as nice, but at least it works.

  15. Anonymous says:

    Hi Alex

    Is there anyway so that i can apply a filter on child records and only those child records which satisfy the filter condition should be included with Parent Entity.

    To further Clarify.. My Problem is that i have ORDER Entity Type and ORDERDETAILS Entity Type. I want only those ORDERDETAILS included with ORDER Entity which satisfy some criteria not all the ORDERDETAILS.

  16. Anonymous says:

    Thanks a lot for this post, this problem was driving me mad!

  17. Anonymous says:

    Hi Alex,

    I have the exact same issue as Rishi (Posted Feb 2).  Being able to filter the children records on a parent would be AWESOME.  I just started using EF and RIA services and I can’t figure out how to do this one – and it’s driving me crazy!

    This was an excellent post!

  18. Anonymous says:

    This works with eager loading:

    var r =

     (from n in context.News.Include("Sources")

      select n).ToList();

    This doesn't work:

    var r =

     (from n in context.News.Include("Sources")

      let calculation = true

      select n).ToList();

    The let-assignment could be anything later used in the query, maybe in a where-condition. But even the simple case with a "true" assignment drops eager loading.

    This does work:

    var r =

     ((from n in context.News

      let calculation = true

      select n) as ObjectQuery<Post>).Include("Sources").ToList();

    Why??? This seems pretty stupid to me and is a major missing "feature".

  19. Anonymous says:

    dont wait for MS, just use expression trees:

    public static class ObjectQueryExtension

    {

     public static ObjectQuery<T> Include<T,TProperty>(this ObjectQuery<T> obj, Expression<Func<T,TProperty>> exp)

     where T:EntityObject

     {

         var mem = (MemberExpression)param.Body;

         obj.Include(mem.Member.Name);

         return obj;

     }

    }

  20. Anonymous says:

    The line that reads:

              var mem = (MemberExpression)param.Body;

    should, instead, be:

              var mem = (MemberExpression)exp.Body;

  21. Anonymous says:

    Hi Alex,

    I am having a similar question as Rishi and Lori. I am coming to the conclusion that it is not possible to filter children records based during eager loading and include. Is that correct?

  22. Anonymous says:

    Hi Alex,

    Create post, it helped me a lot! I tried to use eagor loading wile doing a "group by into". This didn't work because of the anonymous that is created by the group by. your post directed me in the right direction.

    Thnx