LINQ的 - 组由多个表 [英] Linq - Group by multiple tables

问题描述

使用LINQ到SQL我如何组的以下2个表。

Using Linq to Sql how do i group the following 2 tables.

订单表

CustomerID | Name   |Date            
1          | order1 | 2010-01-01  
2          | order2 | 2010-01-01
2          | order3 | 2010-04-01

调用表

CustomerID | Name   |Date            
1          | call1 | 2010-01-01  
3          | call2 | 2010-06-01
2          | call3 | 2010-05-01

我要组两个表按日期，结果

Date       | Orders | Calls
2010-01-01 | 2      | 1
2010-04-01 | 1      | 0
2010-05-01 | 0      | 1
2010-06-01 | 0      | 1

我知道如何组一个表，

i know how to group a single table ,

from o in Orders        
group o by o.Date.Date into og
select new {Date = og.Key,Orders= og.Count()};

如何组中的两个？
THX！

推荐答案

由于两个表似乎有一个类似的结构，我建议那些两套串联两个突出成等价的形式，然后组。

Since both tables seem to have a similar structure I'd recommend projecting both into an equivalent form and then group on the concatenation of those two sets.

var orders = from o in Orders 
            select new { IsOrder = true, o.Date };
var calls = from c in Calls 
            select new { IsOrder = false, c.Date };

var result = from x in orders.Concat(calls)        
            group x by x.Date into og
            select new {Date = og.Key, Orders= og.Count(o=>o.IsOrder), Calls = og.Count(c=>!c.IsTrue)};

由于LINQ2SQL的懒惰天性这实际上可能减少到一个单一的查询。在性能的利益我会确保这是不是来自地狱的查询。

Due to the lazy nature of Linq2Sql this might actually be reduced to a single query. In the interest of performance I would make sure this is not a query from hell.

这篇关于LINQ的 - 组由多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！