Linq-to-Entities Join vs GroupJoin [英] Linq-to-Entities Join vs GroupJoin
问题描述
有人可以解释一下 GroupJoin()
是什么吗?
Can someone please explain what a GroupJoin()
is?
它与普通的 Join()
有什么不同?
How is it different from a regular Join()
?
是否常用?
是否只针对方法语法?查询语法呢?(一个 c# 代码示例会很好)
Is it only for method syntax? What about query syntax? (A c# code example would be nice)
推荐答案
行为
假设您有两个列表:
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
当您加入
Id
字段上的两个列表时,结果将是:
When you Join
the two lists on the Id
field the result will be:
Value ChildValue
A a1
A a2
A a3
B b1
B b2
当您GroupJoin
Id
字段上的两个列表时,结果将是:
When you GroupJoin
the two lists on the Id
field the result will be:
Value ChildValues
A [a1, a2, a3]
B [b1, b2]
C []
所以 Join
产生父值和子值的平面(表格)结果.GroupJoin
在第一个列表中生成一个条目列表,每个条目在第二个列表中都有一组连接的条目.
So Join
produces a flat (tabular) result of parent and child values.
GroupJoin
produces a list of entries in the first list, each with a group of joined entries in the second list.
这就是为什么 Join
与 SQL 中的 INNER JOIN
等价的原因:没有 C
的条目.虽然 GroupJoin
相当于 OUTER JOIN
:C
在结果集中,但相关条目的列表为空(在 SQL 结果中)设置将有一行 C - null
).
That's why Join
is the equivalent of INNER JOIN
in SQL: there are no entries for C
. While GroupJoin
is the equivalent of OUTER JOIN
: C
is in the result set, but with an empty list of related entries (in an SQL result set there would be a row C - null
).
所以让这两个列表分别为 IEnumerable
和 IEnumerable
.(如果是 Linq to Entities:IQueryable
).
So let the two lists be IEnumerable<Parent>
and IEnumerable<Child>
respectively. (In case of Linq to Entities: IQueryable<T>
).
Join
语法是
Join
syntax would be
from p in Parent
join c in Child on p.Id equals c.Id
select new { p.Value, c.ChildValue }
返回一个 IEnumerable
,其中 X 是具有两个属性的匿名类型,Value
和 ChildValue
.此查询语法使用 Join
方法下引擎盖.
returning an IEnumerable<X>
where X is an anonymous type with two properties, Value
and ChildValue
. This query syntax uses the Join
method under the hood.
GroupJoin
语法为
GroupJoin
syntax would be
from p in Parent
join c in Child on p.Id equals c.Id into g
select new { Parent = p, Children = g }
返回一个 IEnumerable
,其中 Y 是一种匿名类型,包含一个 Parent
类型的属性和一个 IEnumerable
代码>.此查询语法使用 GroupJoin
方法下引擎盖.
returning an IEnumerable<Y>
where Y is an anonymous type consisting of one property of type Parent
and a property of type IEnumerable<Child>
. This query syntax uses the GroupJoin
method under the hood.
我们可以在后一个查询中执行 select g
,这将选择一个 IEnumerable
,比如说一个列表列表.在许多情况下,包含父项的选择更有用.
We could just do select g
in 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.
如前所述,声明...
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 }
结果类似
Value Child
A a1
A a2
A a3
B b1
B b2
C (null)
注意 range 变量 c
在上面的语句中被重用了.这样做,任何 join
语句都可以简单地转换为 外连接
,方法是在 g.DefaultIfEmpty() 中将 的等价物添加到 g from c到现有的
join
语句.
Note that the range variable c
is reused in the above statement. Doing this, any join
statement can simply be converted to an outer join
by adding the equivalent of into g from c in g.DefaultIfEmpty()
to an existing join
statement.
这是查询(或综合)语法的亮点.方法(或流利的)语法显示了真正发生了什么,但很难写:
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 } )
所以 LINQ 中扁平的 outer join
是一个 GroupJoin
,被 SelectMany
扁平化.
So a flat outer join
in LINQ is a GroupJoin
, flattened by SelectMany
.
假设父母名单有点长.某些 UI 以固定顺序生成选定父项的列表作为 Id
值.让我们使用:
Suppose the list of parents is a bit longer. Some UI produces a list of selected parents as Id
values in a fixed order. Let's use:
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));
... parents
的顺序将决定结果.如果父母按 Id
排序,结果将是父母 2、3、4、7.不好.但是,我们也可以使用 join
来过滤列表.并且通过使用 ids
作为第一个列表,顺序将被保留:
... the order of parents
will 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 join
to filter the list. And by using ids
as first list, the order will be preserved:
from id in ids
join p in parents on id equals p.Id
select p
结果是父母 3、7、2、4.
The result is parents 3, 7, 2, 4.
这篇关于Linq-to-Entities Join vs GroupJoin的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!