How to order child collections of entities in EF

Question

I have the following query:

public IEnumerable<Team> GetAllTeamsWithMembers(int ownerUserId)
        {
            return _ctx.Teams
                 .Include(x => x.TeamMembers)
                 .Where(x => x.UserId == ownerUserId)
                 .OrderBy(x => x.Name).ToList();

        }

How do I go about ordering the teams by their name, and then have all the child members of each team sorted by their name?

It seems that do this I need to create a new DTO class and use a select. I'd like to use the EF entities already created, in this case Team has a navigation property to Members. I return IEnumerable<Team> out from my repository layer.

There doesn't seem to be a neat way of ordering child collections in EF. Can anyone help?

Here are various examples: http://stackoverflow.com/a/7181574/270591, http://stackoverflow.com/a/7528266/270591, http://stackoverflow.com/a/7395406/270591 — Slauma, Dec 09 '11 at 15:12

score 28 · Accepted Answer · answered Dec 09 '11 at 15:08

You could load the data and sort in memory after loading it.

IEnumerable<Team> teams = _ctx.Teams
            .Include(x => x.TeamMembers)
            .Include(x => x.TeamMembers.Select(u => u.User))
            .Where(x => x.UserId == ownerUserId)
            .OrderBy(x => x.Name).ToList();

foreach (var team in teams)
{
    team.TeamMembers = team.TeamMembers.OrderBy(m => m.Name);
    foreach (var teamMember in team.TeamMembers)
    {
        teamMember.Users = teamMember.Users.OrderBy(u => u.Name);
    }
}

Or you could use Projections and use the Change Tracking of EF to sort your collection. Here is an example of filtering an Include but the same works for Ordering.

Thanks for this, simple and effective. I thought there might be something a little more LINQish, not keen on the loop but it does the job! — jaffa, Dec 09 '11 at 16:11

Matthew Steven Monkan · Answer 2 · 2019-03-23T01:17:02.073

Unfortunately eager loading (Include) doesn't support any filtering or sorting of loaded child collections.

But as stated in the accepted answer, you can sort each collection after they've been eager loaded. I wrote an extension method that handles sorting a collection by a child property easily in place so there is no need for reassignment:

public static class Extensions
{
    public static void SortOn<T>(this List<T> enumerable, Expression<Func<T, object>> expression)
    {
        var type = expression.Parameters[0].Type;
        var memberName = ((MemberExpression)((UnaryExpression)expression.Body).Operand).Member.Name;

        enumerable.Sort((x, y) =>
        {
            var first = (IComparable)type.GetProperty(memberName).GetValue(x);
            var second = (IComparable)type.GetProperty(memberName).GetValue(y);
            return first.CompareTo(second);
        });
    }
}

Then sorting the child collections could be succinctly written as:

foreach (var team in teams)
    team.TeamMembers.SortOn(_ => _.Name);

Ricky Smith · Answer 3 · 2011-12-09T16:13:34.213

2

You could dump your teams and their team members into an anonymous type (probably not what you want), like this:

public IEnumerable<Team> GetAllTeamsWithMembers(int ownerUserId)
{
    return (from t in _ctx.Teams
        where t.UserId == ownerUserId
        select new {
            Team = t,
            TeamMembers = t.TeamMembers.OrderBy(m => m.Name)
        }).ToList()
}

You can then loop through them:

foreach(Team team in GetAllTeamsWithMembers(1234))
{
    string teamName = team.Team.Name;
    string firstTeamMemberName = team.TeamMembers.First().Name;
}

Update: For the record, my opinion is to not use this solution, but to sort each collection in a loop or during rendering/binding.

I removed the 2nd solution, since it was pointed out that EF cannot select into entities.

edited Dec 09 '11 at 16:13

answered Dec 09 '11 at 15:16

Ricky Smith

2,379
1
13
29

The second option doesn't work unfortunately because LINQ to Entities doesn't allow to project into an entity. – Slauma Dec 09 '11 at 15:26
The first option does work if you add a .Select(a => a.Team) after your ToList(). – Wouter de Kort Dec 09 '11 at 15:30
@WouterdeKort If you do that, then he's omit the ordered list of Team Members, wouldn't he? The ordered list wouldn't be stored in the Team.TeamMembers property, but as a sibling of Team in a new anonymous type. – Ricky Smith Dec 09 '11 at 16:03
@RickySmith No that's not true. Because you have activated ChangeTracking the automatic relation fixup will set the TeamMembers property of the Team and because the entities are sorted when they are added to the ChangeTracker this will work. – Wouter de Kort Dec 09 '11 at 16:12

score 1 · Answer 4 · answered Dec 10 '20 at 22:16

My solution :

     var data = await db.lessonTasks
               .Select(res => new
               {
                   details = res,
                   appos  = res.appos.OrderBy(ap=>ap.dateTime).ToList(),
               })
               .OrderBy(r=>r.appos.First().dateTime)
               .ToListAsync();

How to order child collections of entities in EF

4 Answers4

Linked