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

  • Anonymous
    June 03, 2009
    AlexAny 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
  • Anonymous
    June 03, 2009
    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
  • Anonymous
    June 03, 2009
    Yes please, strongly typed, no magic strings!
  • Anonymous
    July 16, 2009
    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.
  • Anonymous
    July 16, 2009
    @DHusing .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
  • Anonymous
    July 16, 2009
    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.
  • Anonymous
    July 28, 2009
    _compiledSUPPLIERSGraf = CompiledQuery.Compile((Entities ctx, string IdCliente) =>  from c in   ctx.SUPPLIERS.Include("ARTICLE").Include("ARTICLE.FAMILY")where c.CODIGO == IdClientselect 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 == IdClientselect 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.
  • Anonymous
    July 28, 2009
    @CésarHmm that seems very strange. I'll ask around for you.Alex
  • Anonymous
    August 16, 2009
    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
  • Anonymous
    September 08, 2009
    Many Thanks for this Tip!It saved us a lot of time to figure out the way JOIN works with Iclude.
  • Anonymous
    October 07, 2009
    Hi AlexI have tried the above work around however i still dont get the include data.I have ported your solution to vb.netDim 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 poforumThreadPosts.Include("ELEMENT").Include("ELEMENT_TYPE")Yours Ken
  • Anonymous
    October 08, 2009
    @KenAre 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
  • Anonymous
    December 17, 2009
    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
  • Anonymous
    January 21, 2010
    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.
  • Anonymous
    February 01, 2010
    Hi AlexIs 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.
  • Anonymous
    February 26, 2010
    Thanks a lot for this post, this problem was driving me mad!
  • Anonymous
    April 13, 2010
    The comment has been removed
  • Anonymous
    May 10, 2010
    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".
  • Anonymous
    May 27, 2010
    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; }}
  • Anonymous
    June 30, 2010
    The line that reads:          var mem = (MemberExpression)param.Body;should, instead, be:          var mem = (MemberExpression)exp.Body;
  • Anonymous
    October 21, 2010
    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?
  • Anonymous
    March 20, 2011
    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