Get total count of object's elements and all children objects` elements I have a table, Taxonomy, with data that looks something like the following:

TaxID | ParentID | Name
-----------------------------------
1     | NULL     | Animal
2     | 1        | Cat
3     | 1        | Dog
4     | 1        | Fish
5     | 3        | Poodle
6     | 3        | Golden Retriever
7     | 4        | Goldfish


I then have a second table, Table2, that has records that reference the TaxIDs in Taxonomy. I've labeled the foreign key column referencing Taxonomy, FK_TaxID.

FK_TaxID | Field2
------------------
1        | value
1        | value
1        | value
2        | value
2        | value
2        | value
3        | value


The resulting hierarchy looks something like the following:

- Parent (TaxID=1; #elements=15)
  - Child (TaxID=2; #elements=3)
  - Child (TaxID=3; #elements=8)
    - GrandChild (TaxID=6; #elements=2)
    - GrandChild (TaxID=5; #elements=0)
  - Child (TaxID=4; #elements=5)
    - GrandChild (TaxID=7; #elements=10)


The #elements is based on how many times the TaxID appears in Table2.

I have a method that takes in a Taxonomy ID, and returns the total element count for the record and its sub-children.

For example:


If ID = 1, count should = 43.
If ID = 3, count should = 10.
If ID = 5, count should = 0.


Here is the code I am currently using:

public int GetCount(int ID)
{
    List&lt;int&gt; childrenIDs = DbContext.Taxonomy
        .Where(x =&gt; x.ParentID == ID)
        .Select(x =&gt; x.TaxID)
        .ToList();

    List&lt;int&gt; grandchildrenIDs = DbContext.Taxonomy
        .Where(x =&gt; childrenIDs.Contains(x.ParentID))
        .Select(x =&gt; x.TaxID)
        .ToList();

    int count = DbContext.Table2.Count(x =&gt; x.FK_TaxID == ID
        || childrenIDs.Contains(x.FK_TaxID)
        || grandchildrenIDs.Contains(x.FK_TaxID));

    return count;
}


This code gets the count in three steps:


Sets childrenIDs to equal the list of records where the ParentID equals the given ID.
Sets grandchildrenIDs to equal the list of records where childrenIDs contains the ParentID.
Gets the count of Table2 records where the FK_TaxID equals the inputted ID, or childrenIDs contains the FK_TaxID or grandchildrenIDs contains the FK_TaxID.


This method returns the correct results, yet it is quite slow, and seems overly complicated.

How can I simplify this method? Can I somehow get rid or one of the lists and get all the IDs in one call? Is there a more efficient way of doing this than using Contains() twice?
Why do you need to make the .ToList() call instead of keeping the IEnumerable objects returned by Select methods? 

    public int GetCount(int ID) 
    { 
        var childrenIDs = DbContext.Taxonomy 
            .Where(x =&gt; x.ParentID == ID) 
            .Select(x =&gt; x.TaxID); 
        var grandchildrenIDs = DbContext.Taxonomy 
            .Where(x =&gt; childrenIDs.Contains(x.ParentID)) 
            .Select(x =&gt; x.TaxID); 
        var count = DbContext.Table2.Count(x =&gt; 
         x.FK_TaxID == ID ||
         childrenIDs.Contains(x.FK_TaxID) ||
        grandchildrenIDs.Contains(x.FK_TaxID)); 
        return count; 
}


This way, the only call to database is the last one.