Only select certain items from related table using linq

Question

Hi I have a 2 tables BlogPost and BlogComments

I want to get all BlogPosts that have a state of "published". and get all BlogComments for each post that has a state of "published".

Im using something like this to get the blogposts:

var BlogPosts = (from p in db.BlogPosts where p.State == State.Published select p).ToArray();

but due to the relationship with BlogComments it autiomatically has all the BlogComments (both published and unpublished).

How can I just get the "published" comments for each of the blogposts (i.e the approved ones)

thanks

Answer 1

Try selecting a new BlogPostViewModel, an analog to BlogPost but with an IEnumerable<BlogComment> instead, with just the blog post data and a collection of published comments.

    select new BlogPostViewModel {
        Title = p.Title,
        Body = p.Body,
        Comments = p.Comments.Where( c => c.Published );
    });

Where BlogPostViewModel is:

public class BlogPostViewModel
{
     public string Title { get; set; }
     public string Body { get; set; }
     public IEnumerable<BlogComment> Comments { get; set; }
}

Answer 2

var BlogPosts = from p in db.BlogPosts where p.State == State.Published select new {
    Post = p,
    Comments = (from c in db.BlogComments where c.State == State.Published && c.Post = p select c)
};

Answer 3

var publishedComments = db.BlobPosts.Where(p => p.State == State.Published)
                                    .SelectMany(p => p.Comments)
                                    .Where(c => c.State == State.Published);

Sorry for not using the query syntax. Hopefully this will get you on your way though.