关于c#:Linq to SQL Left Join, Order and Group By Count

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


试试这个:

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

原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/269640.html

(0)
上一篇 2022年6月22日
下一篇 2022年6月22日

相关推荐

发表回复

登录后才能评论