Include count = 0 in linq results

Question

I have a table with the TeamName and CurrentStatus . I make a linq query to get for each command and for each state the number of entries:

 var teamStatusCounts = models.GroupBy(x => new { x.CurrentStatus, x.TeamName }) .Select(g => new { g.Key, Count = g.Count() }); 

The results of this query return all the counts, except those where the number is 0. I need to get strings in which there is no record for a specific command and a certain status (where count = 0).

Answer 1

You can have a separate collection for the team name and statuses that you expect and add the missing ones to the result set

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

I created a fiddle demonstrating the answer as well

Answer 2

First, I would choose the names and status of the team:

 var teams = models.Select(x => x.TeamName).Distinct().ToList(); var status = models.Select(x => x.CurrentStatus).Distinct().ToList(); 

You can skip this if you already know the list entries.

Then you can choose for each team and each state the number of models:

 var teamStatusCounts = teams.SelectMany(team => states.Select(state => new { TeamName = team, CurrentStatus = state, Count = models.Count(model => model.TeamName == team && model.CurrentStatus == state) }));