Group by multiple columns linq count nested row

Question

I have a table that needs to be populated with records as in the following. Each record has a div, bus, and dept, and a status code that is either Entered,Progress, and Finished.

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, Progress, or Finished. I need a way to group by Div, Bus, Dept, and count the status of each record and then summarize the codes per dept as show above as shown above. I've tried the following linq query

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

I'm not sure where else to take the query,I'm not too strong on 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),
        });

SQL queries in LINQ may help you become more comfortable with LINQ if you're coming from SQL background.