What is right way to deal with "N+1" + Count problem?

Supose the model as below:

class public Post
{
    public int Id {get; set;}
    public virtual ICollection<Comment> Comments {get;set;}
}

in the Posts/Ind开发者_运维百科ex Page, I want to show a list of Post, with the Count of comments of each post (not total number of comments of all posts).

1: If I use

context.Posts.Include("Comments")

it will load the whole entity of all related commments , in fact I only need the Count of Comments.

2: If I get the count of each post one by one:

var commentCount = context.Entry(post)
                      .Collection(p => p.Comments)
                      .Query()
                      .Count();

that is a N+1 problem.

Any one knows the right way?

Thank you!

Do you need this for your presentation layer / view model? In such case create specialized ViewModel

public class PostListView 
{
    public Post Post { get; set; }
    public int CommentsCount { get; set; }
}

And use query with projection:

var data = context.Posts
                  .Select(p => new PostListView
                      {
                          Post = p,
                          CommentsCount = p.Comments.Count()
                      });

And you are done. If you need it you can flatten your PostListView so that it contains Post's properties instead of Post entity.

What about something like this:

public class PostView
{
    public String PostName { get; set; }
    public Int32 PostCount { get; set; }
}

public static IEnumerable<PostView> GetPosts()
    {
        var context = new PostsEntities();

        IQueryable<PostView> query = from posts in context.Posts
                                     select new PostView
                                                {
                                                    PostName = posts.Title,
                                                    PostCount = posts.PostComments.Count()
                                                };

        return query;
    }

Then use something like this:

foreach (PostView post in GetPosts())
        {
            Console.WriteLine(String.Format("Post Name: {0}, Post Count: {1}", post.PostName, post.PostCount));
        }

Should display the list as so:

• Post name (12)
• Post name (1)

Etc etc