EF Core left join with count

Question

i have got 3 tables on MySql database. i want to do left join between this 3 tables and count with group by.

City Table
Id
Name

School Table
Id
CityId
Name

Student Table
Id
SchoolId
Name

/* MySql raw query like this: */
select Count(tstudent.id) as StudentCount, tcity.Id, tcity.Name
from City tcity
left join School tschool on tcity.Id = tschool.CityId
left join Student tstudent on tschool.Id = tstudent.SchoolId
group by tcity.Id;

With EF Core i try like this:

class CityWithStudentCount {
    public int Id { get;set; }
    public string CityName { get;set; }
    public int StudentCount { get;set; }
}

Ef Core :

var db = new MyDbContext();

var result = (from city in db.City
             join school in db.School on city.Id equals school.CityId into tcity
             from r1 in tcity.DefaultIfEmpty()

             join student in db.Student on school.Id equals student.SchoolId into tschool
             from r2 in tschool.DefaultIfEmpty()
             
             select new CityWithStudentCount
             {
                 Id = city.Id,
                 CityName = city.Name,
                 StudentCount = tschool.count()
             } into s1
             
             group s1 by s1.Id)
             .Select(s=>s.ToList())
             .ToList();

Result must be like that :

1 City1 10
2 City2 3
3 City3 0
4 City4 0
5 City5 12

How can i do like this query for this result with Entity Framework Core. Thank you.

Answer 1

Your query is wrong.

 var result = (from city in db.City
         join school in db.School on city.Id equals school.CityId into t1
         from school in t1.DefaultIfEmpty()

         join student in db.Student on school.Id equals student.SchoolId into t2
         from student in t2.DefaultIfEmpty()

         group student by new { city.Id,city.Name } into cityGrouped
         select new CityWithStudentCount
         {
             Id = cityGrouped.Key.Id,
             CityName = cityGrouped.Key.Name,
             StudentCount = cityGrouped.Count(x => x.student != null)
         }
         .ToList();

Also, I strongly suggest you to use navigation properties instead of manual building joins.