c# - LINQ - 按多个属性对列表进行分组并返回具有数组成员的对象

Question

这将是一个由两部分组成的问题。

我正在尝试构建用于 Google Charts API 的数据结构(特别是它们的数据表)。

这是我现在的代码:

return Json.Encode(
    RMAs
    .Where(r => r.CreatedDate.Year > DateTime.Now.Year - 4) //Only grab the last 4 years worth of RMAs
    .GroupBy(r => new { Problem = r.Problem, Year = r.CreatedDate.Year, Quarter = ((r.CreatedDate.Month) / 3) })
    .Select(r => new { Problem = r.Key.Problem, Year = r.Key.Year, Quarter = r.Key.Quarter, Count = r.Count() })
);

这让我非常接近。这让我得到一个类似于以下的数组:

{"Problem":"It broke!","Year":2012,"Quarter":2,"Count":3},
{"Problem":"It broke!","Year":2012,"Quarter":1,"Count":1}

但是，我想要的是根据“问题”属性对数据进行进一步分组，以便四分之一是每个问题的数组(这使得数据结构更容易迭代)。所需结构的示例:

{"Problem":"It broke!",
    {"Year":2012,"Quarter":2,"Count":3},
    {"Year":2012,"Quarter":1,"Count":1}
},
{"Problem":"Some other problem",
    {"Year":2012,"Quarter":1,"Count":31}
}

问题的第二部分:如何确保我拥有每个季度的数据(同样，这使得使用 API 构建数据表时更更容易迭代)，甚至如果那个季度没有发生“问题”？使用与上次相同的示例:

{"Problem":"It broke!",
    {"Year":2012,"Quarter":2,"Count":3},
    {"Year":2012,"Quarter":1,"Count":1}
},
{"Problem":"Some other problem",
    {"Year":2012,"Quarter":2,"Count":0}
    {"Year":2012,"Quarter":1,"Count":31}
}

Answer 1

感谢Mr. TA获得灵感并向我展示您可以使用 LINQ 进行分组。

我已经在本地环境中对此进行了测试，LINQ 确实返回了一个问题列表，这些问题与具有总计数的年度/季度分组数组相关联。我不知道 Json.Encode 是否以正确的格式对其进行编码。

以下 LINQ 应返回符合您所需格式的匿名类型:

编辑:对于至少出现一个问题但未出现指定问题的季度，查询现在返回 count=0

var quarters = RMAs
    .Where(rma => rma.CreatedDate.Year > DateTime.Now.Year - 4)
    .GroupBy(rma => new { 
        Year = rma.CreatedDate.Year, 
        Quarter = ((rma.CreatedDate.Month) / 3) 
    });

return Json.Encode(
    RMAs
        //Only grab the last 4 years worth of RMAs
        .Where(r => r.CreatedDate.Year > DateTime.Now.Year - 4)
        // Group all records by problem     
        .GroupBy(r => new { Problem = r.Problem })
        .Select(grouping => new
            {
                Problem = grouping.Key.Problem,
                Occurrences = quarters.Select(quarter => new
                    {
                        Year = quarter.Key.Year,
                        Quarter = quarter.Key.Quarter,
                        Count = grouping
                                .GroupBy(record => new
                                {
                                    Year = record.CreatedDate.Year,
                                    Quarter = ((record.CreatedDate.Month) / 3)
                                })
                                .Where(record => 
                                    record.Key.Year == quarter.Key.Year 
                                    && record.Key.Quarter == quarter.Key.Quarter
                                ).Count()
                    }).ToArray()
            }));

更新:感谢JamieSee使用示例 JSON 输出进行更新:

这是 JSON 输出的示例:

[{"Problem":"P","Occurrences":[{"Year":2012,"Quarter":4,"Count":2},{"Year":2012,"Quarter":2,"Count":1},{"Year":2012,"Quarter":1,"Count":1}]},{"Problem":"Q","Occurrences":[{"Year":2012,"Quarter":3,"Count":1},{"Year":2012,"Quarter":2,"Count":1},{"Year":2012,"Quarter":1,"Count":1}]}]