无法正确组装查询
本文关键字:查询 | 更新日期: 2023-09-27 18:27:46
3表:
- ParentTable:ParentID(假设ParentID=5)
- ParentChildrenTable:ParentID,ChildrenID(假设ParentID=5有3个关系行)
- ChildrenTable:ChildrenID,ChildrenName(假设ParentID=5有3个孩子,例如:A、B、C)
我想做一些类似"让所有ParentID=5的孩子打印他们的名字"的事情使用实体框架和LinQ
使用像这样的伪代码就是我的意思:
Parent fifthParent = db.ParentTable.FirstOrDefault(p => p.ParentID == 5);
foreach (ParentChildren parentChildren in fifthParent.ParentChildren) // will iterate 3 times
{
//get each child seperatly according
foreach(Child child in parentChildren.Children)
{
//print A (on 1st iteration)
//print B (on 2nd iteration)
//print C (on 3rd iteration)
}
}
据我所见,循环应该是2,尽管我在过去的两个小时里一直在努力。希望你能提供代码示例,因为我仍然无法掌握这些查询的原理。
您可以使用SelectMany
来压平内部集合:
Parent fifthParent = db.ParentTable.FirstOrDefault(p => p.ParentID == 5);
var children = fifthParent.ParentChildren.SelectMany(c=>c.Children)
foreach (Child parentChildren in children)
{
//print children.
}
这将把所有内容连接在一起,并对其进行过滤,只返回父ID为5的子项。
var childrenOfFifthParent =
from parent in context.ParentTable
join parentChild in context.ParentChildrenTable on parent.ParentID
equals parentChild.ParentID
join child in context.ChildrenTable on parentChild.ChildID
equals child.ChildID
where parent.ParentID == 5
select child;
然后你可以做一些类似的事情:
foreach (var child in childrenOfFifthParent.ToList())
{
// print the child
}
我会从另一个方向开始:
foreach ( var child in db.ChildrenTable
.Where( c => c.ParentChildren.Any( pc => pc.ParentID == 5 ) ) )
{
var foo = child.Name // or whatever else
}
您的ParentChildrenTable
类应该类似于以下
public class ParentChildrenTable
{
public int Id { get; set; }
public int ParentId { get; set;}
public int ChildId {get; set; }
public virtual ParentTable Parent { get; set; }
public virtual ChildrenTable Child { get; set; }
}
这意味着在第一个循环中,您可以只访问ParentChildrenTable
对象的Child
属性:
foreach (ParentChildren parentChildren in fifthParent.ParentChildren) // will iterate 3 times
{
ChildrenTable child = parentChildren.Child;
//print A (on 1st iteration)
//print B (on 2nd iteration)
//print C (on 3rd iteration)
}
更新:
要使用单个LINQ查询完成此操作,可以使用SelectMany
,然后使用Select
调用:
var children = db.ParentTable.Where(p => p.ParentID == 5)
.SelectMany(p => p.Children)
.Select(pc => pc.Child);
或者你可以从孩子们开始:
var children = db.ChildrenTable.Where(c => c.ParentChildren.Any(pc => pc.ParentId == 5));