LINQ - 按多个属性对列表进行分组，并返回具有数组成员的对象
本文关键字：返回 数组 对象 组成员 属性 列表 LINQ | 更新日期: 2023-09-27 18:36:32
这将
是一个由两部分组成的问题。
我正在尝试构建一个用于谷歌图表 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"属性进一步分组，以便季度是每个问题的数组（这使得数据结构更容易迭代）。 所需结构的示例：
{"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}
}
感谢 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}]}]
-
将以下内容添加到查询中：
.GroupBy(x => x.Problem) .ToDictionary(g => g.Key, g => g.Select(x=>new { Year=x.Year, Quarter=x.Quarter, Count = x.Count })); -
您必须在 之前插入以下内容。上面的ToDictionary（）：
.Select(g => new { Key = g.Key, Items = g .GroupBy(r => r.Year) .SelectMany(gy => gy.Concat( Enumerable.Range(1,5) .Where(q => !gy.Any(r=>r.Quarter == q)) .Select(q => new { Problem = g.Key, Year = gy.Key, Quarter = q, Count = 0 }) ) ) } )
我认为。。。:)试试吧
但是，我建议不要遵循这种方法，并在客户端上创建"空"记录，以避免过度使用带宽。
以下是满足您所有条件的完整重述：
public static IEnumerable<DateTime> GetQuarterDates()
{
for (DateTime quarterDate = DateTime.Now.AddYears(-4); quarterDate <= DateTime.Now; quarterDate = quarterDate.AddMonths(3))
{
yield return quarterDate;
}
}
public static void RunSnippet()
{
var RMAs = new[] {
new { Problem = "P", CreatedDate = new DateTime(2012, 6, 2) },
new { Problem = "P", CreatedDate = new DateTime(2011, 12, 7) },
new { Problem = "P", CreatedDate = new DateTime(2011, 12, 8) },
new { Problem = "P", CreatedDate = new DateTime(2011, 8, 1) },
new { Problem = "P", CreatedDate = new DateTime(2011, 4, 1) },
new { Problem = "Q", CreatedDate = new DateTime(2011, 11, 11) },
new { Problem = "Q", CreatedDate = new DateTime(2011, 6, 6) },
new { Problem = "Q", CreatedDate = new DateTime(2011, 3, 3) }
};
var quarters = GetQuarterDates().Select(quarterDate => new { Year = quarterDate.Year, Quarter = Math.Ceiling(quarterDate.Month / 3.0) });
var rmaProblemQuarters = from rma in RMAs
where rma.CreatedDate > DateTime.Now.AddYears(-4)
group rma by rma.Problem into rmaProblems
select new {
Problem = rmaProblems.Key,
Quarters = (from quarter in quarters
join rmaProblem in rmaProblems on quarter equals new { Year = rmaProblem.CreatedDate.Year, Quarter = Math.Ceiling(rmaProblem.CreatedDate.Month / 3.0) } into joinedQuarters
from joinedQuarter in joinedQuarters.DefaultIfEmpty()
select new {
Year = quarter.Year,
Quarter = quarter.Quarter,
Count = joinedQuarters.Count()
})
};
string json = System.Web.Helpers.Json.Encode(rmaProblemQuarters);
Console.WriteLine(json);
}
这会产生：
[{"Problem":"P","Quarters":[{"Year":2008,"Quarter":2,"Count":0},{"Year":2008,"Quarter":3,"Count":0},{"Year":2008,"Quarter":4,"Count":0},{"Year":2009,"Quarter":1,"Count":0},{"Year":2009,"Quarter":2,"Count":0},{"Year":2009,"Quarter":3,"Count":0},{"Year":2009,"Quarter":4,"Count":0},{"Year":2010,"Quarter":1,"Count":0},{"Year":2010,"Quarter":2,"Count":0},{"Year":2010,"Quarter":3,"Count":0},{"Year":2010,"Quarter":4,"Count":0},{"Year":2011,"Quarter":1,"Count":0},{"Year":2011,"Quarter":2,"Count":1},{"Year":2011,"Quarter":3,"Count":1},{"Year":2011,"Quarter":4,"Count":2},{"Year":2011,"Quarter":4,"Count":2},{"Year":2012,"Quarter":1,"Count":0},{"Year":2012,"Quarter":2,"Count":1}]},{"Problem":"Q","Quarters":[{"Year":2008,"Quarter":2,"Count":0},{"Year":2008,"Quarter":3,"Count":0},{"Year":2008,"Quarter":4,"Count":0},{"Year":2009,"Quarter":1,"Count":0},{"Year":2009,"Quarter":2,"Count":0},{"Year":2009,"Quarter":3,"Count":0},{"Year":2009,"Quarter":4,"Count":0},{"Year":2010,"Quarter":1,"Count":0},{"Year":2010,"Quarter":2,"Count":0},{"Year":2010,"Quarter":3,"Count":0},{"Year":2010,"Quarter":4,"Count":0},{"Year":2011,"Quarter":1,"Count":1},{"Year":2011,"Quarter":2,"Count":1},{"Year":2011,"Quarter":3,"Count":0},{"Year":2011,"Quarter":4,"Count":1},{"Year":2012,"Quarter":1,"Count":0},{"Year":2012,"Quarter":2,"Count":0}]}]