C# LINQ to entities count items in nested table

Question

How to to get in result number of posts in each Category? Example:

Category 0, 10
Category 1, 10

Program:

namespace Query
{
    class Program
    {
        static void Main(string[] args)
        {
            var Categories = new List<Category>();

            FillCategories(Categories);

            var result = from category in Categories
                         from forum in category.Forums
                         from topic in forum.Topics
                         from post in topic.Posts
                         where category.Id == forum.CategoryId
                         && forum.Id == topic.ForumId
                         && topic.Id == post.TopicId
                         group new { category, post } by new { category.Name } into resultSet
                         select new
                         {
                             resultSet.Key.Name
                         };
        }

        private static void FillCategories(List<Category> Categories)
        {
            for (int categoryId = 0; categoryId < 10; categoryId++)
            {
                Category category = new Category();
                category.Id = categoryId;
                category.Name = "Category " + categoryId;
                for (int forumId = 0; forumId < 10; forumId++)
                {
                    Forum forum = new Forum();
                    forum.CategoryId = categoryId;
                    forum.Id = forumId;
                    forum.Name = "Forum " + forumId;

                    for (int topicId = 0; topicId < 10; topicId++)
                    {
                        Topic topic = new Topic();
                        topic.AspNetUserId = "Some User";
                        topic.DateTime = DateTime.Now;
                        topic.Id = topicId;
                        topic.Title = "Some title";
                        topic.ForumId = forumId;
                        for (int postId = 0; postId < 100; postId++)
                        {
                            Post post = new Post();
                            post.AspNetUserId = "Some user";
                            post.DateTime = DateTime.Now;
                            post.Id = postId;
                            post.Message = "Some message" + postId;
                            post.TopicId = topicId;
                            topic.Posts.Add(post);
                        }
                        forum.Topics.Add(topic);
                    }
                    category.Forums.Add(forum);
                }
                Categories.Add(category);
            }
        }
    }

    public class Category
    {
        public Category()
        {
            Forums = new List<Forum>();
        }

        public long Id { get; set; }
        public string Name { get; set; }
        public virtual List<Forum> Forums { get; set; }
    }
    public class Forum
    {
        public Forum()
        {
            Topics = new List<Topic>();
        }

        public long Id { get; set; }
        public string Name { get; set; }
        public long CategoryId { get; set; }
        public virtual List<Topic> Topics { get; set; }
    }

    public class Topic
    {
        public Topic()
        {
            Posts = new List<Post>();
        }

        public long Id { get; set; }
        public string AspNetUserId { get; set; }
        public string Title { get; set; }
        public DateTime DateTime { get; set; }
        public long ForumId { get; set; }
        public virtual List<Post> Posts { get; set; }
    }

    public class Post
    {
        public long Id { get; set; }
        public string Message { get; set; }
        public DateTime DateTime { get; set; }
        public long TopicId { get; set; }
        public string AspNetUserId { get; set; }
    }
}

Answer 1

Adding count of every resultSet at the select part of your query should give you the correct value, which is 10000.

Every category has 10 forums, which has 10 topics for every forum and 100 posts for every topic, 10k items for every category in total

select new
{
    resultSet.Key.Name,
    Count = resultSet.Count()
};

where clause in your query looks redundant, since you are looking at downstream collections only. It can be simplified a little bit

var result = from category in Categories
             from forum in category.Forums
             from topic in forum.Topics
             from post in topic.Posts
             group new { category, post } by new { category.Name } into resultSet
             select new
             {
                 resultSet.Key.Name,
                 Count = resultSet.Count()
             };