Grouping and multiple ordering using Linq for objects

Question

I have two objects:

public class Course { public int Id { get; set; } public string Name { get; set; } public DateTime StartDate { get; set; } public DateTime EndDate { get; set; } public virtual ICollection<Class> Classes { get; set; } } public class Class { public int Id { get; set; } public string Name { get; set; } public DateTime StartTime { get; set; } public DateTime EndTime { get; set; } ] public Course Course { get; set; } } 

Now I need to list each group of classes by course and sort by (descending) Course.StartDate, then by class .StartTime. I can get the course and order group Course.StartDate:

 var myList = context.Classes .GroupBy(c => c.Course) .OrderByDescending(g => g.Key.StartDate).ToList() 

But it is also not possible to order each class using StartTime. I tried this:

 var myList = context.Classes .OrderBy(c => c.StartTime) .GroupBy(c => c.Course) .OrderByDescending(g => g.Key.StartDate).ToList() 

And even that:

 var myList = context.Classes .GroupBy(c => c.Course) .OrderByDescending(g => g.Key.StartDate) .ThenBy(g => g.Select(c => c.StartTime)).ToList() 

But Classes are never ordered by StartTime.

* Edit for better clarification: This is for WebService, and I have to return List<IGrouping<Course, Class>> , and I really need an elegant way to do this (for example, using only Linq), without manually creating the list. If this is not possible, of course.

Any help is appreciated (I use the first EF code 4.3 bit.).

Answer 1

If you need to keep the signature, I suggest the following:

 var myList = context.Classes .GroupBy(cc => cc.Course) .OrderByDescending(gg => gg.Key.StartDate) .ToArray() // <- stops EF and starts L2O .SelectMany(gg => gg.OrderBy(cc => cc.StartTime)) .ToLookup(cc => cc.Course, cc => cc) .ToList(); 

This leads to a signature type: List<IGrouping<Course, Class>> and arranges them by StartTime . It depends on the behavior of ToLookup regarding the maintenance of the found order and can be changed.

Answer 2

It sounds like you really need a list of lists, where each nested list is ordered with additional criteria you specify. Since you are grouping a Course , you can order groups by start date, since each entry in the group will have the same start date. Then you can project the grouped items into a list ordered by launch time:

 var myList = context.Classes .GroupBy(c => c.Course) .OrderByDescending(g => g.Key.StartDate) .Select(g => g.OrderBy(c => c.StartTime).ToList()) .ToList(); 

Answer 3

Because EF creates direct SQL, it is not possible to perform a grouping that includes ordering in the same SQL.

I would suggest grouping in a single Linq expression using EF and ending with ToList (). And what sorting with Linq To Objects (in memory) as the second expression of Linq.

Which should be done quickly because the objects are already grouped.

So something like this should work:

 var myList = context.Classes .GroupBy(c => c.Course).ToList() .OrderByDescending(g => g.Key.StartDate).ToList()