Ordering Included Entity

Question

I am using entity framework v2.2.3. I am trying to order a sub-entity by using the following code but it did not work.

var result= _databaseContext.Movie.AsNoTracking().Include(p => p.Cast.OrderByDescending(c => c.Birthday)).ToList();

Error:

The Include property lambda expression 'p => {from Cast c in p.Casts orderby [c].Birthday desc select [c]}' is invalid. The expression should represent a property access: 't => t.MyProperty'. To target navigations declared on derived types, specify an explicitly typed lambda parameter of the target type, E.g. '(Derived d) => d.MyProperty'. For more information on including related data, see http://go.microsoft.com/fwlink/?LinkID=746393.

Entities:

public class Movie
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public List<Cast> Casts { get; set; }
    }

  public class Cast
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public DateTime Birthday { get; set; }
    }

Do you have any idea?

Show your model classes (Movie and Cast) and I can complete my answer.. — Risto M, Mar 24 '19 at 12:38
@RistoM I added entities, your answer does not work for my scenario — leo, Mar 24 '19 at 15:59

Risto M · Accepted Answer · 2019-03-24T16:19:29.577

2

There is no way to sort eager-loaded (.Include-queried) child collections as we have seen here and here.

You have to first load Movies and then sort their Casts-collections. Try this:

var result= _databaseContext.Movie.AsNoTracking().Include(p => p.Casts).ToList();

results.ForEach(x => x.Casts = x.Casts.OrderBy(y => y.BirthDay).ToList());

edited Mar 24 '19 at 16:19

answered Mar 24 '19 at 12:35

Risto M

2,647
11
24

I added entities to the question. As you can see the Movie has a cast collection, so this one does not work for me. Do you have any other solution? – leo Mar 24 '19 at 15:57
Thanks for additional information. Now when relationship is one-to-many, It is not 100% clear what do you want to sort: Do you want to sort your Movies-collection (i.e by criteria oldest-cast-member in movie)? Or do you want to sort every Movie's Cast-collection? – Risto M Mar 24 '19 at 16:05
I@RistoM I want to get movies with casts but cas list for each movie must be sorted by “Birthday”. Is it clear now? – leo Mar 24 '19 at 16:18
Is there a way to handle it before getting the list, I mean in query part? – leo Mar 24 '19 at 16:26
No. When selecting with .Include -clause, answer is unfortunately no. You have to create your own sql (With i.e Dapper-library) or load Casts with own ordered query separate to Movies loading. There are good discussion on those links I put on this answer. – Risto M Mar 24 '19 at 16:30

Ordering Included Entity

1 Answers1