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 |
+----------+------------+------------------+
| 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.
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();