Filter Records by RowNumbers in LINQ To SQL

Introduction

Here in this case I am going to 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.

Graphic representation

Summary

Best example of this is when you are using custom paging in you gridcontrol or list control.