为什么实体框架会生成此SQL? [英] Why is the Entity framework generating this SQL?

问题描述

我有这个LINQ语句，

I have this LINQ statement,

var carriageways = from carriageway in dataModelCurrentEntities.Carriageway
                   where carriageway.RoadId == roadId && carriageway.DistanceBreak == false
                   orderby carriageway.CarriagewayStartInMetre
                   select new CarriagewaySummary
                   {
                       StartMetres = carriageway.CarriagewayStartInMetre, 
                       EndMetres = carriageway.CarriagewayEndInMetre
                   };

它以这种形式(实体的LINQ)生成SQL，

It generates SQL in this form (LINQ to entities),

SELECT 
Project1.field1 AS field1
Project1.field2 AS field2
FROM ( SELECT 
    Extent1.field1 AS field1, 
    Extent1.field2 AS field2
    FROM table AS Extent1
    WHERE blah
)  AS Project1
ORDER BY blah ASC

这是什么原因?我本以为这样的东西就足够了，

What is the reasoning for this? I would have thought something like this was sufficient,

SELECT 
fields
FROM table as Project1
WHERE blah
ORDER BY blah ASC

我记得LINQ to SQL倾向于生成更简单的SQL.

I recall that LINQ to SQL would tend to generate the simpler SQL.

我看过带有连接等的更复杂的示例，而对实体的LINQ似乎会生成更复杂的SQL.

I have looked at more complicated examples with joins etc, and LINQ to entities seems to generate the more complicated SQL.

更新:

这很有趣，因为我试图测试您在说什么，而我遇到了这个LINQ，

It's quite interesting because I was trying to test out what you are saying and I came across this LINQ,

var attachments = (from a in entities.Attachments
                  where a.AttachmentID == 749
                  select new {a.AddedOn, a.AddedBy});

然后生成此SQL，

SELECT 
[Extent1].[AttachmentID] AS [AttachmentID], 
[Extent1].[AddedOn] AS [AddedOn], 
[Extent1].[AddedBy] AS [AddedBy]
FROM [dbo].[Attachment] AS [Extent1]
WHERE 749 = [Extent1].[AttachmentID]

这个没有子查询.

区别是(至少是其中之一)...等待它. Informix.上面生成子查询的第一个查询是使用notifyix.第二个查询不是SQL Server.

The difference is (well one of them at least) ... wait for it. Informix. The first query above which generates the sub-query is using informix. The second query which doesn't is SQL server.

它可能不是那么简单，因为查询是不同的.

It might not be as simple as that because the queries are different.

我确实接受了第二个查询，并将其分成这样的子查询(手动)，

I did take the second query and break it into a sub-query like this (manually),

SELECT 
[Project1].[AttachmentID] AS [AttachmentID], 
[Project1].[AddedOn] AS [AddedOn], 
[Project1].[AddedBy] AS [AddedBy]

    FROM ( SELECT

    [Extent1].[AttachmentID] AS [AttachmentID], 
    [Extent1].[AddedOn] AS [AddedOn], 
    [Extent1].[AddedBy] AS [AddedBy]
    FROM [dbo].[Attachment] AS [Extent1]
    WHERE 749 = [Extent1].[AttachmentID]
    ) AS Project1

SQL Server为两者显示了相同的执行计划，因此正如您所说的，SQL Server能够很好地对其进行优化.另一方面，Informix在优化方面是可耻的.

SQL server shows the same execution plan for both, so as you say SQL server is able to optimise it quite nicely. Informix on the other hand is shady at optimising things.

推荐答案

是否通过子查询生成SQL可能取决于您使用的实体框架提供程序.但是，由于大多数现有程序可能具有相同的血统书(因为它们可能是从Microsoft代码示例开始的)，因此它们都可能导致相似的SQL.提供程序将获得一个查询树，该查询树由Linq语句生成，并负责生成SQL.这样做的过程是访问查询树中的节点并随其生成SQL.

Whether or not it produces the SQL with the subquery probably depends on the entity framework provider that you are using. But since most of the existing ones probably share the same pedigree (in that they likely started from a Microsoft code sample), they probably all result in similar SQL. The provider is given a query tree that is produced from the Linq statement and is responsible for producing the SQL. The process for doing this is to visit the nodes in the query tree and generate SQL as it goes.

在OP中的给定投影中，有意义的是生成了子查询.它要求从先前的查询"中获取一组值(新... {StartMetres，EndMetres}).因此，查询生成将产生"SELECT <requested values> FROM something"，其中"something"本身被呈现为查询.因此，对查询树的简单访问将导致一个子查询.

In the given projection in the OP, it makes sense that the subquery is generated. It is asking for a set of values (new ... {StartMetres, EndMetres}) which are taken from the preceding "query". The query generation would thus produce "SELECT <requested values> FROM something" where the "something" is, itself, rendered as a query. The simple visiting of the query tree, thus, results in a subquery.

该过程完成后，提供者当然可以优化"生成的SQL并删除子查询.但是，这是SQL查询引擎真正擅长的事情，因此将任务委派给查询引擎是很有意义的.这可能取决于您所使用的数据库，但是具有子查询的SQL语句的查询计划可能与没有子查询的优化"查询计划相同.

Once that process is finished, it would certainly be possible for the provider to "optimize" the resulting SQL and remove the subquery. However, that is the kind of thing that SQL query engines are really good at, so it makes sense to delegate that task to the query engine. It probably depends on the database you are using, but it is likely that the query plan for the SQL statement with the subquery will be identical to the "optimized" one without the subquery.

这篇关于为什么实体框架会生成此SQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！