EF Code first Eager loading and OrderBy problem

Question

I have two entities called Category and Product with 1:n relation.

I want to get a Category with its childs that childs be in order.

This is my linq:

 _db.Categories.Where(c => c.CategoryID == catID)
    .Include(c => c.Products.OrderBy(p => p.ProductID))
    .SingleOrDefault();

This query enforce with the below exception because of orderby.

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

Answer 1

Include has to reference a navigation property, which means you can't include an OrderBy(). Instead of this:

_db.Categories
    .Where(c => c.CategoryID == catID)
    .Include(c => c.Products.OrderBy(p => p.ProductID))
    .SingleOrDefault();

...you'll have to use this:

_db.Categories
    .Where(c => c.CategoryID == catID)
    .Include(c => c.Products)
    .SingleOrDefault();

...to access an ordered list of Products for each Category, you could add a property to Category like this:

class Category
{
    public IEnumerable<Product> OrderedProducts
    {
        get { return this.Products.OrderBy(p => p.ProductID); }
    }
}

Answer 2

Eager loaded data cannot be ordered or filtered. That is linq-to-entities limitation and the only way how to order relations in the database is by using projection:

var data =  _db.Polls
               .Where(c => c.CategoryID == pollID)
               .Select(c => new 
                   {
                       Pool = c,
                       Products = c.Products.OrderBy(p => p.ProductID)
                   })
               .SingelOrDefault();

You can project to anonymous or custom type but you cannot project to mapped type (for example Poll).

Another way is dividing this to two queries and use explicit loading:

var poll = _db.Polls.SingleOrDefault(c => c.CategoryID == pollID);
_db.Entry(poll).Collection(c => c.Products)
               .Query()
               .OrderBy(p =>.ProductID)
               .Load();