Converting SQL CASE WHEN statement plus group statement into LINQ

Question

How can I write in fluent linq syntax the "case when" sql statement?

select QueueItem, COUNT(*) as [Count],
SUM(CASE WHEN Queued = 0 THEN 1 ELSE 0 END) AS [Sent],
SUM(CASE WHEN Queued = 1 THEN 1 ELSE 0 END) AS Queued,
SUM(CASE WHEN Success = 1 THEN 1 ELSE 0 END) AS Exported,
SUM(CASE WHEN Success = 0 THEN 1 ELSE 0 END) AS Failed
from ExportQueue x
group by QueueItem

Is there some program that can convert SQL to LINQ? LinqPad maybe?

It's suprisingly more complex in linq to do this. In the middle of something, but I'll pop back in about 30mins and write it out if you don't have a solution yet. The key is to do your case into an anonymous result, then your groupby & sum after it. — Gats, Jan 03 '12 at 11:16
Thanks. The intention of the query if it is not clear is to group by queue item (sale/credit/purchase/etc) and report how many are sent, how many remain in the queue, how many were successful and how many failed — JK., Jan 03 '12 at 11:24
Yep got that. I want to help as I like the way you use Sums and cases. It's an old trick of mine too that I'm surprised people don't use more as it's very fast. You'll need to bear in mind that linq will obviously do this much slower too. I still prefer stored procs for complex aggregates as left join cases can get out of control very easily but this one should be fine. — Gats, Jan 03 '12 at 11:29

Gats · Accepted Answer · 2012-01-03T11:53:44.210

4

Ok, something like this. I'll need a bit of info to be sure though

Is Queued a bit? That makes a difference in linq where it doesn't in SQL. I also don't know your context names but you should get the idea.

var query = Context.ExportQueues.Select(x => new { 
  QueueItem = x.QueueItem, 
  Sent = !x.Queued ? 1 : 0,
  Queued = x.Queued ? 1 : 0,
  Exported = x.Success ? 1 : 0,
  Failed = !x.Success ? 1 : 0 })
.GroupBy(x => x.QueueItem)
.Select(g => new { 
  QueueItem = g.Key,
  Sent = g.Sum(x => x.Sent),
  Queued = g.Sum(x => x.Queued),
  Exported = g.Sum(x => x.Exported),
  Failed = g.Sum(x => x.Failed)
}).ToList();

EDIT You could also combine these by doing the case on the fly in the query. I always tend to write it out as above first as I work through it though as more complex aggregates can be a bit hard to debug if there's errors:

var query = Context.ExportQueues
.GroupBy(x => x.QueueItem)
.Select(g => new { 
  QueueItem = g.Key,
  Sent = g.Sum(x => !x.Queued ? 1 : 0),
  Queued = g.Sum(x => x.Queued ? 1 : 0),
  Exported = g.Sum(x => x.Success ? 1 : 0),
  Failed = g.Sum(x => !x.Success ? 1 : 0 )
}).ToList();

edited Jan 03 '12 at 11:53

answered Jan 03 '12 at 11:27

Gats

3,452
19
20

They are actually nullable bit fields so I changed your `Sent = !x.Queued ? 1 : 0` into `Sent = x.Queued != true ? 1 : 0` etc. But the grouping is not working, it is returning one group for every row. – JK. Jan 03 '12 at 11:37
I made a mistake on the QueueItem = x. Edited version should be better. QueueItem = x.QueueItem, – Gats Jan 03 '12 at 11:38
Yeah I just came back to say that x should have been x.QueueItem :) – JK. Jan 03 '12 at 11:44
1

That last line should be `Failed = g.Sum(x => !x.Success ? 1 : 0 )` to count the number of fails, `!x.Success ? 0 : 1` counts the number of non failures ie successes :) – JK. Jan 03 '12 at 11:52
hehe got that backwards. Linq aggregates written blind in 5mins without vs can be trick :D Edited it the way it should be. – Gats Jan 03 '12 at 11:54

score 2 · Answer 2 · answered Jan 03 '12 at 11:40

2

As an alternative to Gatts solution you can do something like

var query = Context.ExportQueues.
.GroupBy(x => x.QueueItem)
.Select(g => new { 
  QueueItem = g.Key,
  Sent = g.Count(x=>!x.Queued),
  Queued = g.Count(x => x.Queued),
  Exported = g.Count(x => x.Success),
  Failed = g.Count(x => !x.Failed)
}).ToList();

answered Jan 03 '12 at 11:40

Muhammad Adeel Zahid

17,474
14
90
155

This should work too, although I think it might end up generating more subqueries. I'm going to try them both and profile them when I get time as it's an interesting one. – Gats Jan 03 '12 at 11:41

score 0 · Answer 3 · answered Jan 03 '12 at 11:34

This is actually quite long-winded to write using LINQ. What you will need to do is to group first and then use lambda expressions to handle the aggregation. So something like:

from eq in ExportQueue
group eq by new {
    eq.QueueItem
} into temp
select new {
    temp.Key.QueueItem,
    Agg1 = temp.Sum(n => n.Queued == 0 ? 1 : 0),
    Agg2 = temp.Sum(n => n.Queued == 1 ? 1 : 0)
}

And so on, LinqPad will be really useful in trying to get this to work.

Converting SQL CASE WHEN statement plus group statement into LINQ

3 Answers3