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.