Linq to SQL Left Join, Order and Group By Count
本文关键字:and Group By Count Order to SQL Left Join Linq | 更新日期: 2023-09-27 18:25:30
我让这个查询工作得很好:
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。所以,我一直在尝试这个查询:
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()
});
试试这个:
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()
}