在 EF 4.1 Code-First 中使用 Include 和/或 Select 方法时订购导航属性? [英] Order navigation properties when using Include and/or Select methods with EF 4.1 Code-First?

问题描述

这是此处解释的问题的第二步:EF 4.1 代码优先:如何加载相关数据(父子孙)?.在 @Slauma 的指导下，我已经使用这种方法成功检索了数据:

This is the second step of a question explained here: EF 4.1 code-first: How to load related data (parent-child-grandchild)?. With @Slauma's guidance, I have successfully retrieved data with this approach:

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 的指导(在上述链接的答案中进行了解释)，我已将查询更改为:

Though this approach works, it sends many queries to the database and I am searching for a way to do this all in just one query. With @Slauma's guidance (explained in the answer at the above link), I have changed 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 shown in the first code example 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 可能导致数据库和客户端之间传输的数据成倍增加.

使用带有 Include 或 Include(....Select(....)) 的预加载，并在加载后对内存中的数据进行排序:

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 Code-First 中使用 Include 和/或 Select 方法时订购导航属性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！