LINQ to SQL : Paging Data

Rate This

Wriju

3 Oct 2007 11:08 PM

Comments 10

When we work with relational database and especially when with larger data we generally go for custom paging. This allows us to create superfast application.

The simple logic for paged query is like,

Let’s suppose you have 100 rows. Now you want to get data of page size 10 for page 2.

So the starting record would be

StartRecord = (PageNumber – 1) * PageSize

For page 2 it would be

StartRecord = (2 – 1) * 10 = 10 (may be you need to +1 to skip one record)

Now when this comes to LINQ to SQL scenario remains the same where the implementation is little different.

NorthWindDataContext db = new NorthWindDataContext();

var query = from c in db.Customers

select c.CompanyName;

//Assuming Page Number = 2, Page Size = 10

int iPageNum = 2;

int iPageSize = 10;

var PagedData = query.Skip((iPageNum - 1) * iPageSize).Take(iPageSize);

ObjectDumper.Write(PagedData);

Generated T-SQL,

For SQL Server 2000 the provider does not support Skip().

For SQL Server 2005

===================

SELECT [t1].[CompanyName]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CompanyName]) AS [ROW_NUMBER], [t0]
.[CompanyName]
    FROM [dbo].[Customers] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [10]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [10]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30428.1

Namoskar!!!