EF Core: Order By of nested eager-loaded collection

Question

I have a use-case with a deeply nested class hierarchy, for example like this:

public class Parent
{
    public int Id { get; set; }

    public List<ChildOne> Children { get; set; }
}


public class ChildOne
{
    public int Id { get; set; }
    public int ParentId { get; set; }

    public List<ChildTwo> ChildrenTwo { get; set; }
}


public class ChildTwo
{
    public int Id { get; set; }
    public int Priority { get; set; }

    public int ChildOneId { get; set; }

    public List<ChildThree> ChildrenThree { get; set; }
}


public class ChildThree
{
    public int Id { get; set; }

    public int ChildTwoId { get; set; }
}

If I want to load all parent-objects and their related children levels, I'd do this:

var objects = context.Parent
    .Include(parent => parent.Children)
        .ThenInclude(childOne => childOne.ChildrenTwo)
            .ThenInclude(childTwo => childTwo.ChildrenThree)
    .ToList();

But what if I want my ChildrenTwo entities in the eager-loaded navigational property of ChildOne to be ordered by their Priority? I've done some research, and from the links below (and some others), it is apparently not directly possible in EF Core (yet):

So, how can you achieve the ordering of the ChildrenTwo above (by Priority) in a good/clean way that is fast? That probably means most of the work should happen on the DB server and not on the .NET client side. What's the best approach here?

Only by projecting (`select new { ... }`) to DTOs or to the original entity classes, applying ordering where necessary in the LINQ statement. — Gert Arnold, Dec 27 '18 at 22:45
@GertArnold Thanks for your reply. I'm not sure if I understand fully - could you give an example? — Martin S., Dec 29 '18 at 14:47

score 0 · Answer 1 · answered Apr 20 '20 at 20:57

Though it is very late to answer, but it may help the future readers:

I will explain the code:

var authorArticles = await _context.AuthorArticles
                .Include(a => a.Author)
                .ThenInclude(p => p.Person)
                .ThenInclude(pq => pq.Qualifications)
                .ThenInclude(q => q.QualificationSubject)
                .Include(a => a.Author)
                .ThenInclude(p => p.Person)
                .ThenInclude(pp => pp.Professions)
                .Include(a => a.Author)
                .ThenInclude(p => p.Person)
                .ThenInclude(pp => pp.Professions)
                .ThenInclude(prof => prof.Profession)
                .Where(aa => aa.ArticleId == articleId)
                .Select(s => new AuthorArticle
                {
                    Author = new Author
                    {
                        Affiliation = s.Author.Affiliation,
                        AvailableAsReviewer = s.Author.AvailableAsReviewer,
                        Person = new Person
                        {
                            Email = s.Author.Person.Email,
                            FirstName = s.Author.Person.FirstName,
                            LastName = s.Author.Person.LastName,
                            MiddleName = s.Author.Person.MiddleName,
                            Title = s.Author.Person.Title,
                            FullName = s.Author.Person.FullName,
                            UserId = s.Author.Person.UserId,
                            Professions = new Collection<PersonProfession>
                            {
                                new PersonProfession
                                {
                                    // using sorting here!!
                                    Organization = s.Author.Person.Professions
                                        .OrderByDescending(pid => pid.ProfessionId)
                                        .FirstOrDefault().Organization,
                                    Profession = s.Author.Person.Professions
                                        .OrderByDescending(pid => pid.ProfessionId)
                                        .FirstOrDefault().Profession
                                }
                            },
                            Qualifications = new Collection<PersonQualification>
                            {
                                new PersonQualification
                                {
                                    QualificationSubject = s.Author.Person.Qualifications
                                        .OrderByDescending(q => q.QualificationLevelId)
                                        .FirstOrDefault().QualificationSubject,
                                    QualificationLevelId = s.Author.Person.Qualifications
                                        .OrderByDescending(q => q.QualificationLevelId)
                                        .FirstOrDefault().QualificationLevelId
                                }
                            }
                        }
                    },
                    IsCorresponding = s.IsCorresponding,
                    AuthorPosition = s.AuthorPosition
                }).ToListAsync();

            return authorArticles;

If you simply eager loaded the entities, then at the time of projection; which means when you are selecting the items from the query, you can recreate the object that has already been provided in slightly different way. In my case, I wanted only one profession of the person out of many and same goes for the qualification of the person.

Took help of select from Another SO great answer!

EF Core: Order By of nested eager-loaded collection

1 Answers1