Group by multiple columns linq count nested row

Question

I have a table that needs to be filled with records as shown below. Each entry has a div, bus and dept and a status code that is either entered or progress, and is completed.

Div Bus Dept    Entered Progress    Finished
Com XYZ Credit  1   1   1
Con DBD Fraud   2   5   3
Con AAA Call C  5   55  2
Com BBB Auth    1   4   3
Com AAA AES 8   1   1
Con XYZ Collection  1   1   1

Con         12  3   5
Com         5   1   7

There is a column called status, which is either entered, or progress, or completed. I need a way to group by Div, Bus, Dept and count the status of each record, and then sum the codes on dept as shown above, as shown above. I tried the following linq request

var records = records.GroupBy(x=>new {x.Div, x.Bus,x.Dept,x.Status.Count()};

I'm not sure where else to get the request, I'm not too strong when grouping using linq.

Answer 1

records
    .GroupBy(x => new { x.Div, x.Bus, x.Dept })
    .Select(g => new 
        { 
            g.Key.Div, 
            g.Key.Bus, 
            g.Key.Dept, 
            Entered = g.Count(r => r.Status == Entered),
            Progress= g.Count(r => r.Status == Progress),
            Finished= g.Count(r => r.Status == Finished),
        });

LINQ SQL queries can help you become more comfortable with LINQ if you are coming from an SQL background.