为什么 LINQ to Entities 为我创建子查询？
我使用 .NET 4 和实体框架来构建一个简单的查询。下面是 C# 代码：
return Context.Files.Where(f => f.FileHash != 40)
.OrderByDescending(f => f.Created)
.Take(5);
当我使用 ObjectQuery.ToTraceString() 跟踪查询时，我发现以下子查询：
SELECT TOP (5)
[Project1].[ID] AS [ID],
-- <snip> lots of columns
[Project1].[FileHash] AS [FileHash]
FROM ( SELECT
[Extent1].[ID] AS [ID],
-- <snip> lots of columns
[Extent1].[FileHash] AS [FileHash]
FROM [dbo].[Files] AS [Extent1]
WHERE (LEN([Extent1].[FileHash])) <> 40
) AS [Project1]
ORDER BY [Project1].[Created] DESC
FileHash 被定义为 NVARCHAR(255)< /强>。
这对我来说似乎很奇怪，因为我认为不需要子查询。为什么 EF 为我这样做，我可以采取什么措施来避免我认为此类查询对性能造成的影响？
I'm using .NET 4 and the Entity Framework to construct a simple query. Here's the C# code:
return Context.Files.Where(f => f.FileHash != 40)
.OrderByDescending(f => f.Created)
.Take(5);
When I trace the query using ObjectQuery.ToTraceString(), I find the following subquery:
SELECT TOP (5)
[Project1].[ID] AS [ID],
-- <snip> lots of columns
[Project1].[FileHash] AS [FileHash]
FROM ( SELECT
[Extent1].[ID] AS [ID],
-- <snip> lots of columns
[Extent1].[FileHash] AS [FileHash]
FROM [dbo].[Files] AS [Extent1]
WHERE (LEN([Extent1].[FileHash])) <> 40
) AS [Project1]
ORDER BY [Project1].[Created] DESC
FileHash is defined as an NVARCHAR(255).
This seems strange to me, as I don't see any need for a subquery. Why is EF doing this for me, and is there something I can do to not take what I assume is a performance hit from such a query?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
首先，我怀疑这是否值得担心。我猜想如果您将 EF 生成的查询执行计划与“最佳”手写查询的查询执行计划进行比较，结果将实际上是相同的。我猜想唯一可能的惩罚是 EF 生成的查询使解析时间延长了几分之一秒。从长远来看，这可能不值得考虑。
至于为什么 EF 首先以这种方式生成查询，我非常确定这与将 LINQ 方法转换为有效 SQL 查询的固有复杂性有关。我确信，执行此转换的引擎是非常模块化的，每个模块都必须生成查询的一部分，可以轻松地将其合并到最终的完整查询中。虽然当然可以运行最终的“优化”过程来消除冗余，但将该任务委托给 SQL Server 本身可能没有什么坏处。
First of all, I doubt this is worth worrying about. I'm guessing if you compare the query execution plan for what EF generates to that of an 'optimal' hand-written query, the results will be effectively identical. I would guess the only likely penalty is that the EF generated query make take a fraction of a second longer to parse. In the grand scheme of things, this is probably not worth considering.
As to why EF generates the query this way in the first place, I'm pretty sure it has to do with the inherent complexity of translating LINQ methods into valid SQL queries. The engine that performs this translation is very modular, I'm sure, and each module must generate a part of the query that can easily be incorporated into the final, complete query. Although it would certainly be possible to run a final 'optimization' pass to eliminate redundancies, there's probably very little harm in delegating that task to SQL Server itself.