Sorting children when selecting a parent using LINQ-to-Entities

Question

Imagine that you have Entity Framework entities that look like this (obviously, not these specific classes, but autogenerated with all Entity Framework plumbing, this is just for illustration):

public class Parent { public int ID { get; set; } public List<Child> Children { get; set; } } public class Child { public int ID { get; set; } public Parent Parent { get; set; } public int Number { get; set; } } 

I have a LINQ query that looks like this:

 from parent in context.Parents.Include("Child") select parent 

However, this returns a list of parents in which the children are in input order. I want children to be sorted by their number in their parent. How can I do that?

Edit:. Clarification: The idea is to hide the request behind a method call (on the facade of the layer), which simply returns an IList<Parent> . This makes using solutions like anonymous class queries and manual sorting painful (compared to some panacea solutions where you can just do it in a query or something like that).

Answer 1

Alex James discusses this issue with this tip .

Essentially, relationships are considered disordered for standard relational modeling. Therefore, you cannot sort them. But you can project onto other collections that can be sorted.

Answer 2

Take a look at the post . You can try something like this:

 var query = ((from parent in context.Parents from child in parent.Child orderby child.Number ascending select parent) as ObjectQuery<Parent> ).Include("Child"); 

Answer 3

One parameter performs a query and sort in memory (for example, on output).

 var parents = context.Parents.Include("Child").ToList(); //note that ToList is here just to execute the query and get the objects in memory foreach (var p in parents) { //do something with parent item foreach (var c in p.Child.OrderBy(c => c.Number)) { /do something with the child item } } 

There are two more options that also work with their pros and cons:

LINQ ".Include" orderby in subquery

LINQ OrderBy Name ThenBy ChildrenCollection.Name

Answer 4

here is what i did:

 var query = from parent in context.Parents select new { parent, childs = from child in context.Child orderby child.ID ascending select new { child } } 

I did something like this and it worked very well for me