10 September 2011

Multiple Group By vs. Multiple Order By syntax in LINQ

Knowing Group by in Sql, having it in linq is a very power full feature. An interesting aspect of linq is that the OrderBy and GroupBy are using very different syntax when operating with multiple elements.

.GroupBy(c => new { c.ClientInitials, c.OwnerInitials, c.Department } )
.OrderBy(c => c.ClientInitials).ThenBy(c => c.OwnerInitials).ThenBy(c => c.Department)

If you are tricked into the assumption that you can do: .OrderBy(c => new { c.ClientInitials, c.OwnerInitials, c.Department } )
You will receive an Exception: “At least one object must implement IComparable."

Here is a an example on how one set of objects can be remodelled with linq to get reporting statistics.
The Resource class has a list of bookings and we start by flattening to one dimension of data for simplicity.

var resourceSet = from c in this.GetResources()
                              from o in c.Bookings

This could also be achieved with a .SelectMany

var resourceSet = this.GetResources().SelectMany( c => c.Bookings, (c, o) => new { c.Department, c.Initials, c.Name, o.ClientInitials, o.OwnerInitials, o.EventDate, o.Description });

Then we group by the three main parameters and summarizes the statistics base on the booking periods.

            IEnumerable<ReportViewModel> groupedByClientsOwnerDepartment = from c in resourceSet
                                                  group c by new { c.ClientInitials, c.OwnerInitials, c.Department }
                                                      into g
                                                      select new ViewModels.ReportViewModel()
                                                              ClientInitials = g.Key.ClientInitials,
                                                              OwnerInitials = g.Key.OwnerInitials,
                                                              Department = g.Key.Department,
                                                              CountBookingsLatestFullMonth = g.ToList().Where(f => f.EventDate.Month == DateTime.Today.AddMonths(-1).Month).Count(),
                                                              CountBookingsSinceLastFullMonth = g.ToList().Where(f => f.EventDate <= new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1)).Count() };

We then return to the view and order the output by the main 3 group by elements.

            return View(groupedByClientsOwnerDepartment.OrderBy(c => c.ClientInitials).ThenBy(c => c.OwnerInitials).ThenBy(c => c.Department));

No comments: