LINQ OrderBy Name ThenBy ChildrenCollection.Name

Question

Is there any way in LINQ to do an OrderBy and then do a ThenBy with the ThenBy using the children of the parent object to do the secondary ordering?

_repository.GetActiveDepartmentGroupsWithDepartments().OrderBy(c => c.DepartmentGroupName).ThenBy(c => c.Departments.OrderBy(d => d.DepartmentName))

In the above case, c.Departments is an EntityCollection.

BTW: When I try the above and then do a ToList() on it, I get this error:

DbSortClause expressions must have a type that is order comparable.
Parameter name: key

Thanks in advance for any help or guidance.

Answer 1

It seems like you're trying to get a list of all departments ordered by group then department name. If so, then you probably want to do something like this:

var res = from c in _repository.GetActiveDepartmentGroupsWithDepartments()
          from d in c.Departments
          orderby c.DepartmentGroupName, d.DepartmentName
          select d;

Or in method syntax:

var res = _repository.GetActiveDepartmentGroupsWithDepartments()
                     .SelectMany(c => c.Departments, (c,d) => new { c, d })
                     .OrderBy(x => x.c.DepartmentGroupName)
                     .ThenBy(x => x.d.DepartmentName)
                     .Select(x => x.d);

Answer 2

Since Deparment is a collection, you have to transform it to a scalar to use it for sorting.

One option is to select a single entity to in the collection, e.g. the name of the first department:

_repository.GetActiveDepartmentGroupsWithDepartments()
   .OrderBy(c => c.DepartmentGroupName)
   .ThenBy(c => c.Departments
       .OrderBy(d => d.DepartmentName)
       .FirstOrDefault()
       .DepartmentName
    )

Another option is to order by a property of the collection itself, e.g. the number of departments:

_repository.GetActiveDepartmentGroupsWithDepartments()
   .OrderBy(c => c.DepartmentGroupName)
   .ThenBy(c => c.Departments.Count())