Order by child table in entity structure
In my Linq, I am accessing a simple master / detail table:
_db.Countries.Include(x=>x.People);
I want people to be sorted by Lastname, but when I include the order in it, include it with the error:
The Include path expression must refer to a navigation property defined on the type.
Use dotted paths for reference navigation properties and the Select operator for collection navigation properties. Parameter name: path
source to share
You can do it.
var countries = _db.Countries
.Select(c => new
{
Country = c,
People = c.People.OrderBy(p => p.Lastname)
})
.AsEnumerable() // not execute yet
.Select(a => a.Country)
.ToArray(); // execute
Even if Select
only selects the country, People
the anonymous type will connect to the People property in each country.
The generated sql will include ordering as requested eg.
ORDER BY [Project1].[Id] ASC, [Project1].[C1] ASC, [Project1].[Lastname] ASC
PS
As a busy workload, you can make relationship fixes. There will only be one round trip database using this approach instead of first loading all countries and then loading each user.
Link Fix-up is the process by which EF links the Entities together. For example, a fix-up will set the order's Customer property when a known related Customer materializes from the database. Once you figure out what to fix, you can use it for all sorts of cool things. - "Vivid Language of Entities" by Alex D James
source to share