LINQ Query to retrieve multiple levels of relational data

Question

I'm just getting up to speed with asp.net mvc and I'm wondering how one would go about getting relational data more than one level deep from the entity specified in the from clause. Using the following domain model as an example:

A Blog has many posts. Posts have many comments.

How would I write a LINQ query to return entities down to the Blog.Posts.Comments level?

The only (not so elegant) solution I came up with was to use a LINQ query to get Blog and Posts and then a foreach to get comments.

var blog = (from b in _db.BlogSet.Include("Posts")
            select b);

foreach (Post p in blog.Posts)
{
    var comments = (from c in _db.CommentSet
                    where c.PostId = p.Id
                    select c);

    p.Comments = comments;

}

Answer 1

You can just use two from statements:

var comments=from post in blog
             from comment in blog.comments
             where comment.PostId==post.Id
             select comment;

Answer 2

A Blog has many posts. Posts have many comments. How would I write a LINQ query to return entities down to the Blog.Posts.Comments level?

I believe, you could do the following to achieve this:

var blog = (from b in _db.BlogSet.Include("Posts.Comments")
            select b);

In this case, for each blog, the posts and their comments will be fetched.

Marc