entity framework - EF4 LINQ Ordering Parent and all child collections with Eager Loading (.Include())

Question

I am doing hierarchical data binding on a grid, and I need to have the database server perform the sorting on my objects. I can easily sort the parent collection, but I can't seem to figure out how to also sort all the child collections. I have a model which has child collections nested 3 deep, and all of these collections need to be sorted.

Here is a sample model of what I'm trying to accomplish:

    public class Year
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Make> Makes { get; set; }
}
public class Make
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Model> Models { get; set; }
}
public class Model
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Color> Colors { get; set; }
}
public class Color
{
    public int Id { get; set; }
    public string Name { get; set; }
}

I am trying to load a List of "Year" objects. This has a collection of Makes, which has a collection of Models which has a Collection of Colors. I need to sort all of these objects based on their name property.

I have tried doing this:

            List<Year> years = db.Years.OrderBy("it.Name")
                                   .Include("Makes").OrderBy("it.Name")
                                   .Include("Makes.Models").OrderBy("it.Name")
                                   .Include("Makes.Models.Colors").OrderBy("it.Name")
                                   .ToList();

but "it." is only an alias for the table being selected from... in this case "Years". Is there any way to create an alias for the child tables so I can perform sorting on them as well in a single query?

Answer 1

If you need to have Ordering or Filtering on inner navigation properties (e.g. Models) then you cannot eager load them using Include method anymore. Instead, you can use EntityCollection<TEntity>.CreateSourceQuery Method like this:

List years = db.Years.OrderBy("it.Name").ToList();
foreach(year in years) 
{
    var makesQuery = year.Makes.CreateSourceQuery().OrderBy(m => m.Name);
    year.Makes.Attach(makesQuery);  

    foreach(make in year.Makes) 
    {
        var modelsQuery = make.Models.CreateSourceQuery().OrderBy(m => m.Name);
        make.Models.Attach(modelsQuery);

        foreach(model in make.Models) 
        {
            var colQuery = model.Colors.CreateSourceQuery().OrderBy(c => c.Name);
            model.Models.Attach(colQuery);        
        }
    }
}

This way, the years object will be constructed with having all of its navigation properties ordered.