SQL to LINQ ( Case 8 - Filter data by RowNumbers )

Case 8 : Filter data by RowNumbers

Here in this case I am going to show how you can filter your data by RowNumbers that you assigned to your recored(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.
more detail Example : LINQ TO SQL GridView (Enhanced Gridview)

Part - 1 :SQL to LINQ ( Visual Representation )
              SQL to LINQ ( Case 7 - Filter data by using IN and NOT IN clause)