EF 4.1代码优先：使用Include和/或Select方法时如何订购导航属性？ [英] EF 4.1 code-first: How to order navigation properties when using Include and/or Select methods?

问题描述

这是一个问题的第二步，解释为这里（EF 4.1代码第一：如何加载相关数据（parent-child-grandchild）？）。
通过 @Slauma 的指导，我可以检索数据。我的第一个代码是这样的：

This is the second step of a question explained here (EF 4.1 code-first: How to load related data (parent-child-grandchild)? ). By @Slauma's guidance I could retrieve data. My first code was this:

        var model = DbContext.SitePages
            .Where(p => p.ParentId == null && p.Level == 1)
            .OrderBy(p => p.Order) // ordering parent 
            .ToList();
        foreach (var child in model) { // loading children
            DbContext.Entry(child)
                .Collection(t => t.Children)
                .Query()
                .OrderBy(t => t.Order) // ordering children
                .Load();
            foreach (var grand in child.Children) { // loading grandchildren
                DbContext.Entry(grand)
                .Collection(t => t.Children)
                .Query()
                .OrderBy(t => t.Order) // ordering grandchildren 
                .Load();
            }
        }

它可以工作，但是这会发送很多查询数据库和我正在搜索一个方法来完成这一切只需一个查询。 @Slauma 的指导（以上链接说明）我将查询更改为此：

It works, but, this will send many queries against the database and I was searching a way to do this all in just one query. By @Slauma's guidance (explained on above link) I change the query to this one:

        var model2 = DbContext.SitePages
            .Where(p => p.ParentId == null && p.Level == 1)
            .OrderBy(p => p.Order)
            .Include(p => p.Children // Children: how to order theme???
                .Select(c => c.Children) // Grandchildren: how to order them???
            ).ToList();

现在，如何在选择子项（和孙子）时（如上面的第一个代码）

Now, how can I order children (and grandchildren) when selecting them (such as first code above)?

推荐答案

不幸的是加载（ Include ）不支持过滤或排序加载的儿童集合。有三个选项来实现你想要的：

Unfortunately eager loading (Include) doesn't support any filtering or sorting of loaded child collections. There are three options to achieve what you want:

• 通过明确的排序加载对数据库进行多次往返。这是您问题的第一个代码片段。请注意，多次往返并不一定是坏的， Include 并嵌套 Include 可能导致数据库和数据库之间转移数据的巨大倍增客户端。

使用e $加载 Include 或 Include （....选择（....））并在内存中加载数据后排序数据：

Use eager loading with Include or Include(....Select(....)) and sort the data in memory after they are loaded:

var model2 = DbContext.SitePages
    .Where(p => p.ParentId == null && p.Level == 1)
    .OrderBy(p => p.Order)
    .Include(p => p.Children.Select(c => c.Children))
    .ToList();

foreach (var parent in model2)
{
    parent.Children = parent.Children.OrderBy(c => c.Order).ToList();
    foreach (var child in parent.Children)
        child.Children = child.Children.OrderBy(cc => cc.Order).ToList();
}

使用投影：

Use a projection:

var model2 = DbContext.SitePages
    .Where(p => p.ParentId == null && p.Level == 1)
    .OrderBy(p => p.Order)
    .Select(p => new
    {
        Parent = p,
        Children = p.Children.OrderBy(c => c.Order)
            .Select(c => new
            {
                Child = c,
                Children = c.Children.OrderBy(cc => cc.Order)
            })
    })
    .ToList() // don't remove that!
    .Select(a => a.Parent)
    .ToList();

这只是一个往返行程，如果您不要禁用更改跟踪（请勿在此查询中使用 .AsNoTracking（））。此投影中的所有对象都必须加载到上下文中（第一个 ToList（）是必要的），并且上下文将导航属性正确地绑定在一起（这是一个功能称为关系跨度）。

This is only a single roundtrip and works if you don't disable change tracking (don't use .AsNoTracking() in this query). All objects in this projection must be loaded into the context (the reason why the first ToList() is necessary) and the context will tie the navigation properties correctly together (which is a feature called "Relationship span").

这篇关于EF 4.1代码优先：使用Include和/或Select方法时如何订购导航属性？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！