Linq to SQL Left Join, Order and Group By Count
我的这个查询运行良好:
1
2 3 4 5 |
SELECT B.ID, B.NAME, COUNT(BU.ID) AS TOTAL
FROM Building B LEFT JOIN BuildingUser BU ON BU.ID_BUILDING = B.ID GROUP BY B.ID, B.NAME ORDER BY COUNT(BU.ID) DESC, B.NAME |
但是,当我将它转换为 Linq 时,我没有得到预期的结果。当左连接返回 null 时,它返回 count = 1。所以,我一直在尝试这个查询:
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
var list1 = (from building in db.GetTable<Building>()
join entitybuildinguser in db.GetTable<BuildingUser>() on building.ID equals entitybuildinguser.ID_BUILDING into tmpbuildinguser from buildinguser in tmpbuildinguser.DefaultIfEmpty() group building by new { building.ID, building.NAME } into grpBuilding orderby grpBuilding.Select(g => g.ID).Count() descending, grpBuilding.Key.NAME select new { ID_BUILDING = grpBuilding.Key.ID, NAME = grpBuilding.Key.NAME, users = grpBuilding.Select(g => g.ID).Count() }); |
- 你的sql正确吗?你只剩下加入用户并且什么都不做。我错过了什么吗?
- @ErenErs?nmez 你是对的。我已经从 INNER 变成了 LEFT,忘记拿出来了。即使出局也不会改变最终结果。
- 您正在按其 ID 对建筑物进行分组,因此该组中只会有一个建筑物(假设 ID 是唯一的)。您的第一个分组是否应该分组 buildingUser 而不是 building?
试试这个:
1
2 3 4 5 6 7 8 9 |
from b in db.Buildings
join u in db.BuildingUsers on b.ID equals u.ID_BUILDING into g orderby g.Count() descending, b.Name descending select new { Id = b.ID, Name = b.NAME, Total = g.Count() } |
来源:https://www.codenong.com/17957252/