Grouping and multiple orderings using Linq to Entities

Question

I have this two entities:

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 Class grouping by Course and ordered by (descending) Course.StartDate then by Class.StartTime. I can get to group by Course and order by Course.StartDate:

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

But can't manage to also order each Class by it's StartTime. I tried this:

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

And even this:

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

But the Classes are never ordered by it's StartTime.

*Edit for better clarification: This is for a WebService and I must return a List<IGrouping<Course, Class>> and I really want an elegant way of doing this (i.e. using just Linq), without manually creating the list. Unless it's not possible of course.

Any help is apreciated (I'm using EF code-first 4.3 btw).

Answer 1

If you need to preserve 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 results in a type of signature: List<IGrouping<Course, Class>> and has them properly ordered by StartTime. This relies on the behavior of ToLookup with respect to maintaining the order found and may be subject to change.

Answer 2

Looks like you really need a list of lists, where each nested list is ordered with the additional criteria you mentioned. Since you are grouping by Course, you can order the groups by start date because each entry within a group will have the same start date. Then you can project the grouped elements to a list ordered by start 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

Since EF creates direct SQL it's not possible to do a grouping that includes an ordering in the same SQL.

I would suggest to do the grouping in one Linq Statement using EF and ending with a ToList(). And than do the sorting with Linq To Objects (in memory) as a second Linq Statement.

Which should run fast, because the objects are already grouped.

So something like that should work:

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