5

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
  • 293
  • 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

10

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
  • 33,026
  • 6
  • 100
  • 83
  • 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