title: How to count total # of items in nested categories (self-referencing category table)
body: <p>I am using C#, ASP.Net, Entity Framework,  LINQ, Microsoft SQLServer</p>
  
  <p>I have two tables</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>So say my categories table contains</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Variables (Id 100 ParentId 25) – directly contains 1 question
      Networking (Id 2 Parent Id null)</p>
  
  <p>I want to know how many questions exist for a each category, including its child categories.<br>
  In other words in this example 18 questions are in programming,  8 are in C#</p>
  
  <p>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()</p>
  
  <p>Here is my category class:  </p>
  
  <pre><code>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&lt;Category&gt; Children { get; set; }
  
      [JsonIgnore]
      public virtual Category Parent { get; set; }
  
      [JsonIgnore]
      public virtual ICollection&lt;Question&gt; Questions { get; set; }
  
  }
  </code></pre>
  
  <p>then in OnModelCreating:</p>
  
  <pre><code>modelBuilder.Entity&lt;Category&gt;()
              .HasMany(x =&gt; x.Children)
              .WithOptional(y =&gt; y.Parent)
              .HasForeignKey(z =&gt; z.ParentId)
              .WillCascadeOnDelete(false);
  </code></pre>
  
  <p>there are several similar questions on StackOverflow, like this: <a href="https://stackoverflow.com/questions/2355791/help-with-generating-a-report-from-data-in-a-parent-children-model">Help with generating a report from data in a parent-children model</a> and this <a href="https://stackoverflow.com/questions/18041015/linq-aggregated-nested-count">linq aggregated nested count</a> but I can't find one that is a good enough match and I can understand</p>
  
  <p>To clarify, I want to know the number of questions in the child categories, not the number of child categories.</p>