Sql语句到linq的转换

Question

我想通过使用linq时的错误，使用group by来获取Testhistories的计数。没有分组，但这是错误的。

这个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时，生成的语句如下所示，这是导致错误的原因

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查询，该查询返回的结果与此处显示的第一条SQL语句相同。

谢谢。

Answer 1

var patients = (from patient in _dbContext.Patients.Include(x => x.TestHistories)  where patient.DoctorId == doctorId group new { x.PatientId, x.RecordId } by patient into g

select new PatientResponse
{
Id = g.key.RecordId,
Name = g.key.Name,
TestCount = g.Count()
});