How to implement paging with LINQ

Before going into how paging is implemented with LINQ, Let's discuss the need for implementing paging.

With large amounts of data, it is not a good practice to pull all records from database when you are showing a fraction of them in one page. It is always recommended to use data on demand approach. When you want to show first 20 records out of the search results then you must get the first 20 records from database and discard the rest. Similarly when you want to show next 20 records of the search results then you need to get the next 20 records from database and discard the rest. This is nothing but called paging.

LINQ has made the paging solution very simple as shown below example.

public List<Client> 
GetAllClients(bool? isActive, int pageNumber, int pageSize, out int totalPages)
{
//Actual query which returns large data
var query = dataContext.Clients.Where(p => isActive == null || p.IsActive == isActive);

//Calculating total number of pages by taking ceiling number of the fractional value
totalPages = (int)Math.Ceiling((decimal)query.Count() / (decimal)pageSize);

//Paging logic goes here
return query.Skip((pageNumber - 1)*pageSize).Take(pageSize).ToList();
}

The parameters which play major role in paging are page number and page size. The page number is to identify the page of which the records to be returned. And the page size to identify the number of records to be returned. And there is another out parameter totalPages which is used to hold the total number of pages available within the data returned. This is needed to show the number of pages to the user and also useful in the logic which enables/disables page navigation.

Implementing Custom Paging, Sorting and Dynamic query in sql

This post explains and demonstrates how to implement following things

1. Custom Paging: Returning a set of records for the given page number and page size
   
2. Dynamic query: Querying based on the where string passed thru parameter
   
3. Dynamic sorting: Sorting based on the sort expression passed thru parameter
   
4. SP_ExecuteSql: Executing sql statements included within parameters
   
5. Common Table Expression: Similar to table variable
6. function row_number(): returns the current row number in a result set

The following code is to create the stored procedure GetCustomersByPage. This stored procedure accepts the parameters page size, page number to return the page, where string (It must start with 'and' keyword if a condition is added. Ex: " and CompanyName like '%soft%' " or empty is accepted but applies no filter), sort expression to get the sorted records and total count which is useful when implementing the paging logic in user interface.

The sample sproc is written for NorthWind database. If you have NorthWind database installed then you can run this sproc and see how it works.

create procedure GetCustomersByPage
@PageSize int,
@PageNumber int,
@WhereString varchar(max),
@SortExpression varchar(200),
@TotalCount int output
as
begin

declare @FirstRecord int
declare @LastRecord int


set @FirstRecord = (((@PageNumber - 1) * @PageSize) + 1)
set @LastRecord = (@PageNumber * @PageSize)

declare @sql nvarchar(max)
DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition=N'@TotalCount int OUTPUT'

set @sql = 'select @TotalCount = count(*) from dbo.Customers where 1=1 '+@WhereString+';
with PagedCustomers as (select row_number() over(order by '+@SortExpression+') as RowNumber, * from dbo.Customers where 1=1 '+@WhereString+')
SELECT * from PagedCustomers where RowNumber between '+cast(@FirstRecord as varchar)+'and '+cast(@LastRecord as varchar)

exec sp_executesql @sql, @ParmDefinition, @TotalCount=@TotalCount output

return @TotalCount
end