EF Core query with GroupBy and Count not working as expected

Question

I have a .NET Core 3.1 project with EF Core 3.1.8. Lets say I have two entitys:

public class Card
{
    public int CardId { get; set; }
    public int Stage { get; set; }
    public int SectionId { get; set; }
    public Section Section { get; set; }        
}

public class Section
{
    public int SectionId { get; set; }
    public string Title { get; set; }
    public List<Card> Cards { get; set; }
}

Now I want a query that gives me the sections and for each section the information of how many Cards with Stage=1, Stage=2, Stage=3 etc. are in there.

I tried this:

var q = _dbContext.Sections
              .Include(s => s.Cards)
              .Select(s => new
              {
                  s.SectionId,
                  cards = s.Cards
                           .Select(c => c.Stage)
                           .GroupBy(c => c)
                           .Select(c => new { c.Key, count = c.Count() })
              })
              .ToList();

But in the result is always only one section with only one card. How can I do this?

Answer 1

When I run into issues where EntityFramework isn't quite behaving as I would expect, I tend to fall back to thinking about how I would do this in SQL directly. Mimicking that usually makes EF work.

//Create a query to group and count the cards
//In SQL:
//  SELECT SectionId, Stage, COUNT(CardId)
//  FROM Cards
//  GROUP BY SectionId, Stage
//In EF (note, not executing just building up the query):
var cardCountQuery = context.Cards
    .Select(c => new
    {
        c.SectionId,
        c.Stage
    })
    .GroupBy(c => c)
    .Select(c => new
    {
        SectionAndStage = c.Key,
        Count = c.Count()
    });

//Now use that as a subquery and join to sections
//In SQL
//  SELECT s.SectionId, s.Title, c.Stage, c.CardCount
//  FROM Sections s
//  INNER JOIN (
//      SELECT  SectionId, Stage, COUNT(CardId) AS CardCount
//      FROM    Cards
//      GROUP BY SectionId, Stage
//  ) c ON c.SectionId = s.SectionId
//In EF:
var sectionsWithCardCountByStage = context.Sections
    .Join(cardCountQuery,
    s => s.SectionId,
    c => c.SectionAndStage.SectionId,
    (s, g) => new
    {
        s.SectionId,
        g.SectionAndStage.Stage,
        CardCount = g.Count
    })
    .ToList();

Edit: Reshaping the data per comment

From what is above we can then reshape the data to what you are looking for.

//If you don't mind bring back the Section data multiple times (this will increase the result set size) you can alter the above to bring back the entire Section in the query and then re-shape it in memory.
//NOTE: This will only bring back Sections that have cards
var sectionsWithCardCountByStage = context.Sections
    .Join(cardCountQuery,
    s => s.SectionId,
    c => c.SectionAndStage.SectionId,
    (s, g) => new
    {
        Section = s,
        g.SectionAndStage.Stage,
        CardCount = g.Count
    })
    .ToList()
    .GroupBy(g => g.Section.SectionId)
    .Select(g => new
    {
        g.First().Section,
        Cards = g.ToDictionary(c => c.Stage, c => c.CardCount)
    })
    .ToList();

//Or you can bring back Sections only once to reduce result set size.  This extends the query from the first response section above.
var sections = context.Sections
    .Where(s => s.Cards.Count > 0) //Only bring back sections with cards. Remove it to bring back all sections and have an empty dictionary of card counts.
    .ToList()
    .Select(s => new
    {
        Section = s,
        Cards = sectionsWithCardCountByStage
            .Where(c => c.SectionId == s.SectionId)
            .ToDictionary(c => c.Stage, c => c.CardCount)
    })
    .ToList();

EDIT: I try minimize my queries and bring back only the data necessary to do the job. But if you aren't dealing with a lot of data then this might offer a more compact single query option at the expense of possibly bringing back more data then you need and thus a larger result set.

var sections = context.Sections
    .Include(s => s.Cards)
    .ToList()
    .Select(s => new
    {
        Section = s,
        CardCount = s.Cards.GroupBy(c => c.Stage)
            .Select(g => new { Stage = g.Key, CardCount = g.Count() })
            .ToDictionary(c => c.Stage, c => c.CardCount)
    })
    .ToList();

Answer 2

I made slight tweak on Group by

 var q = _dbContext.Sections
      .Include(s => s.Cards)
      .GroupBy(s => s.SectionId)
      .Select(s => new
      {
          s.Key,
          cards = s.SelectMany(t => t.Cards)
                   .GroupBy(c => c.Stage)
                   .Select(c => new { c.Key, count = c.Count() })
      })
      .ToList();