Partilhar via

LINQ to SQL : Paging Data

  • Artigo

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!!!

Comments

  • Anonymous
    October 03, 2007
    PingBack from http://www.artofbam.com/wordpress/?p=5042

  • Anonymous
    October 24, 2007
    The comment has been removed

  • Anonymous
    October 24, 2007
    @ RUIYes it doesWriju

  • Anonymous
    June 11, 2008
    At first glance this looks terrible .... Is this pulling all customers from the datastore or is there some clever extension method on the linq to sql parser that restricts by row_number? I'd like to see the sql that hits the database if possible.

  • Anonymous
    November 02, 2008
    Very good, that all was I need.

  • Anonymous
    February 13, 2009
    @RUSureYes, there is a clever extension method that restricts by row number.  The SQL from the article shows it.

  • Anonymous
    February 24, 2009
    This is great, thanks for you post as its very useful.

  • Anonymous
    April 30, 2009
    Just echoing what @hobbit125 said in response to @RUSure.This is awesome - thank you!

  • Anonymous
    May 28, 2009
    Hi Everybody,it selects from the table. But i need while searching.i need the total records as well

  • Anonymous
    November 10, 2009
    Thanks, that saved me a lot of work!

  • Anonymous
    May 18, 2014
    hello,I from Iran,Thank you for above code

  • Anonymous
    April 26, 2015
    In SQL Server 2012+ you have the Offset Fetch Next statement. It would be interesting to see if LINQ to SQL in VS 2012+ uses this new construct behind the scenes.