Linq2Entities等效查询父母/子女关系，与所有父母和子女，过滤/订购子女

本文介绍了Linq2Entities等效查询父母/子女关系，与所有父母和子女，过滤/订购子女的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这个问题太长了，所以让我们看一下代码.给定如下所示的实体(表)，以下Sql的linq2entities等效项是什么:

So the question is ridiculously long, so let's go to the code. What's the linq2entities equivalent of the following Sql, given entities (tables) that look like:

Parent --- parent_id parent_field1 Child -- child_id parent_id child_field1 child_field2

sql:

select p.*, c.* from parent p inner join p on p.parent_id = child.parent_id where c.child_field1 = some_appropriate_value order by p.parent_field1 c.child_field2

L2E让我们执行.include()，这似乎是为子代保留排序和过滤的适当位置，但是include方法不接受表达式(为什么！！).因此，我猜测目前无法完成此操作，因为这是很多文章所说的内容，但是它们已经过时了，我想知道EF6是否有可能.

L2E let's you do .include() and that seems like the appropriate place to stick the ordering and filtering for the child, but the include method doesn't accept an expression (why not!?). So, I'm guessing this can't be done right now, because that's what a lot of articles say, but they're old, and I'm wondering if it's possible with EF6.

此外，我无权访问上下文，因此我需要使用lambda语法版本.

Also, I don't have access to the context, so I need the lambda-syntax version.

我正在寻找一个看起来像这样的结果对象层次结构:

I am looking for a resultant object hierarchy that looks like:

Parent1 | +-- ChildrenOfParent1 | Parent2 | +-- ChildrenOfParent2

，依此类推.该列表最终将是IEnumerable.如果一个人遍历该列表，他们可以获得该列表中每个父级的.Children属性.

and so forth. The list would be end up being an IEnumerable. If one iterated over that list, they could get the .Children property of each parent in that list.

理想(我想我在这里做梦)是，结果列表的总体大小可能会受到限制.例如，如果对于总共33个(30个孩子+ 3个父母)实体有3个父母，每个父母有10个孩子，我可以将列表总数限制为某个任意值，例如13，在这种情况下将限制结果集设置为第一个父级及其所有子级，第二个父级仅具有其一个子级(总共13个实体).我猜所有这些都必须在代码中手动完成，这很令人失望，因为它可以很容易地在SQL中完成.

Ideally (and I'm dreaming here, I think), is that the overall size of the result list could be limited. For example, if there are three parents, each with 10 children, for a total of 33 (30 children + 3 parents) entities, I could limit the total list to some arbitrary value, say 13, and in this case that would limit the result set to the first parent, with all its children, and the second parent, with only one of its children (13 total entities). I'm guessing all of this would have to be done manually in code, which is disappointing because it can be done quite easily in SQL.

推荐答案

当您从db使用entityframewrok获取父项的查询时，将在单个查询中获取父项的字段.现在您得到了这样的结果集:

when you get a query from db using entityframewrok to fetch parents, parent's fields are fetched in single query. now you have a result set like this:

var parentsQuery = db.Parents.ToList();

然后，如果在父级上有foreign key，则entityframework在父级上创建navigation property可以访问corresponding entity(例如Child表).

then, if you have a foreign key on parent, entityframework creates a navigation property on parent to access to corresponding entity (for example Child table).

在这种情况下，当您使用已经获取的parent entities中的navigation property来获取childs时，entityframework将为每个父级创建与sql server的另一个连接.

in this case, when you use this navigation property from parent entities which already have been fetched, to get childs, entityframework creates another connection to sql server per parent.

例如，如果parentsQuery的计数为15，则通过以下查询entityframework创建15另一个连接，并获得15另一个query:

for example if count of parentsQueryis 15, by following query entityframework creates 15 another connection, and get 15 another query:

var Childs = parentsQuery.SelectMany(u => u.NavigationProperty_Childs).ToList();

在这种情况下，当您试图在单个查询中获取父项时，可以使用include来防止额外的连接获取带有parent的所有childs，例如:

in these cases you can use include to prevent extra connections to fetch all childs with its parent, when you are trying to get parents in single query, like this:

var ParentIncludeChildsQuery = db.Parents.Include("Childs").ToList();

然后按照Query的操作，entityframework不会创建任何连接，也不会再次获得任何查询:

then by following Query, entityframework doesn't create any connection and doesn't get any query again :

var Childs = ParentIncludeChildsQuery.SelectMany(u => u.NavigationProperty_Childs).ToList();

但是，您不能使用include创建任何条件和约束，可以使用Where，Join，Contains等在包含之后检查任何约束或条件，如下所示:

but, you can't create any condition and constraint using include, you can check any constraint or conditions after include using Where, Join, Contains and so forth, like this:

var Childs = ParentIncludeChildsQuery.SelectMany(u => u.NavigationProperty_Childs .Where(t => t.child_field1 = some_appropriate_value)).ToList();

，但通过此查询，之前已从database提取了所有子对象

but by this query, all child have been fetched from database before

实现等效sql查询的更好方法是:

the better way to acheieve equivalent sql query is :

var query = parent.Join(child, p => p.ID c => c.ParentID (p, c) => new { Parent = p, Child = c }) .Where(u => u.Child.child_field1 == some_appropriate_value) .OrderBy(u => u.Parent.parent_field1) .ThenBy(u => u.Child.child_field2) .ToList();