How to count total # of items in nested categories (self-referencing category table)

Question

I am using C#, ASP.Net, Entity Framework, LINQ, Microsoft SQLServer

I have two tables

tblCategories
Id
Name
ParentId     (parentId is the Id of another category in this table that is the parent of this one)

tblQuestion
Id
CategoryId
Etc.

So say my categories table contains

Programming  (Id 1 ParentId null)     - directly contains 10 questions
     C# (Id 25 ParentId 1)    – directly contains 5 questions
        Interview Questions (Id 99 ParentId 25) - directly contains 2 questions

Variables (Id 100 ParentId 25) – directly contains 1 question Networking (Id 2 Parent Id null)

I want to know how many questions exist for a each category, including its child categories.
In other words in this example 18 questions are in programming, 8 are in C#

Is it possible to do this in a query or do I need some kind of iterative loop called on each category? such as db.categories.Where(something).count()

Here is my category class:

public class Category
{

    [Key]
    public int Id { get; set; }

    [StringLength(40)]
    public string Name { get; set; }

    public int? ParentId { get; set; }

    [JsonIgnore]
    public virtual ICollection<Category> Children { get; set; }

    [JsonIgnore]
    public virtual Category Parent { get; set; }

    [JsonIgnore]
    public virtual ICollection<Question> Questions { get; set; }

}

then in OnModelCreating:

modelBuilder.Entity<Category>()
            .HasMany(x => x.Children)
            .WithOptional(y => y.Parent)
            .HasForeignKey(z => z.ParentId)
            .WillCascadeOnDelete(false);

there are several similar questions on StackOverflow, like this: Help with generating a report from data in a parent-children model and this linq aggregated nested count but I can't find one that is a good enough match and I can understand

To clarify, I want to know the number of questions in the child categories, not the number of child categories.

Answer 1

I am unsure as to what you want to accomplish, but as others pointed out it can't be accomplished by simple Linq.

• If what you want is to get a total count of questions given a category: you need to:

  var count = TotalQuestions(category1);
  

TotalQuestions method

 public int TotalQuestions(Category category)
        {
            var totalQuestions = category.Questions.Count;
            foreach (var innerCategory in category.Categories)
            {
                totalQuestions += TotalQuestions(innerCategory);
            }
            return totalQuestions;

            //OR
            //return category.Questions.Count + category.Categories.Sum(innerCategory => TotalQuestions(innerCategory));
        }

• If you want to have the total count of questions for each Category (including the count of its child categories) you need:

  var counts = new Dictionary<Category, int>();        
  TotalQuestions(category1, counts);
  

2nd TotalQuestions method

private int TotalQuestions(Category category, Dictionary<Category, int> counts)
        {
            if (!counts.ContainsKey(category))
            {
                counts.Add(category, category.Questions.Count);
            }

            foreach (var innerCategory in category.Categories)
            {
                counts[category] += TotalQuestions(innerCategory, counts);
            }

            return counts[category];
        }

Sample

For this sample data:

        var category1 = new Category(1, "Cat1", null);
        var category2 = new Category(1, "Cat2", category1);
        var category3 = new Category(1, "Cat3", category2);
        var category4 = new Category(1, "Cat4", category2);
        var category5 = new Category(1, "Cat5", category3);

        category1.Questions.Add(new Question("q1"));
        category1.Questions.Add(new Question("q2"));
        category1.Questions.Add(new Question("q3"));
        category1.Questions.Add(new Question("q4"));

        category2.Questions.Add(new Question("q1"));
        category2.Questions.Add(new Question("q2"));
        category2.Questions.Add(new Question("q3"));

        category3.Questions.Add(new Question("q1"));
        category3.Questions.Add(new Question("q2"));

        category4.Questions.Add(new Question("q1"));

        category5.Questions.Add(new Question("q1"));
        category5.Questions.Add(new Question("q2"));

        var count = TotalQuestions(category1);
        MessageBox.Show(count.ToString());

        var counts = new Dictionary<Category, int>();
        TotalQuestions(category1, counts);

You Get:

• count = 12
• And in counts:
  • category1 => 12
  • category2 => 8
  • category3 => 4
  • category4 => 1
  • category5 => 2

Answer 2

You can't do such counting using only a simple linq query or only with fluent callings for the simple fact that you have a variable number of levels of iterations (the number of generations: parents, children, grandchildren, etc varies). Therefore, I would recommend creating a recursive method to count children:

static void RecursiveQuestionCount(Category cat, ref int count)
{
    count += Questions.Count(); // this cat's questions.
    foreach(var child in cat.Children)
    {
        RecursiveQuestionCount(child, ref count); // will add each child and so it goes...
    }
}

The count result will be stored in the ref int count:

int catCount;

Foo.RecursiveQuestionCount(existingCategoryObj, catCount);

Console.WriteLine(catCount);

Answer 3

Yes, you need a recursive method.

// Recursively counts the number of children of this parent
static int CountChildren(Category parent)
{
    if (parent == null)
        return 0;

    if (parent.Children == null || !parent.Children.Any())
        return 0;

    return parent.Children.Count() 
         + parent.Children.Sum(ch => CountChildren(ch));
}