Databases
StackOverflow2013
Postsdbo
POWhy is the Entity framework generating this SQL?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy is the Entity framework generating this SQL?
    primarykey
    data
    text
    <p>I have this LINQ statement,</p> <pre><code>var carriageways = from carriageway in dataModelCurrentEntities.Carriageway where carriageway.RoadId == roadId &amp;&amp; carriageway.DistanceBreak == false orderby carriageway.CarriagewayStartInMetre select new CarriagewaySummary { StartMetres = carriageway.CarriagewayStartInMetre, EndMetres = carriageway.CarriagewayEndInMetre }; </code></pre> <p>It generates SQL in this form (LINQ to entities),</p> <pre><code>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 </code></pre> <p>What is the reasoning for this? I would have thought something like this was sufficient,</p> <pre><code>SELECT fields FROM table as Project1 WHERE blah ORDER BY blah ASC </code></pre> <p>I recall that LINQ to SQL would tend to generate the simpler SQL.</p> <p>I have looked at more complicated examples with joins etc, and LINQ to entities seems to generate the more complicated SQL.</p> <p><strong>UPDATE:</strong></p> <p>It's quite interesting because I was trying to test out what you are saying and I came across this LINQ,</p> <pre><code>var attachments = (from a in entities.Attachments where a.AttachmentID == 749 select new {a.AddedOn, a.AddedBy}); </code></pre> <p>And that generates this SQL,</p> <pre><code>SELECT [Extent1].[AttachmentID] AS [AttachmentID], [Extent1].[AddedOn] AS [AddedOn], [Extent1].[AddedBy] AS [AddedBy] FROM [dbo].[Attachment] AS [Extent1] WHERE 749 = [Extent1].[AttachmentID] </code></pre> <p>This one doesn't have a sub-query.</p> <p>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.</p> <p>It might not be as simple as that because the queries are different.</p> <p>I did take the second query and break it into a sub-query like this (manually),</p> <pre><code>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 </code></pre> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USpeter
    1. USpeter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhy is the Entity framework generating this SQL?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POWhy is the Entity framework generating this SQL?
      1. USfearofawhackplanet
      1. VTFavorite
    3. VO
      singulars
      1. POWhy is the Entity framework generating this SQL?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload