Wednesday, September 3, 2014

GROUP BY ORDER BY COUNT IN LINQ

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

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



var result = (from ls in transdatadtlsobj
                          group ls by new { ls.id, ls.transdata } into grouped
                          let count = grouped.Count()
                          orderby grouped.Key.id, count descending 
                          select new
                          {
                              id = grouped.Key.id,
                              transdata = grouped.Key.transdata,
                              Count = grouped.Count()
                          });