Skip Navigation LinksHome > View Post

Projection blows includes in Entity Framework

In Sub-selects in code with the Entity Framework I talked about my foray into using the ADO.NET Entity Framework (EF) in thejoyofcode.com's blog engine. Specifically I mentioned the use of projection and sub-selects to fetch additional data that is queried in one go using T-SQL (thanks to EF).

I'm sure you're probably aware that you can eagerly load associated types in EF by using the Include method. So, if I want my query on the Posts table to also return the Comments for each post I simply:

    dataContext.Posts.Include("Comments");

... and if I want the Comments and the Users ...

    dataContext.Posts.Include("User").Include("Comments");
    
In most cases this works a treat but I was surprised to find that any use of projection in the query just knocks out these Includes. They no longer work! Ouch. In the last post we finished with the query:

from p in dataContext.Posts.Include("Posts").Include("Comments")
select new PostData
{
    Post = p,
    NextLinkData = (from n in be.Posts where n.PostedDate > p.PostedDate orderby n.PostedDate ascending select new PostLinkData { Title = n.Title, LinkTitle = n.LinkTitle }).Take(1).FirstOrDefault(),
    PreviousLinkData = (from prev in be.Posts where prev.PostedDate < p.PostedDate orderby prev.PostedDate descending select new PostLinkData { Title = prev.Title, LinkTitle = prev.LinkTitle }).Take(1).FirstOrDefault(),
};

This should return an IEnumerable<PostData> type with Post, NextLinkData and PreviousData properties. And it does but, sadly, the Post's associated Comments and User are absent. So, what to do?

It's actually pretty easy to workaround, you just need to select the appropriate associations using sub-queries and re-attach them.

var postsWithCount = from p in dataContext.Posts
    select new PostData {
        Post = p,
        Comments = p.Comments, // specifically get the Comments
        User = p.User, // specifically get the User
        NextLinkData = (from n in dataContext.Posts where n.PostedDate > p.PostedDate orderby n.PostedDate ascending select new PostLinkData { Title = n.Title, LinkTitle = n.LinkTitle }).Take(1).FirstOrDefault(),
        PreviousLinkData = (from prev in dataContext.Posts where prev.PostedDate < p.PostedDate orderby prev.PostedDate descending select new PostLinkData { Title = prev.Title, LinkTitle = prev.LinkTitle }).Take(1).FirstOrDefault(),

And then we re-attach each one like so:

postsWithCount.AsEnumerable().Select(pd => Rebuild(pd));

private void Rebuild(PostData postData)
{
    Post post = postData.Post;
    post.User = postData.User;
    post.Comments.Attach(postData.Comments);
}

Situation rescued. And the query plan is still nice and optimised, as though you'd carefully crafted the T-SQL yourself. Phew.

 
Josh Post By Josh Twist
3:25 AM
11 Feb 2009

» Next Post: Sorting associations in the Entity Framework
« Previous Post: Sub-selects in code with the Entity Framework

Comments are closed for this post.

Posted by Sunit Joshi @ 23 Sep 2010 2:05 AM
I'm wondering if I'm having an issue somewhat similar to what you saw? If you have time, can you check out this:
http://forums.silverlight.net/forums/p/202148/471954.aspx#471954


thanks
Sunit

© 2005 - 2014 Josh Twist - All Rights Reserved.