Sql statement to linq conversion

Question

I want to get count of Testhistories using group by but through error when using linq. Without group by got the count but that was wrong.

This mysql query is working fine

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 = '';

But when I tried to write linq for same query the statement was generated like below which is cause error

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` = '';

This is my attempt at the linq query:

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

Please share a linq query which returns the same result as the first SQL statement shown here.

Thanks.

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