Why is Linq2SQL generating a nested query instead of using a JOIN?

Question

I'm trying to understand why Linq is generating the SQL that it is 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].[ParentId],
       [t2].[TemplateId],
       [t2].[ElementId],
       [t2].[Left]  AS [Left],
       [t2].[Right] AS [Right],
       [t2].[Id2],
       [t2].[Content]
FROM   (SELECT ROW_NUMBER() OVER (ORDER BY [t0].[Left]) AS [ROW_NUMBER],
               [t0].[Id],
               [t0].[ParentId],
               [t0].[TemplateId],
               [t0].[ElementId],
               [t0].[Left],
               [t0].[Right],
               [t1].[Id]                                AS [Id2],
               [t1].[Content]
        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 */
ORDER  BY [t2].[ROW_NUMBER]

There is a Foreign Key from TemplateNode.ElementId to TemplateElement.Id.

I would have expected the query to produce a JOIN, like so:

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 3 times faster than the nested query.

I'm using a .NET 4.0 Windows Forms app to test with SQL Server 2008 SP2 64bit developer edition.

Answer 1

The only reason that LINQ-SQL would generate the ROW_NUMBER query is due to the Skip Method. As bizare as the above SQL seems, I think within T-SQL there is no construct for simple paging like MySQL's Limit 10,25, so you get the above SQL when using Skip and Take.

I would assume that there is a Skip being used for paging purposes and LINQ-SQL is modifying the query. If you use an application like LINQ-Pad you can run different LINQ queries to see their generated SQL.