GroupBy和Count的EF Core查询未按预期工作

Question

我有一个带有EF Core 3.1.8的.NET Core 3.1项目。假设我有两个实体：

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; }
}

现在，我想要一个查询，它可以为我提供部分，以及每个部分中有多少张Stage=1、Stage=2、Stage=3等的卡的信息。

我试过这个：

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

但结果总是只有一节只有一张牌。我该怎么做？

Answer 1

我对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();

Answer 2

当我遇到EntityFramework行为不如我预期的问题时，我倾向于重新思考如何在SQL中直接做到这一点。模仿通常会让EF发挥作用。

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

编辑：根据注释重塑数据

根据以上内容，我们可以将数据重塑为您想要的数据。

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

编辑：我尽量减少查询，只带回完成工作所需的数据。但是，如果你不处理大量数据，那么这可能会提供一个更紧凑的单一查询选项，而代价是可能带回更多你需要的数据，从而获得更大的结果集。

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