问题描述
我最近在msdn的实体框架论坛上遇到了一个问题：
）正在做错事。可能还有更好的方法呢？
Having seen a fair share of monster queries from EF I thought maybe the OP (and me, and others) are doing something wrong. Maybe there is a better way to do this?
所以这里是我的挑战：使用，并且只对两个实体使用Linq-to-Entities，是不是可能得到EF生成一个SQL查询，而不需要不必要的连接和其他复杂性？
So here's my challenge: using the example from the EF forum and using just Linq-to-Entities against the two entities, is it possible to get EF to generate a SQL query without unnecessary joins and other complexities?
我想看到EF生成一些更接近Linq-to- SQL对于同样的查询，同时仍然使用Linq对EF模型。
I'd like to see EF generate something a little bit closer to what Linq-to-SQL does for the same kind of queries, while still using Linq against a EF model.
限制：使用EFv1 .net 3.5 SP1或EFv4 （beta 1是可从Microsoft下载的VS2010 / .net4 beta的一部分）。没有CSDL-> SSDL映射技巧，模型'definequeries'，存储过程，db-side函数或允许的视图。只需简单的1：1模型和数据库之间的映射，以及一个纯粹的L2E查询，就可以在MSDN上询问原始线程。两个实体之间必须存在关联（即我的解决方法＃1原始线程的答案不是有效的解决方法）
Restrictions: use EFv1 .net 3.5 SP1 or EFv4 (beta 1 is part of the VS2010/.net4 beta available for download from Microsoft). No CSDL->SSDL mapping tricks, model 'definingqueries', stored procs, db-side functions, or views allowed. Just plain 1:1 mapping between the model and the db and a pure L2E query that does what the original thread on MSDN asked. An association must exist between the two entities (i.e. my "workaround #1" answer to the original thread is not a valid workaround)
更新：添加500pt赏金。获得乐趣。
Update: 500pt bounty added. Have fun.
更新：如上所述，使用EFv4 / .net 4（β1或更高版本）的解决方案当然符合赏金。如果您使用.net 4 postβ1，请包括内部版本号（例如4.0.20605），您使用的L2E查询及其生成并发送到DB的SQL。
Update: As mentioned above, a solution that uses EFv4 / .net 4 (β1 or later) is of course eligible for the bounty. If you're using .net 4 post β1, please include build number (e.g. 4.0.20605), the L2E query you used, and the SQL it generated and sent to the DB.
更新：此问题已在VS2010 / .net 4 beta 2中修复。尽管生成的SQL仍然有几个[相对无害]额外的嵌套级别，但它不会任何它曾经的坚果的东西。 SQL Server优化器之后的最终执行计划现在已经很好了。 +++为负责EFv4的SQL生成部分的dudes和dudettes ...
Update: This issue has been fixed in VS2010 / .net 4 beta 2. Although the generated SQL still has a couple of [relatively harmless] extra levels of nesting, it doesn't do any of the nutty stuff it used to. The final execution plan after SQL Server's optimizer has had a go at it is now as good as it can be. +++ for the dudes and dudettes responsible for the SQL generating part of EFv4...
推荐答案
如果我担心疯狂的SQL，我只是不会在数据库中进行任何分组。我将首先查询我使用ToList（）完成的所有数据，同时使用Include函数在单个选择中加载所有数据。
If I was that worried about the crazy SQL, I just wouldn't do any of the grouping in the database. I would first query all of the data I needed by finishing it off with a ToList() while using the Include function to load all the data in a single select.
这里是我的最终结果：
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) };
这导致一个更简单的选择：
This results in a much simpler select:
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 ms。
Additionally, with the example data provided, SQL Profiler only notices a 3 ms increase in duration of the SQL call.
我个人认为，任何关于不喜欢ORM层输出SQL的人都应该去回到使用存储过程和数据集。他们根本没有准备好进化，需要在谚语烤箱里花更多的时间。 ：）
Personally, I think that anyone that whines about not liking the output SQL of an ORM layer should go back to using Stored Procedures and Datasets. They simply aren't ready to evolve yet, and need to spend a few more years in the proverbial oven. :)
这篇关于挑战：让Linq-to-Entities产生不必要的连接的体面的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！