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

Slava Utesinov · Answer 1 · 2016-05-26T14:35:06.900

3

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();

edited May 26 '16 at 14:35

answered May 26 '16 at 14:29

Slava Utesinov

13,410
2
19
26

This one is really good, however I want to get output as a list so that I can source it to a DataGrid. When I do this, I get the following columns BugState, Details and Total. Details column is shown as collection. I need a way in such a way that the grid shows data as I have mentioned in the question. – Kannan Suresh May 26 '16 at 15:37
Are you want get result, where all rows will have separate properties for each column, instead of one property as dynamic list(dictionary) for all columns? At this case, you, unfortunately, should to hardcode something, because your problem is classic **PIVOT** problem, where as you know, columns also must be enumerated manually. You can try to create class in runtime with needed set of properties and then via reflection set his properties values. – Slava Utesinov May 26 '16 at 15:58

Dmitriy Zapevalov · Accepted Answer · 2016-05-26T19:05:18.143

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:

Where can you see hardcoded values? This `test` array is just for output. You should write only part starting from `var grouped =` — Dmitriy Zapevalov, May 26 '16 at 14:53
Oh I am sorry, on quick look I thought you had hard coded. I will have a look and let you know. — Kannan Suresh, May 26 '16 at 14:59
This works good, however I want to get output as a list so that I can source it to a DataGrid. — Kannan Suresh, May 26 '16 at 15:34
Oh. Ok. I've made research about DataGridView data binding. See my update. — Dmitriy Zapevalov, May 26 '16 at 19:06
Yes, see my implementation below the question. Thanks a lot man. — Kannan Suresh, May 27 '16 at 07:37

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

2 Answers2