LINQ-To-SQL Uses magic ROW_NUMBER() function

Blog comments edit

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 </span>( )     OVER ( [ ] <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.

``` SQL 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 @P1th to @P2th rows which list is ordered by ProductId.

``` SQL 
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.
    

``` CSharp 
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:
    

``` SQL 

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.

Comments