Linq to SQL Left Join, Order and Group By Count

Question

This query works fine for me:

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 

However, I do not get the expected result when I convert it to Linq. It returns count = 1 when left join returns null. So, I tried this query:

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

Answer 1

Try the following:

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