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].[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 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
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.
source to share