Entity Framework, MVC 3, OrderBy in LINQ To Entities

Question

I've got the following query:

model.Page = db.Pages
    .Where(p => p.PageId == Id)
    .Include(p => p.Series
                   .Select(c => c.Comics
                                 .Select(col => col.Collection)))
    .SingleOrDefault();

This works great, although I now need to order the Comics by a property called 'ReadingOrder'.

I've tried:

model.Page = db.Pages
    .Where(p => p.PageId == Id)
    .Include(p => p.Series.Select(c => c.Comics.OrderBy(o => o.ReadingOrder)
                          .Select(col => col.Collection)))
    .SingleOrDefault();

But this results in the following error:

The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties. Parameter name: path

Any ideas what this error means?

Thanks in advance

EDIT:

My models:

public class Page
{
    public int PageId { get; set; }
    public string Title { get; set; }
    public ICollection<Series> Series { get; set; }
}

public class Series
{
    public int SeriesId { get; set; }
    public int PageId { get; set; }
    public string Title { get; set; }
    public Page Page { get; set; }
    public ICollection<Comic> Comics { get; set; }
}

public class Comic
{
    public int ComicId { get; set; }
    public string Title { get; set; }
    public int ReadingOrder { get; set; }
    public string Subtitle { get; set; }
    public int CollectionId { get; set; }
    public Collection Collection { get; set; }

}

public class Collection
{
    public int CollectionId { get; set; }
    public string Title { get; set; }
    public ICollection<Comic> Comics { get; set; }
}

Answer 1

The exception "...Include path expression must refer to a navigation property..." basically complains that c.Comics.OrderBy is not a navigation property. (It's a legitimate complaint, I think.)

Actually it's not supported by EF to apply sorting (and also filtering) in eager loading statements (Include).

So, what can you do?

Option 1:

Sort in memory after you have loaded the entity:

model.Page = db.Pages
    .Where(p => p.PageId == Id)
    .Include(p => p.Series.Select(c => c.Comics
                          .Select(col => col.Collection)))
    .SingleOrDefault();

if (model.Page != null)
{
    foreach (var series in model.Page.Series)
        series.Comics = series.Comics.OrderBy(c => c.ReadingOrder).ToList();
}

Ugly, but because you are loading apparently only a single Page object by id it's possibly faster (LINQ to Objects in memory) than the following options (if Series and Comics collections are not extraordinarily long).

Option 2:

Break down the query in parts which mix eager and explicite loading:

model.Page = db.Pages
    .Where(p => p.PageId == Id)
    .Include(p => p.Series) // only Series collection is included
    .SingleOrDefault();

if (model.Page != null)
{
    foreach (var series in model.Page.Series)
        db.Entry(series).Collection(s => s.Comics).Query()
          .Include(c => c.Collection)
          .OrderBy(c => c.ReadingOrder)
          .Load(); // one new DB query for each series in loop
}

Option 3:

Projection?

Here and here is by the way something about the dangers of complex Include chains of multiple navigation properties. It can load huge amounts of duplicated data. Include ensures that you only have one DB roundtrip but possibly at the cost of much more transfered data. Explicite loading has multiple roundtrips but with possibly less data in total.

(I know, I gave you this Include...Select...Select...Select... chain, but how could I know that you would take me serious :). Well, depending on the size of your nested collections it can still be the best option.)

Answer 2

Off the top of my head, untested:

model.Page = db.Pages
               .Where(p => p.PageId == Id)
               .Include(p => p.Series
                              .Select(c => c.Comics
                                            .Select(col => col.Collection)
                                            .OrderBy(o => o.ReadingOrder)))
               .SingleOrDefault();