我有一个包含TeamName
和CurrentStatus
字段的表。我正在做一个linq查询,以获得每个团队和每个状态的记录计数:
var teamStatusCounts = models.GroupBy(x => new { x.CurrentStatus, x.TeamName })
.Select(g => new { g.Key, Count = g.Count() });
该查询的结果返回除count为0之外的所有计数。我需要得到没有特定团队和特定状态记录的行(其中count = 0)。
您可以为您期望的团队名称和状态设置一个单独的集合,并将缺少的添加到结果集
//assuming allTeamNamesAndStatuses is a cross joing of all 'CurrentStatus' and 'TeamNames'
var teamStatusCounts = models.GroupBy(x => new { x.CurrentStatus, x.TeamName })
.Select(g => new { g.Key, Count = g.Count() })
.ToList();
var missingTeamsAndStatuses = allTeamNamesAndStatuses
.Where(a=>
!teamStatusCounts.Any(b=>
b.Key.CurrentStatus == a.CurrentStatus
&& b.Key.TeamName == a.TeamName))
.Select(a=>new {
Key = new { a.CurrentStatus, a.TeamName },
Count = 0
});
teamStatusCounts.AddRange(emptyGroups);
我已经创建了一个小提琴来演示答案
我将首先选择团队名称和状态:
var teams = models.Select(x => x.TeamName).Distinct().ToList();
var status = models.Select(x => x.CurrentStatus).Distinct().ToList();
如果你已经知道列表的条目,你可以跳过这个。
然后您可以为每个团队和每个州选择模型的数量:
var teamStatusCounts = teams.SelectMany(team => states.Select(state =>
new
{
TeamName = team,
CurrentStatus = state,
Count = models.Count(model =>
model.TeamName == team && model.CurrentStatus == state)
}));