LINQ学习笔记:Join和Group Join(2)
GroupJoin
GroupJoin功能与Join类似, 只不过GroupJoin返回的结果集是一个层级结构, 而不是一个扁平的结构. 对于复合语法来说, GroupJoin和Join是一样的, 只不过它通常会跟着一个into关键字:
1: var query =
2:
3: from c in customers
4:
5: join p in purchases on c.ID equals p.CustomerID
6:
7: into custPurchases
8:
9: select custPurchases;
只有当into语句出现在一个join语句之后它才会被翻译成GroupJoin. 如果是跟在一个select或者group语句之后, 则意味着是查询延续. 这两个用法是非常不同的, 但它们有一个共同的特点: 两者都引入了新的查询变量
上述查询结果是一个序列的序列, 我们可以像下面这样来枚举它:
1: foreach (IEnumerable purchaseSequence in query)
2:
3: foreach (Purchase p in purchaseSequence)
4:
5: Console.WriteLine (p.Description);
另外我们也可以在返回结果中来选取外部的查询变量:
1: from c in customers
2:
3: join p in purchases on c.ID equals p.CustomerID
4:
5: into custPurchases
6:
7: select new { CustName = c.Name, custPurchases }; //c可以被引用到
使用下面的select子查询也可以得到同样的结果:
1: from c in customers
2:
3: select new
4:
5: {
6:
7: CustName = c.Name,
8:
9: custPurchases =
10:
11: purchases.Where (p => c.ID == p.CustomerID)
12:
13: };
默认情况下, GroupJoin相当于一个左外连接. 为了得到一个inner join, 我们必须在custPurcahse上面做一层过滤:
1: from c in customers join p in purchases
2:
3: on c.ID equals p.CustomerID
4:
5: into custPurchases
6:
7: where custPurchases.Any( )
8:
9: select ...
在group-join之后的into操作作用于内部元素的子序列, 而不是每一个单独的子元素, 因此如果你要过滤每一个单独的采购单, 你必须在joining之前调用Where
1: from c in customers
2:
3: join p in purchases.Where (p2 => p2.Price > 1000)
4:
5: on c.ID equals p.CustomerID
6:
7: into custPurchases ...
扁平的Outer Joins
当你想得到一个Outer Join同时输出扁平的结果集的时候, 这会是一个两难的境地. GroupJoin会提供给你Outer Join; Join提供给你扁平的结果集. 因此, 解决方案是先调用GroupJoin, 然后在每一个子序列上面调用DefaultIfEmpty, 最后调用SelectMany:
1: from c in customers
2:
3: join p in purchases on c.ID equals p.CustomerID
4:
5: into custPurchases
6:
7: from cp in custPurchases.DefaultIfEmpty( )
8:
9: select new
10:
11: {
12:
13: CustName = c.Name,
14:
15: Price = cp == null ? (decimal?) null : cp.Price
16:
17: };
当Purchases为空的时候DefaultIfEmpty会得到一个null值. 第二个from语句被翻译成SelectMany, 它扩展和压扁了所有的purchase, 并将它们联合在一起形成purchase元素所属的单一序列