我在MySql数据库中有三个表格。我想在这三个表格之间进行左联接,并使用group by计数。
城市表格
Id
名称
学校表格
Id
城市Id
名称
学生表格
Id
学校Id
名称
/* 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;
使用 EF Core,我尝试以下方式:
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();
结果必须如下所示:
1 City1 10
2 City2 3
3 City3 0
4 City4 0
5 City5 12
我该如何使用Entity Framework Core编写查询以获得此结果?谢谢。
city.Schools
等。 - Gert Arnold