Sorting Collection of One-To-Many relationship - Entity Framework Codefirst

Question

I have a codefirst entity model which has two entities, City and Locality. City has one to many relation with Locality. Here is how they are related.

public class City {
    public virtual List<Locality> Localities { get; set; }
}

public class Locality {
    public virtual City City { get; set; }
}

Now I have some code which finds a particular City and loads Localities for that city too. I was looking to sort the loaded Localities by "Name" using LINQ IQueryable something like this

city = context.Cities.AsNoTracking().Include(c => c.Localities.OrderBy(l => l.Name))
                                    .Where(c => c.Id == id).FirstOrDefault();

When I use the above code it throws "ArgumentException". So I tried the code shown below.

city = context.Cities.AsNoTracking().Include(c => c.Localities)
                                    .Where(c => c.Id == id).FirstOrDefault();
if (city != null) {
     city.Localities.OrderBy(l => l.Name);
}

The above code does not throw any exceptions but it doesn't sort the the Localities by Name. I get Localities sorted by Id instead.

Any ideas how to sort the "many side" of One-to-Many relationship

Answer 1

LINQ queries do not modify the source collection. You need to create a new sorted City.Localities instance:

if (city != null) {
    city.Localities = city.Localities.OrderBy(l => l.Name).ToList();
}

This sorts in memory after the parent and the child collection has been loaded.

And your code first snippet doesn't work because you cannot specify sort or filter criteria in an eager loading statement.

Here is another answer with some more remarks to the same problem: Entity Framework, MVC 3, OrderBy in LINQ To Entities