entity framework 4 - LINQ Query - how sort and filter on eager fetch

Question

How do I do a eager query of a parent child relationship that:

1. filters a on child fields
2. sorts on both parent and child
3. return a List or Parents with the children pre-populated

If I try

from p in _context.Parents.Include("children")
join c in _context.childrenon p.Id equals c.ParentId 
where d.DeletedDate == null
orderby p.Name ascending, c.Name 
select p

Then I get the Parent object back but each Parent has NULL for children

if I try

from p in _context.Parents.Include("children")
orderby p.Name ascending
select p

The query returns all Parents and children but they are not filtered or sorted.

The result I want back is a IEnumerable<Parent> i.e.

Parent[0].name = "foo"
Parent[0].children = IEnumerable<Child>
Parent[1].name = "bar"
Parent[1].children = IEnumerable<Child>

Answer 1

There is no direct way of doing this, but you can use somewhat of a workaround - project the parent and children onto an annonymous object and then select and return the parent from the object.

See similar question: Linq To Entities - how to filter on child entities

In your case you will have something along the lines of:

var resultObjectList = _context.
                       Parents.
                       Where(p => p.DeletedDate == null).
                       OrderBy(p => p.Name).
                       Select(p => new
                                 {
                                     ParentItem = p,
                                     ChildItems = p.Children.OrderBy(c => c.Name)
                                 }).ToList();

List<Parent> resultingCollection = resultObjectList.Select(o => o.ParentItem).ToList();