Linq query with join, group and count (extension format)

Question

I have a table, that stores some information and reference (column parent_ID) to parent row in the same table.

I need to get list of all records (using Linq extension format) with count of child records. This is SQL query that gives me the desired information

    Select a.ID, a.Name, Count(b.ID) from Table a
    left join Table b on b.parent_ID=a.ID
    group by a.ID, a.Name

Example

|    ID   | Name     | parent_ID |
----------------------------------
|    1    | First    |   null    |
|    2    | Child1   |    1      |
|    3    | Child2   |    1      |
|    4    | Child1.1 |    2      |

The result should be:

|    1    | First    |    2      |
|    2    | Child1   |    1      |
|    3    | Child2   |    0      |
|    4    | Child1.1 |    0      |

I tried to make at least child counting, but it doesn't work...

    var model = _db.Table 
            .GroupBy(r => new { r.parent_ID })
            .Select(r => new {
                r.Key.parent_ID,
                ChildCount = r.GroupBy(g => g.parent_ID).Count() 
            });

Shouldn't this query be equivalent to something like this:

    select parent_ID, count(parent_ID) from Table group by Table

but it returns count = 1 for each row...

How can i make such a query using linq extension format?

Answer 1

What I believe you are looking for is a group join which can be easier to understand using linq query syntax instead of the linq extensions so for ease of understanding I will post both methods.

I'm self-joining the table on the ID to the parent_ID into an object which keeps the referential integrity for you and select an anonymous object to select out the parent then all of the children.

This is using straight LINQ query syntax.

    var model = from t1 in _db.Test1
                join t2 in _db.Test1 on t1.ID equals t2.parent_ID into c1
                select new {Parent = t1, Children = c1};

And here is the code using LINQ extensions

    var model2 = _db.Test1.GroupJoin(_db.Test1, 
                     t1 => t1.ID, 
                     t2 => t2.parent_ID,
                     (t1, c1) => new {Parent = t1, Children = c1});

I used a quick test program I threw together to post the results into a textbox for both methods but the code was the same so I'll just post it once.

    foreach (var test in model)
    {
        textBox1.AppendTextAddNewLine(String.Format("{0}: {1}", 
                                      test.Parent.Name, 
                                      test.Children.Count()));
    }

And the results of both of those tests were the same below:

    First: 2
    Child1: 1
    Child2: 0
    Child1.1: 0

    First: 2
    Child1: 1
    Child2: 0
    Child1.1: 0