entity-framework - 挑战 : Getting Linq-to-Entities to generate decent SQL without unnecessary joins

Question

我最近在 msdn 上的 Entity Framework 论坛中遇到了一个问题:
http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/bb72fae4-0709-48f2-8f85-31d0b6a85f68

提出问题的人尝试做一个相对简单的查询，涉及到两个表、一个分组、一个排序依据和一个使用 Linq-to-Entities 的聚合。一个非常简单的 Linq 查询，在 SQL 中也很简单——人们每天都在尝试做的事情。

然而，当使用 Linq-to-Entities 时，结果是一个复杂的查询，有很多不必要的连接等。我试过了，如果只使用纯 Linq，则无法让 Linq-to-Entities 从中生成一个像样的 SQL 查询针对 EF 实体。

看到来自 EF 的大量怪物查询后，我认为 OP(和我，and others)可能做错了什么。也许有更好的方法来做到这一点？

所以这是我的挑战:使用 the example from the EF forum并且仅对两个实体使用 Linq-to-Entities，是否可以让 EF 生成 SQL 查询而无需不必要的连接和其他复杂性？

我希望看到 EF 生成的东西更接近于 Linq-to-SQL 对同类查询所做的事情，同时仍然针对 EF 模型使用 Linq。

限制:使用 EFv1 .net 3.5 SP1 或 EFv4(beta 1 是 VS2010/.net4 beta 的一部分，可从 Microsoft 下载)。不允许使用 CSDL->SSDL 映射技巧、模型“定义查询”、存储过程、数据库端函数或 View 。模型和数据库之间只是简单的 1:1 映射，以及执行 MSDN 上原始线程所要求的纯 L2E 查询。两个实体之间必须存在关联(即我对原始线程的“解决方法＃1”答案不是有效的解决方法)

更新:添加了 500pt 的赏金。玩得开心。

更新:如上所述，使用 EFv4/.net 4(β1 或更高版本)的解决方案当然有资格获得赏金。如果您使用 .net 4 post β1，请包括内部版本号(例如 4.0.20605)、您使用的 L2E 查询以及它生成并发送到数据库的 SQL。

更新:这个问题已在 VS2010/.net 4 beta 2 中得到修复。虽然生成的 SQL 仍然有几个 [相对无害] 额外的嵌套级别，但它并没有像以前那样做任何疯狂的事情。 SQL Server 优化器试运行后的最终执行计划现在已达到最佳状态。+++ 用于负责 EFv4 的 SQL 生成部分的老兄和老兄……

Answer 1

如果我那么担心疯狂的 SQL，我就不会在数据库中进行任何分组。我将首先查询我需要的所有数据，方法是使用 ToList() 完成它，同时使用 Include 函数在单个选择中加载所有数据。

这是我的最终结果:

var list = from o in _entities.orderT.Include("personT")
           .Where(p => p.personT.person_id == person_id && 
                       p.personT.created >= fromTime && 
                       p.personT.created <= toTime).ToList()
           group o by new { o.name, o.personT.created.Year, o.personT.created.Month, o.personT.created.Day } into g
           orderby g.Key.name
           select new { g.Key, count = g.Sum(x => x.price) };

这导致一个更简单的选择:

SELECT 
1 AS [C1], 
[Extent1].[order_id] AS [order_id], 
[Extent1].[name] AS [name], 
[Extent1].[created] AS [created], 
[Extent1].[price] AS [price], 
[Extent4].[person_id] AS [person_id], 
[Extent4].[first_name] AS [first_name], 
[Extent4].[last_name] AS [last_name], 
[Extent4].[created] AS [created1]
FROM    [dbo].[orderT] AS [Extent1]
LEFT OUTER JOIN [dbo].[personT] AS [Extent2] ON [Extent1].[person_id] = [Extent2].[person_id]
INNER JOIN [dbo].[personT] AS [Extent3] ON [Extent1].[person_id] = [Extent3].[person_id]
LEFT OUTER JOIN [dbo].[personT] AS [Extent4] ON [Extent1].[person_id] = [Extent4].[person_id]
WHERE ([Extent1].[person_id] = @p__linq__1) AND ([Extent2].[created] >= @p__linq__2) AND ([Extent3].[created] <= @p__linq__3)

此外，通过提供的示例数据，SQL Profiler 仅注意到 SQL 调用的持续时间增加了 3 毫秒。

就个人而言，我认为任何提示不喜欢 ORM 层的输出 SQL 的人都应该重新使用存储过程和数据集。他们只是还没有准备好进化，需要在众所周知的 toastr 中再呆几年。 :)