# Tuesday, September 18, 2007
LINQ Sql Using Skip And Take
Tuesday, September 18, 2007 1:22:32 PM (GMT Daylight Time, UTC+01:00) ( .Net General | Asp.Net | Database )
Here is the sql that is generated by a DataContext when you use Skip() and Take() to efficiently select records for the grid:
SELECT TOP 10 [t1].[Name], [t1].[Address], [t1].[Tel1], [t1].[Tel2], [t1].[Email], [t1].[DateCommenced], [t1].[Comments], [t1].[Active], [t1].[Fax]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[Name], [t0].[Address], [t0].[Tel1], [t0].[Tel2], [t0].[Email], [t0].[DateCommenced], [t0].[Comments], [t0].[Active], [t0].[Fax]) AS [ROW_NUMBER], [t0].[Name], [t0].[Address], [t0].[Tel1], [t0].[Tel2], [t0].[Email], [t0].[DateCommenced], [t0].[Comments], [t0].[Active], [t0].[Fax]
    FROM [dbo].[Table1] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] > @p0
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [50]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

it's not immediately obvious why they use the subquery like this, but i'm sure they have been very thorough in optimising LINQ. 

Comments [1] | | # 
Monday, September 24, 2007 11:59:32 PM (GMT Daylight Time, UTC+01:00)
I hope this makes it obvious. The SQL query needs the row number to implement the Skip operation. The row number is not in scope for the TOP and WHERE expressions unless it is computed in a nested query.
Matt Warren
Name
E-mail
Home page

Comment (HTML not allowed)  

Enter the code shown (prevents robots):
Live Comment Preview