Only select certain items from related table using linq
Hi I have a 2 tables BlogPost 开发者_StackOverflow社区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
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; }
}
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)
};
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.
精彩评论