Include count = 0 in linq results

Question

I have a table having TeamName and CurrentStatus fields. I am making a linq query to get for each team and for each status the count of records:

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

The results of this query returns all the counts except where count is 0. I need to get the rows where there is no record for a specific team and a specific status (where count = 0).

Answer 1

You could have a separate collection for team name and statuses you are expecting 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've created a fiddle demonstrating the answer as well