LINQ-To-SQL Uses magic ROW_NUMBER() function

Recently I founded that LINQ-To-SQL Uses magic ROW_NUMBER() function. ROW_NUMBER() function is a magic function which was added in SQL Server 2005. Microsoft put this function in version 2005 so that developers will not take it for granted and appreciate it. ROW_NUMBER "returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition".

ROW_NUMBER() Syntax:

ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )

For example following query add a number field which is partitioned by ProductID (reset on new ProductID) in descending order on UnitPrice * OrderQty.

        SELECT ROW_NUMBER() 
        OVER (Partition by ProductID ORDER BY UnitPrice * OrderQty DESC) AS ROWNUM,* 
        FROM Sales.SalesOrderDetail 
    

ROW_NUMBER() helps programmer to select specified amount of rows within a select command. This feature commonly used in paging. Following example returns @P1^th to @P2^th rows which list is ordered by ProductId.

SELECT * FROM
SELECT *, ROW_NUMBER() OVER (ORDER BY ProductID) AS ROWNUMBER 
FROM Sales.SalesOrderDetail) AS ALLDATA
WHERE ALLDATA.ROWNUMBER  BETWEEN @P1 and @P2

Skip() and Take()LINQ-To-SQL functions generate ROW_NUMBER syntax in query result. I created a LINQ-TO-SQL dbml file on AdventureWorks. I selected SalesOrderDetail as Data Class.

            AdventureWorksDataContext advDC = new AdventureWorksDataContext();
 
            IQueryable orderDetails = advDC.SalesOrderDetails.OrderBy(f => f.SalesOrderDetailID)
                                                             .Skip(20).Take(10);
 
            foreach (SalesOrderDetail orderDetail in orderDetails)
                Console.WriteLine(orderDetail.ProductID);
            Console.ReadLine();

Above code generates following SQL for orderDetails:

 {SELECT [t1].[SalesOrderID], [t1].[SalesOrderDetailID], [t1].[CarrierTrackingNumber], [t1].[OrderQty], [t1].[ProductID], [t1].[SpecialOfferID], [t1].[UnitPrice], [t1].[UnitPriceDiscount], [t1].[LineTotal], [t1].[rowguid], [t1].[ModifiedDate]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[SalesOrderDetailID]) AS [ROW_NUMBER], [t0].[SalesOrderID], [t0].[SalesOrderDetailID], [t0].[CarrierTrackingNumber], [t0].[OrderQty], [t0].[ProductID], [t0].[SpecialOfferID], [t0].[UnitPrice], [t0].[UnitPriceDiscount], [t0].[LineTotal], [t0].[rowguid], [t0].[ModifiedDate]
    FROM [Sales].[SalesOrderDetail] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]
}

As you see, Skip and Take functions interpreted as ROW_NUMBER() function.