实体框架复杂查询

Question

我正在努力学习实体框架，我需要一些帮助来建立我的查询。我有一个Post类，像这样:

public class Post
{
    public int PostID {get;set;}
    public string Title {get;set;}
    public string Content {get;set;}
    public PostStatus Status {get;set;}
    public IList<Comment> Comments {get; set;}
}

我也有一个注释类:

public class Comment
{
    public int CommentID {get;set;}
    public string Content {get;set;}
    public CommentStatus Status {get;set;}
}

我想要的是检索所有文章与状态== PostStatus。已发布，包括Comment with Status == CommentStatus.Published.

如您所知，我想在一个博客中显示所有已发表的文章及其发表的评论。

我检索所有已发表的文章和所有评论，但我只想获得他们已发表的评论，而不是待处理的评论。

var result = from art in context.Posts.Include("Comments")
             where art.Status == PostStatus.Published
             select art;

谢谢你的帮助

Answer 1

这里如何只选择具有至少一个已发布评论的帖子:

var result = from p in context.Posts
             .Include("Comments")
             from c in p.Comments
             where p.Status == PostStatus.Published &&
                   c.Status == CommentStatus.Published
             select new Post {
                      PostID = p.PostID, 
                      // ... other members of Post
                      Comments = p.Comments
                             .Where(c2 => c2.Status == CommentStatus.Published).ToList()
                    };

Answer 2

经过一些研究和使用你的答案，我发现好的方法是:

context.Posts.Where(p=> p.InternalStatus == (int)PostStatus.Published).ToList()
    .Select(item =>
                    new Post
                        {
                            PostID = item.PostID,
                            Title = item.Title,
                            Author = item.Author,
                            Content = item.Content,
                            DateCreation = item.DateCreation,
                            Status = item.Status,
                            Comments =
                                item.Comments.Where(
                                    comment => comment.InternalStatus == (int) CommentStatus.Approved).ToList()
                        }).AsQueryable();

Answer 3

你需要用它创建一个新的对象列表。像这样

var result = from art in context.Posts.Include("Comments").ToList()
             where art.Status == PostStatus.Published
             select new Post { PostID=art.PostID, Title=art.Title, Content=art.Content, Status=art.Status, Comments=art.Comment.Select(comment => comment.Status == CommentStatus.Published).ToList()};