求与指定MySQL查询等价的LINQ语句：分组统计Testhistories数量

需要实现与以下正确MySQL查询结果一致的LINQ语句，当前编写的LINQ生成的SQL存在逻辑错误，导致统计结果不正确。

正确的MySQL查询语句 #

SELECT 
    t.RecordId AS `Id`, t.Name, 
    (SELECT COUNT(*)
     FROM 
         (SELECT t1.PatientId, t1.RecordId
          FROM tbl_TestHistories AS t1
          GROUP BY t1.RecordId) AS t0  
     WHERE t.RecordId = PatientId) AS `TestCount`
FROM 
    tbl_Patients AS t
WHERE 
    t.DoctorId = '';

当前LINQ生成的错误SQL #

SELECT 
    `t`.`RecordId` AS `Id`, `t`.`Name`,
    (SELECT COUNT(*)
     FROM 
         (SELECT `t1`.`PatientId`, `t1`.`RecordId`
          FROM `tbl_TestHistories` AS `t1`
          WHERE `t`.`RecordId` = `t1`.`PatientId`
          GROUP BY `t1`.`PatientId`, `t1`.`RecordId`) AS `t0`) AS `TestCount`
FROM 
    `tbl_Patients` AS `t`
WHERE 
    `t`.`DoctorId` = '';

尝试的LINQ代码 #

var patients = (from patient in _dbContext.Patients.Include(x => x.TestHistories)
                where patient.DoctorId == doctorId
                select new PatientResponse
                        {
                            Id = patient.RecordId,
                            Name = patient.Name,
                            TestCount = patient.TestHistories.GroupBy(x => new { x.PatientId, x.RecordId }).Count()
                        });

正确的LINQ语句 #

方式1：模拟原SQL子查询结构 #

完全匹配原MySQL的逻辑，先全局对TestHistories按RecordId分组，再统计与当前患者匹配的分组数量：

var patients = from patient in _dbContext.Patients
               where patient.DoctorId == doctorId
               select new PatientResponse
               {
                   Id = patient.RecordId,
                   Name = patient.Name,
                   TestCount = _dbContext.TestHistories
                       .GroupBy(th => th.RecordId)
                       .Select(g => new { PatientId = g.First().PatientId })
                       .Count(t0 => t0.PatientId == patient.RecordId)
               };

方式2：分组关联优化查询 #

通过左关联分组后的统计结果，逻辑等价且性能更优：

// 先预计算TestHistories的分组统计
var testGroupCounts = from th in _dbContext.TestHistories
                      group th by th.RecordId into g
                      select new 
                      { 
                          PatientId = g.First().PatientId, 
                          GroupCount = 1 
                      };

// 关联患者表获取最终结果
var patients = from patient in _dbContext.Patients
               join tc in testGroupCounts on patient.RecordId equals tc.PatientId into tcGroup
               where patient.DoctorId == doctorId
               select new PatientResponse
               {
                   Id = patient.RecordId,
                   Name = patient.Name,
                   TestCount = tcGroup.Sum(tc => tc.GroupCount)
               };

错误原因说明 #

原LINQ代码的问题在于：通过Include加载当前患者的TestHistories后，仅对该患者的关联数据进行分组，这与原MySQL先全局分组所有TestHistories，再关联患者匹配统计的逻辑完全不符，导致生成的SQL提前加入了患者关联条件，统计结果错误。

内容的提问来源于stack exchange，提问作者Technorg