如何在 ADO.NET Entity Framework 中通过预先加载对相关实体进行排序 [英] How to sort related entities with eager loading in ADO.NET Entity Framework

问题描述

您好，

考虑到Northwind 示例表Customers、Orders 和OrderDetails，我希望预先加载与上述表相对应的相关实体，但我需要在获取实体之前对数据库中的子实体进行排序.

Considering the Northwind sample tables Customers, Orders, and OrderDetails I would like to eager load the related entities corresponding to the tables mentioned above and yet I need ot order the child entities on the database before fetching entities.

基本情况:

var someQueryable = from customer in northwindContext.Customers.Include("Orders.OrderDetails") 
select customer;

但我还需要根据这些表上的一些随机列在数据库端(在将这些实体提取到内存之前)对 Orders 和 OrderDetails 进行排序.是否有可能没有一些投影，就像在 T-SQL 中一样?解决方案是使用 e-SQL 还是 LINQ to Entities 并不重要.我搜索了网络，但我对找到的答案并不满意，因为它们主要涉及将数据投影到某个匿名类型，然后重新查询该匿名类型以按照您喜欢的顺序获取子实体.同样使用 CreateSourceQuery() 对我来说似乎不是一个选项，因为我需要获取数据库端的数据，通过急切加载但只需订购子实体.那就是我想在执行任何查询之前执行ORDER BY"，然后按我想要的顺序获取实体.提前感谢您的任何指导.作为个人说明，请原谅直接语言，因为我对微软以如此不成熟的形式发布 EF 感到有点生气，即使与 Linq to SQL 相比(他们似乎正在慢慢摆脱).我希望这个 EF 东西会变得更好，并且在 .NET FX 4.0 的发布版本中没有重大错误.

but I also need to sort Orders and OrderDetails on the database side (before fetching those entities into memory) with respect to some random column on those tables. Is it possible without some projection, like it is in T-SQL? It doesn't matter whether the solution uses e-SQL or LINQ to Entities. I searched the web but I wasn't satisfied with the answers I found since they mainly involve projecting data to some anonymous type and then re-query that anonymous type to get the child entities in the order you like. Also using CreateSourceQuery() doesn't seem to be an option for me since I need to get the data as it is on the database side, with eager loading but just by ordering child entities. That is I want to do the "ORDER BY" before executing any query and then fetch the entities in the order I'd like. Thanks in advance for any guidance. As a personal note, please excuse the direct language since I am kinda pissed at Microsoft for releasing the EF in such an immature shape even compared to Linq to SQL (which they seem to be getting away slowly). I hope this EF thingie will get much better and without significant bugs in the release version of .NET FX 4.0.

推荐答案

其实我有提示正好解决了这个问题.

Actually I have Tip that addresses exactly this issue.

不支持"对相关实体进行排序，但使用投影方法 Craig 显示 AND 依靠称为Relationship Fixup"的东西，您可以获得非常相似的工作:

Sorting of related entities is not 'supported', but using the projection approach Craig shows AND relying on something called 'Relationship Fixup' you can get something very similar working:

如果你这样做:

var projection = from c in ctx.Customers
                 select new {
                       Customer = c, 
                       Orders = c.Orders.OrderByDescending( 
                                 o => o.OrderDate 
                             )
                 };

foreach(var anon in projection )
{
   anon.Orders //is sorted (because of the projection)
   anon.Customer.Orders // is sorted too! because of relationship fixup
}

这意味着如果你这样做:

Which means if you do this:

var customers = projection.AsEnumerable().Select(x => x.Customer);

您将拥有已排序订单的客户！

you will have customers that have sorted orders!

查看提示了解更多信息.

See the tip for more info.

希望对你有帮助

亚历克斯

这篇关于如何在 ADO.NET Entity Framework 中通过预先加载对相关实体进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！