Why does Linq2SQL generate a subquery instead of using a JOIN?

I'm trying to understand why Linq generates SQL, what is it for the statement below:

var dlo = new DataLoadOptions();
dlo.LoadWith<TemplateNode>(x => x.TemplateElement);
db.LoadOptions = dlo;

var data = from node in db.TemplateNodes
           where node.TemplateId == someValue
           orderby node.Left
           select node;


Which generates the following SQL:

SELECT [t2].[Id],
       [t2].[Left]  AS [Left],
       [t2].[Right] AS [Right],
               [t1].[Id]                                AS [Id2],
        FROM   [dbo].[TemplateNode] AS [t0]
               INNER JOIN [dbo].[TemplateElement] AS [t1]
                 ON [t1].[Id] = [t0].[ElementId]
        WHERE  [t0].[TemplateId] = 16 /* @p0 */) AS [t2]
WHERE  [t2].[ROW_NUMBER] > 1 /* @p1 */


There is a foreign key from TemplateNode.ElementId

to TemplateElement.Id


I would expect the request to create JOIN

, for example:

SELECT * FROM TemplateNode
INNER JOIN TemplateElement ON TemplateNode.ElementId = TemplateElement.Id
WHERE TemplateNode.TemplateId = @TemplateId


As per the suggestions in the answers to this question, I have profiled both queries and the JOIN is 3x faster than the nested query.

I am using a .NET 4.0 Windows Forms Application for testing with the 64-bit release of SQL Server 2008 SP2.


source to share

2 answers

The only reason LINQ-SQL generates a query ROW_NUMBER

is due to the method Skip

. Because as you can see from the above SQL, I think there is no construct in T-SQL for simple paging like MySQL Limit 10,25

, so you get the above SQL when using Skip

and Take


I would guess that for paging purposes it is used Skip

and LINQ-SQL modifies the query. If you are using an application like LINQ-Pad, you can run various LINQ queries to see their generated SQL.



Your join example is not equivalent. You cannot get ROW_NUMBER and then only select WHERE ROW_NUMBER> 1 rows with simple join. You will need to do a sub-selection or the like to get this result.



All Articles