基于多个字段，在对象列表上进行LINQ查询以获取分布

Question

我有一个错误列表 MyBugList，它是使用以下类创建的。

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

我想根据“状态”和“严重性”对这些错误进行分组。我使用了以下代码来实现它。

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

这个Linq查询会输出以下内容。

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

然而，我希望得到以下输出

            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

能否通过对 MyBugList 或 BugListGroup 进行 LINQ 查询来获得此内容？

我想要将输出作为列表获取，以便可以将其用作数据网格的源。

注意：状态和严重性值是动态的，不能硬编码。

以下是我根据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;
}

数据网格中的输出将如下所示：

- Kannan Suresh

Answer 1

你可以使用这种动态解决方案，不需要硬编码任何内容。 BugModel 类的 Details 属性包含所有列，例如 Critical ， High 等：

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

解决方案:

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

- Slava Utesinov

---

这个很不错，但是我想要将输出作为列表，以便我可以将其源到DataGrid。当我这样做时，我得到了以下列：BugState、Details和Total。Details列显示为集合。我需要一种方法，使得网格以我在问题中提到的方式显示数据。 - Kannan Suresh

你是否想要获得结果，其中每一行都有单独的属性来表示每一列，而不是所有列都作为动态列表（字典）的一个属性？在这种情况下，不幸的是，你需要硬编码一些东西，因为你的问题是经典的PIVOT问题，正如你所知，列也必须手动枚举。你可以尝试在运行时创建一个具有所需属性集的类，然后通过反射设置其属性值。 - Slava Utesinov

Answer 2

我已经进行了双重分组：

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

输出：

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

如果您想使用DataGridView显示数据，那么您可以创建一个动态类型，并设置自己的属性集。但是这种方法太过复杂。 最简单（也更高效）的方法是手动填充DataGridView：

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

结果如下：

- Dmitriy Zapevalov

---

你可以在哪里看到硬编码的值？这个 test 数组仅用于输出。你应该只写从 var grouped = 开始的部分。 - Dmitriy Zapevalov

哦，对不起，我一眼看错了，以为你已经硬编码了。我会仔细看一下并让你知道的。 - Kannan Suresh

这个很好用，但是我想把输出作为一个列表，这样我就可以将它源到一个数据网格中。 - Kannan Suresh

哦。好的。我已经就DataGridView数据绑定进行了研究。请查看我的更新。 - Dmitriy Zapevalov

是的，请查看我在问题下面的实现。非常感谢你。 - Kannan Suresh