Linq - Group by multiple tables

Question

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

Orders Table:

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

Calls Table:

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

I want to group the two tables by date , Result:

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()};

how do i group both? thx!

Johannes Rudolph · Accepted Answer · 2010-08-08T18:29:46.167

9

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)};

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.

edited Aug 08 '10 at 18:29

answered Aug 08 '10 at 18:23

Johannes Rudolph

35,298
14
114
172

can you give me an example please? – dano Aug 08 '10 at 18:31
thx! , i was hoping i could use regular linq/sql commands , i wasn't thinking about concating the two tables. would you do the same if this was plain TSQL? – dano Aug 08 '10 at 18:35
In TSQL I'd use a full outer join, but this is not easy in L2S, I hope the solution I provided is simpler than writing a full outer join. Just search SO if you want more information on how to use a FOJ in Linq. – Johannes Rudolph Aug 08 '10 at 18:42
i searched , and it is complex. :( – dano Aug 08 '10 at 19:23
what's so bad about my solution? – Johannes Rudolph Aug 08 '10 at 20:13
Actually i will use it , thank for the efforts :) it just generates a really long/complex T-SQL query , i was hoping for something simpler , isn't my request a really common scenario? – dano Aug 09 '10 at 12:20

score 1 · Answer 2 · answered Aug 08 '10 at 19:11

1

You can use the Union method:

    var result =
        (from c in Calls group c by c.Date into cg select new {Date = cg.Key, Calls = cg.Count(), Orders = 0})
        .Union(from o in Orders group o by o.Date into og select new {Date = og.Key, Calls = 0, Orders = og.Count()})
        .GroupBy(x => x.Date)
        .Select(g => new {Date = g.Key, Calls = g.Max(r => r.Calls), Orders = g.Max(r => r.Orders)});

    foreach (var row in result)
    {
        Trace.WriteLine(row);
    }

This is very similar to the SQL you would write (a union of the two tables, and then an outer query to merge the results into a row)

answered Aug 08 '10 at 19:11

Shaun McCarthy

1,670
11
9

Yeah, but it is more likely to result in a single SQL statement. Best to put them both in and look at the debugger :) – Shaun McCarthy Aug 08 '10 at 19:30
1

im getting "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists" , think that if tables have different dates query will fail – dano Aug 08 '10 at 19:54

Linq - Group by multiple tables

2 Answers2

Linked