Why is the Entity framework generating this SQL?

Question

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
                   };

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

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

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

UPDATE:

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});

And that generates this SQL,

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

This one doesn't have a sub-query.

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 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.

Answer 1

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.

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.

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.