Filter Records by RowNumbers in LINQ To SQL

By | March 10, 2011

In this tips we are going show how you can filter your data by RowNumbers that you assigned to your record(s). So to filter data in SQL (SQL server-2005) we use RowNumber function and than we use <=, >= or BETWEEN to filer data.

SQL query
 SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [id]) AS [ROW_NUMBER],
   [id], [FirstName], [LastName], [Email], [DisplayName], [Address1], [Address2], [Password], [Role]
    FROM [User] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN 11 AND 20
ORDER BY [t1].[ROW_NUMBER] 

In above query as you can see ROW_NUMBER() function assign number to records and than we use that number in outer query to filter data between 11 to 20.

LINQ query

But in LINQ it make use of two functions
•Skip: Bypasses a specified number of elements in a sequence and then returns the remaining elements. (See this link.)
•Take: Returns a specified number of contiguous elements from the start of a sequence. (See this link.)

So LINQ query is something as below.

var users = from u in Users
select u;
var filterUsers= users.OrderBy (p => p.Id).Skip (10).Take(10);

In above code we are selecting data first and than we are applying Skip and Take to get data between 11 to 20 records.
Graphical Representation

Category: Tips Tags: , , ,

About The Author

About Pranay Rana

Pranay is MVB on DZone and working as a Senior Software engineer. Doing Web development using Asp.Net ,C#, MS sql server, JQuery/Javascript that he had experience of 4.3 years now. For himdef. of programming is : Programming is something that you do once and that get used by multiple for many years You can visit him on his blog - http://pranayamr.blogspot.com/ Follow on twitter : @pranayamr