EF Core 左连接和计数

Question

我在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编写查询以获得此结果？谢谢。

- ASPMaker

---

使用 city.Schools 等。 - Gert Arnold

Answer 1

您的查询有误。

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

另外，我强烈建议您使用导航属性而不是手动构建联接。

- lucky

---

这可以只是 group student by，因为你不需要 city 来进行计数。 - juharr

你可以从在 by 和 into 之间定义的键中获取它们。 - juharr

是的，你说得对。谢谢提醒。我已经更新了答案。 - lucky

@ASPMaker 这个答案已经更新了我的建议。你可以做的另一个潜在方法是使用导航属性而不是连接，如果你已经设置好了的话。类似这样 from school in city.Schools.DefaultIfEmpty()。 - juharr

@ASPMaker，查询有问题吗？ - lucky

@Stormcloak，谢谢你的回答。它运行得非常好。 - ASPMaker