在单独的查询中加载相关实体并对子集合进行排序 [英] Loading related entities in separate queries and sorting of children collection

问题描述

我首先需要使用EF 4.1代码检索包含在几个相关表中的数据。当前如果我使用此

I need to retrieve data using EF 4.1 Code first that are contained in several related tables. Currently if i use this

return Context.Set<Entity>()
              .Include(x => x.Children.Select(y => y.GrandChildren.Select(z => z.Child)))
              .Include(x => x.SomeEntity)
              .Include(x => x.AnotherEntity)
              .OrderByDescending(x => x.Id)
              .FirstOrDefault();

数据正确获取，但我担心两件事：

The data is fetched correctly, but I am worried about two things:

1）似乎无法对Chilren / GrandChildren进行排序

1) it seems that there is no way to sort Chilren / GrandChildren

2）数据在一个表中被展平，这意味着为每个GrandChild记录复制实体（和所有其他）数据

2) the data is flattened in one table, which means that Entity (and all others) data is duplicated for each GrandChild record

问题：

• 要对Children / GrandChildren进行排序，我需要做什么？


• 在这种特殊情况下，第二点本身可能不是问题，因为传输的数据量不大-最多30条30列的记录。还是，我想知道是否有办法分别加载Enttity，Children和GrandChildren（3个查询），并在客户端站点上加入它们？

返回的结果集需要可更新。

Returned resultset needs to be updatable.

推荐答案

我需要做什么才能对Children / GrandChildren进行排序？

What Do I need to do to be able to sort Children / GrandChildren?

在应用程序中对它们进行排序。那是唯一可靠的方法。 Include根本不允许排序，并且下面显示的方法不可靠，因为您无法控制EF的内部机制，也无法将导航属性设置为 SortedList 或其他集合保持排序顺序（必须具有可靠的排序关系）。

Sort them in your application. That is the only reliable way. Include doesn't allow sorting at all and the approach showed below is not reliable because you don't have control over EF's internal mechanism and you cannot have your navigation property as SortedList or other collection maintaining the sort order (which is requirement to have reliably sorted relation).

数据将在一个表中展平。每个GrandChild记录的实体（和所有其他）数据都是重复的

the data is flattened in one table, which means that Entity (and all others) data is duplicated for each GrandChild record

这是有效异议。您可以通过关闭延迟加载并使用单独的查询来加载数据来避免这种情况：

This is valid objection. You can avoid it by turning off lazy loading and use separate queries to load data:

context.Configuration.LazyLoadingEnabled = false;
var parent = context.Set<Entity>().First(e => e.Name = "ABC");
// Load relations in separate query
context.Set<Child>()
       .Where(c => c.Parent.Name == "ABC")
       .OrderBy(c => c.Name) // You can at least try it but as mentioned above it may not work in all scenarios
       .Load();
// Now parent.Children collection should be filled 

关系以及嵌套关系。关键是正确构造在何处装载子孙的条件。

You can follow same approach for other relation and for nested relations as well. The key is to correctly construct Where condition for children and grandchildren loading.

这并不意味着这种方法会更快地使表格变平。这种方法对每个执行的查询都进行了单独的数据库往返，因此在较小的数据集中，展平的表可以更快。

It doesn't mean that this approach will be faster that flattened table. This approach makes separate database roundtrip for every executed query so the flattened table can be faster in smaller data sets.

这篇关于在单独的查询中加载相关实体并对子集合进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！