CODEWITHSUNDEEP
c#linqgroup-bysql-order-by
ciroid
ciroid

Reputation: 25

How do I order a group result with a list, in Linq?

This linq provides me all rooms as a list grouped by rateCode.

var results = (from r in dcCrs.CRS_RateTypePerReservation
                   where r.Reservation_id_fk == reservation.Reservation_id_pk 
                      && r.RoomTypeCenter_id_fk != null 
                      && r.Price != 0
               group r.RoomTypesPerCenter.RoomTypes.Code by r.CRS_RateTypes.Name into g
               select new { rateCode = g.Key, roomName = g.ToList() });

But now I have to order the results by an Integer in database, named Order:

var results = (from r in dcCrs.CRS_RateTypePerReservation
                   where r.Reservation_id_fk == reservation.Reservation_id_pk 
                      && r.RoomTypeCenter_id_fk != null 
                      && r.Price != 0
                   orderby r.Order ascending
                   group r.RoomTypesPerCenter.RoomTypes.Code by r.CRS_RateTypes.Name into g
                   select new { rateCode = g.Key, roomName = g.ToList() });

This only orders the name of rooms, not both of them.

Data:

Order   Rates      RoomType 
5       PER        DBL  
30      PER        IND
15      BAR        IND
10      BAR        DBL  
20      BAR        URB

It should give this result because the first is 5 and 30 (PER) and then 10, 15 and 20 (BAR):

   {rateCode = PER, roomName = {DBL, IND} }

   {rateCode = BAR, roomName = {DBL, IND, URB} }

But it returns me this:

   {rateCode = BAR, roomName = {DBL, IND, URB} }

   {rateCode = PER, roomName = {DBL, IND} }

Thanks for any advice.

Upvotes: 1

Views: 115

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205849

The order of keys of database GROUP BY query result is undefined.

You need to apply ordering after the grouping, like this

var results = 
   (from r in dcCrs.CRS_RateTypePerReservation
    where r.Reservation_id_fk == reservation.Reservation_id_pk 
        && r.RoomTypeCenter_id_fk != null 
        && r.Price != 0
    group r by r.CRS_RateTypes.Name into g
    orderby g.Min(r => r.Order)
    select new
    {    
        rateCode = g.Key,
        roomName = (from r in g orderby r.Order select r.RoomTypesPerCenter.RoomTypes.Code).ToList()
    });

Upvotes: 1

Related Questions