how sort and filter on eager fetch

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

filters a on child fields

sorts on both parent and child

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> ie

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

---

The solution depends on what exactly you are trying to do.

The first query gives the impression that you want to "flatten out" the results in objects, like this (pseudocode, I hope it's clear what I mean):

{ Parent1, Child1 }
{ Parent1, Child2 }
{ Parent1, Child3 }
{ Parent2, Child1 }

In this case each result "row" would be an object having a Parent and a Child property, and you could sort by parent name and then by child name.

The second query just returns the Parent objects and (you don't show it but I assume EF has been instructed to do that) each one has a Children collection. In this case you can only sort by parent name; if you want to sort each Parent 's children, sort the Children collection on that object by itself.

Which of the two do you want to do?

Update

OK, it seems you want the second one. I don't believe it can be done directly. You can just do it when you enumerate the results - since the Parent s are already sorted, simply sort each one's children:

var sortedChildren = parent.Children.OrderBy(c => c.Name);

---

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();

---

预取子字段：

using (BlogDataContext context = new BlogDataContext())
{
    DataLoadOptions options = new DataLoadOptions();
    options.LoadWith<Blog>(c => c.Categories);
    options.LoadWith<Blog>(c => c.Title);
    context.LoadOptions = options;
    Blog blog = context.Blogs.Single<Blog>(c => c.BlogId == 1);
}

链接地址: http://www.djcxy.com/p/37648.html

上一篇: 实体框架：查询子实体

下一篇: 如何对渴望获取进行排序和过滤