Group by Aggregate functions in LINQ

Question

I have a SQL query of the following type and I was trying to fetch the data in my C# application using LINQ

select 
sum(CASE when outcome = 0 then samplingweight else 0 end) tpcount, 
sum(CASE when outcome = 1 then samplingweight else 0 end) fpcount, 
sum(CASE when outcome = 2 then samplingweight else 0 end) mismatch, 
sum(CASE when outcome = 3 then samplingweight else 0 end) fncount, 
sum(CASE when outcome = 4 then samplingweight else 0 end) tncount, 
sum(samplingweight) totalweight, 
__date, 
scenario from MyTable
where __date = '30-Dec-16 12:00:00 AM' and scenario = 'parcels'
group by __date, scenario

NOTE: samplingweight is a double value in DB.

I was trying and was able to reach to the below state

from pr in this.context.ComputedPrSet
where
    pr.Date.Equals(date) && pr.Scenario.Equals(scenario)
group pr by new { pr.Date, pr.Scenario }
into grp
select new
           {
               grp.Key.Date,
               grp.Key.Scenario,
               TruePositiveCount = grp.Sum(x => x.SamplingWeight) //I am stuck here. Not sure how to aggregate the properties 
           };

NOTE: date and scenario in the above LINQ query are taken as parameters to the executing function.

Any ideas or tips on how to proceed?

Answer 1

You've got two easily visible options:

• Filter grp with a .Where() clause before summing
• Test x.outcome for your preferred value in the .Sum()

I consider the former more readable, while the latter more closely matches your SQL. They may translate to SQL differently, depending on your provider. If performance is an issue, make sure to evaluate the resulting SQL.

from pr in this.context.ComputedPrSet
where
    pr.Date.Equals(date) && pr.Scenario.Equals(scenario)
group pr by new { pr.Date, pr.Scenario }
into grp
select new
           {
               grp.Key.Date,
               grp.Key.Scenario,
               TruePositiveCount = grp.Where(x => x.outcome == 0).Sum(x => x.SamplingWeight),
               FPCount = grp.Sum(x => x.outcome == 1 ? x.SamplingWeight : 0),
           };

A third option is to group by Date, Scenario, and Outcome in the DB, then re-Select() into the object you want in code, like so:

this.Context.ComputedPrSet
    .Where(pr => pr.Date == date && pr.Scenario == scenario)
    .GroupBy(pr => new { pr.Date, pr.Scenario, pr.Outcome })
    .Select(grp => new {
        grp.Key.Date,
        grp.Key.Scenario,
        grp.Key.Outcome,
        Count = grp.Sum(pr => pr.SamplingWeight)
    })
    .AsEnumerable()
    // .AsEnumerable() will "close" the query in LINQ.
    // Further operations are done via LINQ to Objects after fetching the prior results from your DB
    .GroupBy(e => new { e.Date, e.Scenario })
    .Select(g => new {
        g.Key.Date,
        g.Key.Scenario,
        TruePositiveCount = g.FirstOrDefault(e => e.Outcome == 0),
        FPCount = g.FirstOrDefault(e => e.Outcome == 1),
    });

Answer 2

You can use ?: operator to do CASE WHEN part.

from pr in this.context.ComputedPrSet
where
    pr.Date.Equals(date) && pr.Scenario.Equals(scenario)
group pr by new { pr.Date, pr.Scenario }
into grp
select new
{
    grp.Key.Date,
    grp.Key.Scenario,
    TruePositiveCount = grp.Sum(x => x.Outcome == 0?x.SamplingWeight:0)
};