In EF, how do you organise the child collections of entities?

entity-framework linq

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?

1
30
12/9/2011 3:01:46 PM

Accepted Answer

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.

25
12/9/2011 3:08:24 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow