有人可以幫忙嗎？我簡化了表/列名稱等。我到處搜索，但我得到的答案是我想要在下面實作的結果的不完整解決方案。LINQ 新手，所以請多多關照。:-)
表
- 父母（ParentId、ParentName、ParentOccupation）
- 孩子（ChildId、ChildName、OtherField、ParentId）
- GrandChild（GrandChildId、GrandChildName、OtherField、ChildId）
家長
---------- ------------ ------------------
| ParentId | ParentName | ParentOccupation |
---------- ------------ ------------------
| 1 | Mary | Teacher |
| 2 | Anne | Doctor |
| 3 | Michael | Farmer |
| 4 | Elizabeth | Police |
| 5 | Andrew | Fireman |
---------- ------------ ------------------
孩子
--------- ----------- ------------- ----------
| ChildId | ChildName | OtherField | ParentId |
--------- ----------- ------------- ----------
| 1 | Ashley | [SomeValue] | 1 |
| 2 | Brooke | [SomeValue] | 1 |
| 3 | Ashton | [SomeValue] | 3 |
| 4 | Emma | [SomeValue] | 4 |
--------- ----------- ------------- ----------
孫子
-------------- ---------------- ------------- ---------
| GrandChildId | GrandChildName | OtherField | ChildId |
-------------- ---------------- ------------- ---------
| 1 | Andrew | [SomeValue] | 1 |
| 2 | Isabelle | [SomeValue] | 2 |
| 3 | Lucas | [SomeValue] | 2 |
| 4 | Matthew | [SomeValue] | 4 |
-------------- ---------------- ------------- ---------
預期成績
---------- ------------ ------------------ ----------------------- -------------------------
| 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 | |
---------- ------------ ------------------ ----------------------- -------------------------
到目前為止我做了什么
LEFT OUTER JOINS - 獲取所有列但沒有聚合
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 - 包含聚合但并非所有父列都在那里。在計數中也回傳 1，而不是 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()
});
連接逗號分隔的行值（對于孫子的名字） - 在我解決上面的計數之前還沒有嘗試過，但請打開解決方案。
如何結合并實作上述結果？任何幫助表示贊賞！提前致謝。
uj5u.com熱心網友回復：
假設您正在使用 EF，并且設定了導航屬性，那么您的查詢將如下所示：
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();
uj5u.com熱心網友回復：
編輯
問題作者發布的新評論表明，Linq 查詢涉及 EF Core。我最初的回答假設它是一個本地查詢（Linq to Object）。事實上，它更像是一個解釋性查詢（Linq to Entities）。
參見linq to entity vs linq to objects - 它們相同嗎？有關 Linq to object 和 Linq to entity 之間區別的解釋。
在那種情況下，Robert McKee 的回答更切中要害。
出于好奇，Linqpad 顯示此查詢：
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))
});
將被轉換為以下 SQL 查詢：
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"
（使用 Sqlite，以及包含具有導航屬性的物體類的自定義 EFCore 背景關系）
原始答案 - 假設 Linq 反對
這是一種可以構建查詢的方法。
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))
});
這是一個完整的 LinqPad 腳本，隨機生成資料，所以你可以試試：
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; }
}
這是一組結果：
// 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/344922.html