vb.net - 动词。净 : LINQ to SQL equivalent to Count - group by query

Question

我正在尝试转换此查询(已经工作):

Select building.Name, Count(people.ID) as NumberOfUser
From tblBuilding as building left outer join tblPeople as people on building.ID = people.buildingID
Group by building.Name

结果如下:

Name          | NumberOfUser
----------------------------
WestBuilding    50
EastBuilding    70
SouthBuilding   0
NorthBuilding   2

Linq to SQL。看看我的尝试:

Dim db As New MyDatabaseDataContext
Dim query = From building In db.tblBuilding
            From people In db.tblPeople.Where(Function(x) building.ID = x.buildingID).DefaultIfEmpty
            Group building, people By building.ID, building.Name Into grp
                Select ID, Name, grp.Count
Return query.ToList

起初它似乎有效。但是当我检查一个没有人的建筑物时，Count 列说有 1 个。像这样:

Name          | NumberOfUser
----------------------------
WestBuilding    50
EastBuilding    70
SouthBuilding   1
NorthBuilding   2

因为那个楼虽然没有人，但是在结果查询中确实出现过一次，所以计为1。换句话说，Count语句实际上统计的是每个楼在结果表中出现了多少次，而不是每个楼有多少人建筑，从而使结果错误，因为没有人建筑。

有什么解决办法吗？

Answer 1

你是对的，要解决这个问题你需要测试是否有人，就像你对 count(people.Id)

所做的那样

Dim db As New MyDatabaseDataContext
Dim query = From building In db.tblBuilding
            From people In db.tblPeople.Where(Function(x) building.ID = x.buildingID).DefaultIfEmpty
            Group building, people By building.ID, building.Name Into Group, Count(Not IsNothing(people))
'Select ID, Name, grp.Count
Return query.ToList