Server Side Pagination in T-SQL / LINQ

  • Article

OK it came as a shock to me that so many people were building huge sets of query data that they are binding in their UI and then deciding to page it at like 10 rows per page. I'm not talking about 1 or 2 or even 3 pages of data, I'm talking about hundreds here. If anyone thinks that schleping this much data across the wire is a good thing then please put down the keyboard. This is especially true when you can simply get the singe page of data in T-SQL with the following code:

 With Contacts As
(
SELECT c.FirstName, c.LastName
,ROW_NUMBER() OVER(ORDER BY Firstname) AS 'RowNumber'
FROM Person.Contact c
)
SELECT * FROM Contacts
WHERE RowNumber BETWEEN 10 AND 20

With LINQ it is even easier! Just supply a command with a Query property returning IQuerable<T> for your type:

 IQueryable<YourEntity> query = command.SelectQuery;
query.Skip(pageNumber * pageSize).Take(pageSize);

var results = command.Execute();

See? There you go and for pete's sake (whoever he is) stop being such a noodge!

Jimmy Zimms should probably not use yiddish in his both posts