4

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?

Travis
  • 283
  • 1
  • 5
  • 13
  • possible duplicate of [Ordering Entity Framework sub-items for EditorFor](http://stackoverflow.com/questions/3591726/ordering-entity-framework-sub-items-for-editorfor) – Craig Stuntz Nov 11 '10 at 17:20

1 Answers1

9

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.

Morteza Manavi
  • 32,518
  • 6
  • 97
  • 82
  • Does this work with POCO classes where you don't have collections of type `EntityCollection`? For instance: In the example above `Makes` is of type `List` which doesn't have `Attach` and `CreateSourceQuery` methods. I'm just facing a similar problem, but I am using Code-First (CTP5) with DbContext and simple POCO classes. In this situation is there a solution to load the child collections in a specific sort order - other than sorting the child collections in memory after the parent object has already been loaded from DB? – Slauma Feb 18 '11 at 15:02
  • 2
    No it doesn't work with POCOs but there is a perfect way to accomplish this in CTP5 by using the new *Query()* method: `context.Entry(year).Collection(y => y.Makes).Query().OrderBy(m => m.Name).Load();` – Morteza Manavi Feb 18 '11 at 16:43
  • Great, thanks! But this requires actually two queries against the database: 1) Load `year` without child collection, and 2) The query to load the sorted child collection. Is that right? – Slauma Feb 18 '11 at 18:42
  • 2
    Yes and this is exactly the same case as in `CreateSourceQuery` way since a second trip to DB happens when we call the `Attch` method. – Morteza Manavi Feb 18 '11 at 18:54