在EF 4.1 Code-First中使用Include和/或Select方法时，如何对导航属性进行排序? [英] Order navigation properties when using Include and/or Select methods with EF 4.1 Code-First?

问题描述

这是这里解释的问题的第二步: @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屋！