LINQ Left Outer Join Multiple Tables with Group Count and Row Concatenation

Question

Can someone help with below please? I simplified the table/column names, etc. I search everywhere but the answers I get are incomplete solutions for the results I want to achieve below. New to LINQ so please be kind. :-)

TABLES

• Parent (ParentId, ParentName, ParentOccupation)
• Child (ChildId, ChildName, OtherField, ParentId)
• GrandChild (GrandChildId, GrandChildName, OtherField, ChildId)

Parent

+----------+------------+------------------+
| ParentId | ParentName | ParentOccupation |
+----------+------------+------------------+
| 1        | Mary       | Teacher          |
| 2        | Anne       | Doctor           |
| 3        | Michael    | Farmer           |
| 4        | Elizabeth  | Police           |
| 5        | Andrew     | Fireman          |
+----------+------------+------------------+

Child

+---------+-----------+-------------+----------+
| ChildId | ChildName | OtherField  | ParentId |
+---------+-----------+-------------+----------+
| 1       | Ashley    | [SomeValue] | 1        |
| 2       | Brooke    | [SomeValue] | 1        |
| 3       | Ashton    | [SomeValue] | 3        |
| 4       | Emma      | [SomeValue] | 4        |
+---------+-----------+-------------+----------+

GrandChild

+--------------+----------------+-------------+---------+
| GrandChildId | GrandChildName | OtherField  | ChildId |
+--------------+----------------+-------------+---------+
| 1            | Andrew         | [SomeValue] | 1       |
| 2            | Isabelle       | [SomeValue] | 2       |
| 3            | Lucas          | [SomeValue] | 2       |
| 4            | Matthew        | [SomeValue] | 4       |
+--------------+----------------+-------------+---------+

EXPECTED RESULTS

+----------+------------+------------------+-----------------------+-------------------------+
| ParentId | ParentName | ParentOccupation | NumberOfGrandChildren | NamesOfGrandChildren    |
+----------+------------+------------------+-----------------------+-------------------------+
| 1        | Mary       | Teacher          | 3                     | Andrew, Isabelle, Lucas |
| 2        | Anne       | Doctor           | 0                     |                         |   
| 3        | Michael    | Farmer           | 0                     |                         |
| 4        | Elizabeth  | Police           | 1                     | Matthew                 |
| 5        | Andrew     | Fireman          | 0                     |                         | 
+----------+------------+------------------+-----------------------+-------------------------+

WHAT I HAVE DONE SO FAR

LEFT OUTER JOINS - getting all the columns but no aggregates

var result1 = (from p in Parent
               join c in Child on p.ParentId equals c.ParentId into pcj
               from pc in pcj.DefaultIfEmpty()
               join g in GrandChild on pc.ChildId equals g.ChildId into cgj
               from cg in cgj.DefaultIfEmpty()
               where [some criteria]
               select new 
               {
                  ParentId = p.ParentId,
                  ParentName = p.ParentName,
                  ChildId = pc.ChildId,
                  ChildName = pc.ChildName,
                  GrandChildId = cg.GrandChildId,
                  GrandChildName = cg.GrandChildName   
               });

COUNTS - contain the aggregate but not all parent columns are there. Also returns 1 in the count, instead of 0.

var result2 = (from p in Parent
               join c in Child on p.ParentId equals c.ParentId into pcj
               from pc in pcj.DefaultIfEmpty()
               join g in GrandChild on pc.ChildId equals g.ChildId into cgj
               from cg in cgj.DefaultIfEmpty()
               where [some criteria]
               group new { p } by new { p.ParentId } into r
               select new 
               {
                  ParentId = r.Key.Id,
                  NumberOfGrandChildren = r.Count()
               });

CONCATENATE COMMA SEPARATED ROW VALUES (for names of grandchildren) - have not attempted yet until I solve the count above, but open for solutions please.

How can I combine and achieve the results above? Any help is appreciated! Thanks in advance.

Answer 1

Assuming you are using EF, and you have navigation properties set up, then your query would look like this:

var result = context.Parents
  .Select(p => new {
    p.ParentId,
    p.ParentName,
    p.ParentOccupation,
    NumberOfGrandChildren = p.Children
       .SelectMany(c => c.GrandChildren)
       .Count(),
    NamesOfGrandChildren = string.Join(", ", p.Children
      .SelectMany(c => c.GrandChildren)
      .Select(g => g.GrandChildName))
  }).ToList();

Answer 2

EDIT

New comments posted by the author of the question show that the Linq query involves EF Core. My original answer assumed it was a local query (Linq to Object). In fact, it rather seems to be an interpreted query (Linq to Entities).

See linq to entities vs linq to objects - are they the same? for explanations about the distinction between Linq to object and Linq to entities.

In that case, Robert McKee's answer is more to the point.

For curiosity's sake, Linqpad shows that this query:

Parents
    .Select(p => new
    {
        ParentId = p.Id,
        ParentName = p.Name,
        ParentOccupation = p.Occupation,
        GrandChildrenCount = p.Children
            .SelectMany(c => c.GrandChildren)
            .Count(),
        GranchildrenNames = string.Join(", ", p.Children
            .SelectMany(c => c.GrandChildren)
            .Select(gc => gc.Name))
    });

will be translated to the following SQL query:

SELECT "p"."Id", "p"."Name", "p"."Occupation", (
    SELECT COUNT(*)
    FROM "Children" AS "c"
    INNER JOIN "GrandChildren" AS "g" ON "c"."Id" = "g"."ChildId"
    WHERE "p"."Id" = "c"."ParentId"), "t"."Name", "t"."Id", "t"."Id0"
FROM "Parents" AS "p"
LEFT JOIN (
    SELECT "g0"."Name", "c0"."Id", "g0"."Id" AS "Id0", "c0"."ParentId"
    FROM "Children" AS "c0"
    INNER JOIN "GrandChildren" AS "g0" ON "c0"."Id" = "g0"."ChildId"
) AS "t" ON "p"."Id" = "t"."ParentId"
ORDER BY "p"."Id", "t"."Id", "t"."Id0"

(Using Sqlite, and a custom EFCore context containing Entity Classes with navigation properties)

ORIGINAL ANSWER - assuming Linq to object

Here is a way you could construct your query.

var Result = Parents
    // Stage 1: for each parent, get its Chidren Ids
    .Select(p => new
    {
        Parent = p,
        ChildrenIds = Children
            .Where(c => c.ParentId == p.Id)
            .Select(c => c.Id)
            .ToList()
    })
    // Stage 2: for each parent, get its Grandchildren, by using the childrenIds list constructed before
    .Select(p => new
    {
        p.Parent,
        GrandChildren = Grandchildren
            .Where(gc => p.ChildrenIds.Contains(gc.ChildId))
            .ToList()
    })
    // Stage 3: for each parent, count the grandchildren, and get their names
    .Select(p => new
    {
        
        ParentId = p.Parent.Id,
        ParentName = p.Parent.Name,
        ParentOccupation = p.Parent.Occupation,
        NumberOfGrandChildren = p.GrandChildren.Count(),
        GranchildrenNames = string.Join(", ", p.GrandChildren.Select(gc => gc.Name))
    });

And here is a full working LinqPad script, with random data generation, so you can try it:

void Main()
{
    var rnd = new Random();
    var Parents = Enumerable
        .Range(0, 10)
        .Select(i => new Parent
        {
            Id = i,
            Name = $"Parent-{i}",
            Occupation = $"Occupation{i}"
        })
        .ToList();
    var Children = Enumerable
        .Range(0,15)
        .Select(i => new Child
        {
            Id = i,
            Name = $"Child{i}",
            ParentId = rnd.Next(0, 10)
        })
        .ToList();
    var GrandChildren = Enumerable
        .Range(0, 25)
        .Select(i => new GrandChildren
        {
            Id = i,
            Name = $"GrandChild{i}",
            ChildId = rnd.Next(0, 15)
        })
        .ToList();


    var Result = Parents
        // Stage 1: for each parent, get its Chidren Ids
        .Select(p => new
        {
            Parent = p,
            ChildrenIds = Children
                .Where(c => c.ParentId == p.Id)
                .Select(c => c.Id)
                .ToList()
        })
        // Stage 2: for each parent, get its Grandchildren, by using the childrenIds list constructed before
        .Select(p => new
        {
            p.Parent,
            GrandChildren = GrandChildren
                .Where(gc => p.ChildrenIds.Contains(gc.ChildId))
                .ToList()
        })
        // Stage 3: for each parent, count the grandchildren, and get their names
        .Select(p => new
        {
            
            ParentId = p.Parent.Id,
            ParentName = p.Parent.Name,
            ParentOccupation = p.Parent.Occupation,
            NumberOfGrandChildren = p.GrandChildren.Count(),
            GranchildrenNames = string.Join(", ", p.GrandChildren.Select(gc => gc.Name))
        })
        .Dump();
}

// You can define other methods, fields, classes and namespaces here
public class Parent
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Occupation { get; set; }
}

public class Child
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int ParentId { get; set; }
}

public class GrandChildren
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int ChildId { get; set; }
}

And here is a set of results:

// Parents
0   Parent-0    Occupation0
1   Parent-1    Occupation1
2   Parent-2    Occupation2
3   Parent-3    Occupation3
4   Parent-4    Occupation4
5   Parent-5    Occupation5
6   Parent-6    Occupation6
7   Parent-7    Occupation7
8   Parent-8    Occupation8
9   Parent-9    Occupation9

// Children
0   Child0  1
1   Child1  5
2   Child2  8
3   Child3  6
4   Child4  9
5   Child5  3
6   Child6  0
7   Child7  4
8   Child8  9
9   Child9  7
10  Child10 8
11  Child11 2
12  Child12 7
13  Child13 7
14  Child14 8

// GrandChildren
0   GrandChild0 7
1   GrandChild1 11
2   GrandChild2 11
3   GrandChild3 14
4   GrandChild4 6
5   GrandChild5 0
6   GrandChild6 11
7   GrandChild7 6
8   GrandChild8 0
9   GrandChild9 12
10  GrandChild10    9
11  GrandChild11    7
12  GrandChild12    0
13  GrandChild13    3
14  GrandChild14    11
15  GrandChild15    9
16  GrandChild16    2
17  GrandChild17    12
18  GrandChild18    12
19  GrandChild19    12
20  GrandChild20    14
21  GrandChild21    12
22  GrandChild22    11
23  GrandChild23    14
24  GrandChild24    12

// Result
0   Parent-0    Occupation0 2   GrandChild4, GrandChild7
1   Parent-1    Occupation1 3   GrandChild5, GrandChild8, GrandChild12
2   Parent-2    Occupation2 5   GrandChild1, GrandChild2, GrandChild6, GrandChild14, GrandChild22
3   Parent-3    Occupation3 0   
4   Parent-4    Occupation4 2   GrandChild0, GrandChild11
5   Parent-5    Occupation5 0   
6   Parent-6    Occupation6 1   GrandChild13
7   Parent-7    Occupation7 8   GrandChild9, GrandChild10, GrandChild15, GrandChild17, GrandChild18, GrandChild19, GrandChild21, GrandChild24
8   Parent-8    Occupation8 4   GrandChild3, GrandChild16, GrandChild20, GrandChild23
9   Parent-9    Occupation9 0