C# Linq to Entities 加入 vs groupjoin

Linq to Entities join vs groupjoin

提问by duyn9uyen

I have web searched but I still cant find a simple answer. Can someone please explain (in simple English) what a GroupJoinis? How is it different from a regular inner Join? Is it commonly used? Is it only for method syntax? What about query syntax? A c# code example would be nice.

我在网上搜索过，但仍然找不到简单的答案。有人可以解释一下（用简单的英语）什么是 aGroupJoin吗？它与常规的内部Join有何不同？是否常用？它仅用于方法语法吗？查询语法呢？一个 c# 代码示例会很好。

采纳答案by Gert Arnold

Behaviour

行为

Suppose you have two lists:

假设您有两个列表：

Id  Value
1   A
2   B
3   C

Id  ChildValue
1   a1
1   a2
1   a3
2   b1
2   b2

When you Jointhe two lists on the Idfield the result will be:

当您Join在Id字段上列出两个列表时，结果将是：

Value ChildValue
A     a1
A     a2
A     a3
B     b1
B     b2

When you GroupJointhe two lists on the Idfield the result will be:

当您GroupJoin在Id字段上列出两个列表时，结果将是：

Value  ChildValues
A      [a1, a2, a3]
B      [b1, b2]
C      []

So Joinproduces a flat (tabular) result of parent and child values.
GroupJoinproduces a list of entries in the first list, each with a group of joined entries in the second list.

因此Join产生父值和子值的平面（表格）结果。
GroupJoin在第一个列表中生成一个条目列表，每个条目在第二个列表中都有一组连接的条目。

That's why Joinis the equivalent of INNER JOINin SQL: there are no entries for C. While GroupJoinis the equivalent of OUTER JOIN: Cis in the result set, but with an empty list of related entries (in an SQL result set there would be a row C - null).

这就是为什么在 SQL 中Join是等价的INNER JOIN：没有C. WhileGroupJoin相当于OUTER JOIN:C在结果集中，但相关条目的列表为空（在 SQL 结果集中会有一行C - null）。

Syntax

句法

So let the two lists be IEnumerable<Parent>and IEnumerable<Child>respectively. (In case of Linq to Entities: IQueryable<T>).

所以让这两个列表分别是IEnumerable<Parent>和IEnumerable<Child>。（在 Linq 到实体的情况下：）IQueryable<T>。

Joinsyntax would be

Join语法是

from p in Parent
join c in Child on p.Id equals c.Id
select new { p.Value, c.ChildValue }

returning an IEnumerable<X>where X is an anonymous type with two properties, Valueand ChildValue. This query syntax uses the Joinmethod under the hood.

返回一个IEnumerable<X>where X 是具有两个属性的匿名类型，Value并且ChildValue. 此查询语法使用Join了引擎盖下的方法。

GroupJoinsyntax would be

GroupJoin语法是

from p in Parent
join c in Child on p.Id equals c.Id into g
select new { Parent = p, Children = g }

returning an IEnumerable<Y>where Y is an anonymous type consisting of one property of type Parentand a property of type IEnumerable<Child>. This query syntax uses the GroupJoinmethod under the hood.

返回IEnumerable<Y>其中 Y 是一个匿名类型，由一个 typeParent属性和一个 type 属性组成IEnumerable<Child>。此查询语法使用GroupJoin了引擎盖下的方法。

We could just do select gin the latter query, which would select an IEnumerable<IEnumerable<Child>>, say a list of lists. In many cases the select with the parent included is more useful.

我们可以只select g在后一个查询中做，它会选择一个IEnumerable<IEnumerable<Child>>，比如一个列表列表。在许多情况下，包含父项的选择更有用。

Some use cases

一些用例

1. Producing a flat outer join.

1. 制作平面外连接。

As said, the statement ...

如前所述，声明...

from p in Parent
join c in Child on p.Id equals c.Id into g
select new { Parent = p, Children = g }

... produces a list of parents with child groups. This can be turned into a flat list of parent-child pairs by two small additions:

... 生成具有子组的父母列表。这可以通过两个小的添加变成父子对的平面列表：

from p in parents
join c in children on p.Id equals c.Id into g // <= into
from c in g.DefaultIfEmpty()               // <= flattens the groups
select new { Parent = p.Value, Child = c?.ChildValue }

The result is similar to

结果类似于

Value Child
A     a1
A     a2
A     a3
B     b1
B     b2
C     (null)

Note that the range variablecis reused in the above statement. Doing this, any joinstatement can simply be converted to an outer joinby adding the equivalent of into g from c in g.DefaultIfEmpty()to an existing joinstatement.

请注意，上面的语句中重复使用了范围变量c。这样做后，任何join语句都可以outer join通过向into g from c in g.DefaultIfEmpty()现有join语句添加等效项来简单地转换为 an 。

This is where query (or comprehensive) syntax shines. Method (or fluent) syntax shows what really happens, but it's hard to write:

这就是查询（或综合）语法的亮点。方法（或流利）语法显示了真正发生的事情，但很难编写：

parents.GroupJoin(children, p => p.Id, c => c.Id, (p, c) => new { p, c })
       .SelectMany(x => x.c.DefaultIfEmpty(), (x,c) => new { x.p.Value, c?.ChildValue } )

So a flat outer joinin LINQ is a GroupJoin, flattened by SelectMany.

所以outer joinLINQ 中的平面是GroupJoin，被SelectMany.

2. Preserving order

2. 保持秩序

Suppose the list of parents is a bit longer. Some UI produces a list of selected parents as Idvalues in a fixed order. Let's use:

假设父母名单有点长。某些 UIId以固定顺序生成选定父项的列表作为值。让我们使用：

var ids = new[] { 3,7,2,4 };

Now the selected parents must be filtered from the parents list in this exact order.

现在必须按照这个确切的顺序从父列表中过滤选定的父。

If we do ...

如果我们...

var result = parents.Where(p => ids.Contains(p.Id));

... the order of parentswill determine the result. If the parents are ordered by Id, the result will be parents 2, 3, 4, 7. Not good. However, we can also use jointo filter the list. And by using idsas first list, the order will be preserved:

...parents将决定结果的顺序。如果父母按 排序Id，结果将是父母 2、3、4、7。不好。但是，我们也可以使用join过滤列表。并通过使用ids作为第一个列表，顺序将被保留：

from id in ids
join p in parents on id equals p.Id
select p

The result is parents 3, 7, 2, 4.

结果是父母 3、7、2、4。

回答by MarcinJuraszek

According to eduLINQ:

根据eduLINQ：

The best way to get to grips with what GroupJoin does is to think of Join. There, the overall idea was that we looked through the "outer" input sequence, found all the matching items from the "inner" sequence (based on a key projection on each sequence) and then yielded pairs of matching elements. GroupJoin is similar, except that instead of yielding pairs of elements, it yields a single result for each "outer" item based on that item and the sequence of matching "inner" items.

掌握 GroupJoin 功能的最佳方法是考虑 Join。在那里，总体思路是我们查看“外部”输入序列，从“内部”序列中找到所有匹配项（基于每个序列的关键投影），然后生成匹配元素对。GroupJoin 是类似的，除了它不是生成元素对，而是根据该项目和匹配的“内部”项目的序列为每个“外部”项目生成单个结果。

The only difference is in return statement:

唯一的区别在于 return 语句：

Join:

加入：

var lookup = inner.ToLookup(innerKeySelector, comparer); 
foreach (var outerElement in outer) 
{ 
    var key = outerKeySelector(outerElement); 
    foreach (var innerElement in lookup[key]) 
    { 
        yield return resultSelector(outerElement, innerElement); 
    } 
} 

GroupJoin:

群组加入：

var lookup = inner.ToLookup(innerKeySelector, comparer); 
foreach (var outerElement in outer) 
{ 
    var key = outerKeySelector(outerElement); 
    yield return resultSelector(outerElement, lookup[key]); 
} 

Read more here:

在此处阅读更多信息：

• Reimplementing LINQ to Objects: Part 19 - Join

• Reimplementing LINQ to Objects: Part 22 - GroupJoin

• 重新实现 LINQ to Objects：第 19 部分 - 加入

• 重新实现 LINQ to Objects：第 22 部分 - GroupJoin