实体框架：查询子实体 [英] Entity Framework: Querying Child Entities

问题描述

有人可以澄清，如果我是正确的，认为我不能得到一个父母和一个子集的数据库？

例如...

  db.Parents 
 .Include（p => p.Children）
 .Where（p => p.Children.Any（c => c.Age> = 5））
  

这将返回所有有5岁以上孩子的父母，但如果我迭代通过Parents.Children收藏，所有的孩子都将出席（不只是5岁以上）。

现在查询对我有意义（我已经问了包括孩子，我有他们！），但可以想象，我想在某些情况下，将子句应用于孩子集合。

问题：

1. 我所说的是否正确？


2. 是否可能得到父母，只是从db的子集调用db的负载吗？


3. 我要离开标记吗？ （不会是第一次）!!!!

我发现了几个博客和SO帖子， ，但没有什么可以解释它对我的小脑子来说很好。

编辑

读了这个博客（谢谢Daz Lewis）.......我还是不明白!!!

在博客中给出的例子，我可以看到我如何实现一个单一的父母实例，但我正在努力解决如何使用一个集合。

如何获得一个IEnumerable，其中每个父母都有过滤的儿童（年龄> = 5）的集合？

进一步的澄清：

在回答DonAndre的评论时，我是在a）有一个5岁以上的孩子（并且只包括那些孩子）的父母名单。

任何帮助赞赏，

谢谢。

解决方案

在单个数据库往返中使用过滤的儿童集合获取父母的集合的唯一方法是使用投影。由于不支持过滤，因此不可能使用热切加载（ Include ）， Include 总是加载整个采集。 @Daz显示的显式加载方式需要每个父实体一个往返。

示例：

  var result = db.Parents 
 .Select（p => new 
 {
 Parent = p，
 Children = p.Children.Where（c => c.Age> = 5）
}）
 .ToList（）; 
  

您可以直接使用匿名类型对象的这个集合。 （您也可以投射到您自己的命名类型而不是匿名投影（但不能像 Parent 中的实体）。）

$ b $如果不禁用更改跟踪，EF的上下文也将自动填充 Parent 的儿童集合（例如使用 AsNoTracking（））。在这种情况下，您可以将匿名结果类型（发生在内存中，没有DB查询）中的父项目设置为

  var parents = results.Select（a => a.Parent）.ToList（）; 
  

parents [i] .Children 将包含您的过滤孩子每个父母。

---

在

中，编辑到您的最后一次编辑：a） 5（和
只包括那些孩子）。

上面的代码将返回所有父母并且只包括 Age > = 5的孩子，所以如果只有年龄< 5.您可以使用额外的其中子句过滤这些，使父母只能获得至少有一个的父母（<$ c $年龄> = 5：

  var result = db.Parents 
 .Where（p => p.Children.Any（c => c.Age> = 5））
 .Select（p = > new 
 {
 Parent = p，
 Children = p.Children.Where（c => c.Age> = 5）
}）
 .ToList（）; 
  

I'm learning about Entity Framework at the mo, and am having problems!!

Can someone clarify if I am right in thinking that I can't get a parent and a subset of it's children from the db?

For example...

db.Parents
.Include(p => p.Children)
.Where(p => p.Children.Any(c => c.Age >= 5))

This will return all Parents that have a child aged 5+, but if I iterate through the Parents.Children collection, all children will be present (not just those over 5 years old).

Now the query does make sense to me (I've asked to include children and I've got them!), but can imagine that I would like to have the where clause applied to the child collection in some scenarios.

Questions:

1. Is what I have said correct?
2. Is it possible to get the parents and just a subset from the db without make loads of calls to the db?
3. Am I way off the mark? (Wouldn't be the 1st time)!!!!

I have found a few blogs and SO posts that touch on the subject, but nothing that explains it well enough for my little brain.

EDIT

Having read this blog (thanks Daz Lewis).......I still don't get it!!!

In the example given in the blog, I can see how I can achieve it against a single instance of Parent, but I'm struggling to work out how I can do it with a collection.

How could I get an IEnumerable, in which each of the parents has a filtered collection of Children (Age>=5)?

Further clarification:

In answer to DonAndre's comment, I am after a) A list of parents who have a child older than 5 (and include only those children).

Any help appreciated,

Thanks.

解决方案

The only way to get a collection of parents with a filtered children collection in a single database roundtrip is using a projection. It is not possible to use eager loading (Include) because it doesn't support filtering, Include always loads the whole collection. The explicite loading way shown by @Daz requires one roundtrip per parent entity.

Example:

var result = db.Parents
    .Select(p => new
    {
        Parent = p,
        Children = p.Children.Where(c => c.Age >= 5)
    })
    .ToList();

You can directly work with this collection of anonymous type objects. (You can also project into your own named type instead of an anonymous projection (but not into an entity like Parent).)

EF's context will also populate the Children collection of the Parent automatically if you don't disable change tracking (using AsNoTracking() for example). In this case you can then project the parent out of the anonymous result type (happens in memory, no DB query):

var parents = result.Select(a => a.Parent).ToList();

parents[i].Children will contain your filtered children for each Parent.

---

Edit to your last Edit in the question:

I am after a) A list of parents who have a child older than 5 (and include only those children).

The code above would return all parents and include only the children with Age >= 5, so potentially also parents with an empty children collection if there are only children with Age < 5. You can filter these out using an additional Where clause for the parents to get only the parents which have at least one (Any) child with Age >= 5:

var result = db.Parents
    .Where(p => p.Children.Any(c => c.Age >= 5))
    .Select(p => new
    {
        Parent = p,
        Children = p.Children.Where(c => c.Age >= 5)
    })
    .ToList();

这篇关于实体框架：查询子实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！