LINQ query on an object list to get distribution based on multiple fields

Question

I have a list of bugs MyBugList created using the following class

internal class BugDetails
{
    public int Id { get; set; }
    public string State { get; set; }
    public string Severity { get; set; }
}

I wanted to group these bugs based on State and Severity. I used the following code to achieve it.

var BugListGroup = (from bug in MyBugList
                             group bug by new
                             {
                                 bug.State,
                                 bug.Severity
                             } into grp
                             select new
                             {
                                 BugState = grp.Key.State,
                                 BugSeverity = grp.Key.Severity,
                                 BugCount = grp.Count()
                             }).OrderBy(x=> x.BugState).ToList();

This linq query gives me output like the following

Closed      Critical    40
Active      Critical    167
Closed      Medium      819
Closed      Low         323
Resolved    Medium      61
Resolved    Low         11
Closed      High        132
Active      Low         17
Active      Medium      88
Active      High        38
Resolved    High        4
Resolved    Critical    22
Deferred    High        11

However I would like to get an output like below

            Critical    High    Medium  Total
Closed      3           4       5       12
Active      5           4       5       14
Resolved    6           4       5       15
Deferred    1           4       5       10
Total       15          16      20      51

Is it possible to get this via a LINQ query on MyBugList or on BugListGroup

I would like to get the output as a list so that I can be make it source a data grid.

Note: State and Severity values are dynamic and cannot be hard coded

Below is my implementation with the help of answer provided by Dmitriy Zapevalov

private void button1_Click(object sender, EventArgs e)
{
    var grouped = MyBugList.GroupBy(b => b.State).Select(stateGrp => stateGrp.GroupBy(b => b.Severity));

    //Setting DataGrid properties
    dataGridBug.Rows.Clear();
    dataGridBug.Columns.Clear();
    dataGridBug.DefaultCellStyle.NullValue = "0";
    dataGridBug.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

    //Declaring DataGrid Styles
    var gridBackColor = Color.AliceBlue;
    var gridFontStyle = new Font(Font, FontStyle.Bold | FontStyle.Italic);

    //Declaring column and row Ids
    const string stateColumnId = "State";
    const string totalColumnId = "Total";
    const string totalRowId = "Total";

    //Adding first column
    dataGridBug.Columns.Add(stateColumnId, stateColumnId);
    dataGridBug.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;

    //Adding other columns
    foreach (var strSeverity in MyBugList.Select(b => b.Severity).Distinct())
    {
        dataGridBug.Columns.Add(strSeverity, strSeverity);
    }

    //Adding Total Column
    var totColPos = dataGridBug.Columns.Add(totalColumnId, totalColumnId);
    var totCol = dataGridBug.Columns[totColPos];

    //Adding data to grid
    foreach (var state in grouped)
    {
        var nRow = dataGridBug.Rows.Add();
        var severities = state as IList<IGrouping<string, BugDetails>> ?? state.ToList();
        dataGridBug.Rows[nRow].Cells[0].Value = severities.First().First().State;
        var sevCount = 0;
        foreach (var severity in severities)
        {
            dataGridBug.Rows[nRow].Cells[severity.Key].Value = severity.Count();
            sevCount += severity.Count();
        }
        dataGridBug.Rows[nRow].Cells[totalColumnId].Value = sevCount;
    }


    //Adding total row
    var totRowPos = dataGridBug.Rows.Add(totalRowId);
    var totRow = dataGridBug.Rows[totRowPos];

    //Adding data to total row
    for (var c = 1; c < dataGridBug.ColumnCount; c++)
    {
        var sum = 0;
        for (var i = 0; i < dataGridBug.Rows.Count; ++i)
        {
            sum += Convert.ToInt32(dataGridBug.Rows[i].Cells[c].Value);
        }
        dataGridBug.Rows[totRowPos].Cells[c].Value = sum;
    }

    //Styling total column
    totCol.DefaultCellStyle.BackColor = gridBackColor;
    totCol.DefaultCellStyle.Font = gridFontStyle;

    //Styling total row
    totRow.DefaultCellStyle.BackColor = gridBackColor;
    totRow.DefaultCellStyle.Font = gridFontStyle;
}

Output in the data grid will look like

Answer 1

You can do this (it is dynamic solution, where you not need to hardcode anything). Details property of BugModel class contains all columns as Critical, High and so on:

public class BugModel
{
    public string BugState { get; set; }
    public Dictionary<string, int> Details { get; set; }
    public int Total { get { return Details.Sum(x => x.Value); } }
}

Solution:

var result = (from bug in BugListGroup
              group bug by bug.BugState into sub
              select new BugModel
              {
                  BugState = sub.Key,
                  Details = sub.GroupBy(x => x.BugSeverity)
                             .ToDictionary(x => x.Key, x => x.Sum(y => y.BugCount))
              }).ToList();

Answer 2

I've done double grouping:

class Program
{
    internal class BugDetails
    {
        public int Id { get; set; }
        public string State { get; set; }
        public string Severity { get; set; }
    }
    static void Main(string[] args)
    {
        var MyBugList = new BugDetails[]
        {
            new BugDetails() { Id = 1, State = "Active", Severity = "Critical" },
            new BugDetails() { Id = 1, State = "Closed", Severity = "Critical" },
            new BugDetails() { Id = 1, State = "Closed", Severity = "Critical" },
            new BugDetails() { Id = 1, State = "Closed", Severity = "Critical" },
            new BugDetails() { Id = 1, State = "Resolved", Severity = "Critical" },
            new BugDetails() { Id = 1, State = "Resolved", Severity = "Critical" },
            new BugDetails() { Id = 1, State = "Resolved", Severity = "Critical" },

            new BugDetails() { Id = 1, State = "Active", Severity = "Medium" },
            new BugDetails() { Id = 1, State = "Active", Severity = "Medium" },
            new BugDetails() { Id = 1, State = "Closed", Severity = "Medium" },
            new BugDetails() { Id = 1, State = "Closed", Severity = "Medium" },
            new BugDetails() { Id = 1, State = "Resolved", Severity = "Medium" },
            new BugDetails() { Id = 1, State = "Resolved", Severity = "Medium" },
            new BugDetails() { Id = 1, State = "Resolved", Severity = "Medium" },

            new BugDetails() { Id = 1, State = "Active", Severity = "High" },
            new BugDetails() { Id = 1, State = "Active", Severity = "High" },
            new BugDetails() { Id = 1, State = "Closed", Severity = "High" },
            new BugDetails() { Id = 1, State = "Closed", Severity = "High" },
            new BugDetails() { Id = 1, State = "Closed", Severity = "High" },
            new BugDetails() { Id = 1, State = "Closed", Severity = "High" },
            new BugDetails() { Id = 1, State = "Closed", Severity = "High" },
        };

        var grouped = MyBugList.GroupBy(b => b.State).
            Select(stateGrp => stateGrp.GroupBy(b => b.Severity));

        foreach (var state in grouped)
        {
            Console.Write("{0}: ", state.First().First().State);
            foreach (var severity in state)
            {
                Console.Write("{0}={1} ", severity.Key, severity.Count());
            }
            Console.WriteLine();
        }
    }
}

Output:

Active: Critical=1 Medium=2 High=2
Closed: Critical=3 Medium=2 High=5
Resolved: Critical=3 Medium=3

If you want to display data with DataGridView In that case you can create type dynamically with your own Properties set. But this way is to complicated. The most simple (and more performance) way is to fill DataGridView manually:

private void button1_Click(object sender, EventArgs e)
{
    var grouped = MyBugList.GroupBy(b => b.State).
        Select(stateGrp => stateGrp.GroupBy(b => b.Severity));

    dataGridView1.Columns.Add("State", "State");
    foreach (var strSeverity in MyBugList.Select(b => b.Severity).Distinct())
        dataGridView1.Columns.Add(strSeverity, strSeverity);

    foreach (var state in grouped)
    {
        int nRow = dataGridView1.Rows.Add();
        dataGridView1.Rows[nRow].Cells[0].Value = state.First().First().State;
        foreach (var severity in state)
        {
            dataGridView1.Rows[nRow].Cells[severity.Key].Value = severity.Count();
        }
    }
}

The result: