如何通过Linq按姓名、年龄等多字段分组并统计记录次数

需要将人员数据按姓氏（LastName）、名字（FirstName）、中间名（MiddleName）、年龄（Age）、**性别（Sex）和生日（BirthDate）**分组，统计每组的出现次数。

当前已编写的Linq查询代码 #

var spesbabies = new List<SpesBabies>();

var placed = (from ben in db.Beneficiaries
              join reg in db.Registrations on ben.ID equals reg.BenID
              join plc in db.Placements on reg.ID equals plc.RegID
            
              select new 
              {
                  LastName = ben.LastName,
                  FirstName = ben.FirstName,
                  MiddleName = ben.MiddleName,
                  Sex = ben.Gender,
                  BirthDate = ben.BirthDate,
                  Age = (int)plc.Age,

              }).ToList();

期望结果格式 #

修改后的Linq查询实现 #

要实现分组统计，只需在现有查询基础上添加分组逻辑，以下提供两种写法：

1. 查询语法写法 #

var placed = (from ben in db.Beneficiaries
              join reg in db.Registrations on ben.ID equals reg.BenID
              join plc in db.Placements on reg.ID equals plc.RegID
              select new 
              {
                  LastName = ben.LastName,
                  FirstName = ben.FirstName,
                  MiddleName = ben.MiddleName,
                  Sex = ben.Gender,
                  BirthDate = ben.BirthDate,
                  Age = (int)plc.Age,
              } into temp
              group temp by new 
              {
                  temp.LastName,
                  temp.FirstName,
                  temp.MiddleName,
                  temp.Age,
                  temp.Sex,
                  temp.BirthDate
              } into grouped
              select new 
              {
                  grouped.Key.LastName,
                  grouped.Key.FirstName,
                  grouped.Key.MiddleName,
                  grouped.Key.Age,
                  Gender = grouped.Key.Sex,
                  Birthdate = grouped.Key.BirthDate,
                  Count = grouped.Count()
              }).ToList();

2. 方法链语法写法 #

var placed = db.Beneficiaries
    .Join(db.Registrations, ben => ben.ID, reg => reg.BenID, (ben, reg) => new { ben, reg })
    .Join(db.Placements, br => br.reg.ID, plc => plc.RegID, (br, plc) => new 
    {
        LastName = br.ben.LastName,
        FirstName = br.ben.FirstName,
        MiddleName = br.ben.MiddleName,
        Sex = br.ben.Gender,
        BirthDate = br.ben.BirthDate,
        Age = (int)plc.Age
    })
    .GroupBy(x => new 
    {
        x.LastName,
        x.FirstName,
        x.MiddleName,
        x.Age,
        x.Sex,
        x.BirthDate
    })
    .Select(g => new 
    {
        g.Key.LastName,
        g.Key.FirstName,
        g.Key.MiddleName,
        g.Key.Age,
        Gender = g.Key.Sex,
        Birthdate = g.Key.BirthDate,
        Count = g.Count()
    })
    .ToList();

修改后的查询会先关联三张表获取所需字段，再按指定的6个字段分组，最后统计每组的记录数，结果格式与你期望的表格完全匹配。

内容的提问来源于stack exchange，提问作者Mark John Capistrano