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.
CodePudding user response：
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
CodePudding user response：
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.Sum(c => c.GrandChildren.Count()),
// Could also be:
// NumberOfGrandChildren = p.Children
// .SelectMany(c => c.GrandChildren)
// .Count(),
NamesOfGrandChildren = string.Join(", ", p.Children
.SelectMany(c => c.GrandChildren)
.Select(g => g.GrandChildName))
}).ToList();