Entity Framework and LINQ to SQL Paging

In some applications when we need to display large sets of data , Data Paging is our choice in order to improve the performance. For ASP.NET developers we can use Data Paging mechanism provided by the most of ASP.NET Data-bound Controls such as GridView.

The default behavior of ASP.NET Data Controls is to bring the complete data set and do the paging at the application server side. This will led to network overload and server memory consuming every time user requests new data page.

Paging at the database level is mostly the best way to optimize the performance. The idea behind database level paging is to build the appropriate SQL query which brings the needed data page rather than the complete data set.

Entity Framework and LINQ to SQL provide developers with the ability to extend their SQL generator functionality to build paging enabled queries through extending IQueryable<T> interface implementation.

Here is the IQueryable<T> function as described in MSDN:

The IQueryable<T> interface enables queries to be polymorphic. That is, because a query against an IQueryable data source is represented as an expression tree, it can be executed against different types of data sources.

In this article, I am going to create a new Extended Method to offer Paging functionality to Entity Framework and LINQ to SQL query providers.

After this is completed we should be able to use Page method as the below figure shows

Page Method Signature

public static IQueryable<T> Page<T, TResult> (this IQueryable<T> query,
                                              int pageNum, int pageSize,
                                              Expression<Func<T, TResult>> orderByProperty,
                                              bool isAscendingOrder,
                                              out int rowsCount
                                             )

Page method takes 2 generic types T and TResult. T is the type of the data in the data source to be paged and TResult is the return value of the method encapsulated by Func<T, TResult> Delegate.

query parameter is the Object Query where paging should be applied, pageNum parameter is the data page to retrieve, pageSize is the number of rows to be retrieved per data page, orderByProperty is the T type property to sorted by and it is defined as a Lambda Expression to avoid passing orderByProperty parameter as a string, isAscendingOrder identifies if the sorting is ASC or DESC and  rowsCount is the count of rows in the complete data set. this will help on creating UI Data Pager in the Presentation Layer

rowsCount parameter is defined as out which requires rowsCount to be assigned to a value before the Page method returns.

Page Method Implementation

First off, I am validating the parameters as the following:

pageSize should be greater than zero, otherwise an Argument Out Of Range Exception thrown or a default value provided. The default value might be the rowsCount or a data page size predefined. In this example I’ll use 20 as the default page size.

pageNum should be greater than zero, otherwise pageNum is defined as 1. In case of pageSize is greater than or equals to rowsCount, 1 will be used as the pageNum.

I will use the query providers Take, Skip and OrderBy methods to get the correct data page according to the current page number and page size.

Let’s show how Skip and Take methods differ when mapped to SQL code. I am using LINQPad to get the equivalent SQL code.

If we write the below code

 Customers.Take(5)

Equivalent SQL code looks like

 SELECT TOP (5) [t0].[ID], [t0].[Name]
FROM [Customers] AS [t0]

And for

 Customers.Skip(5)

The result is

-- Region Parameters
DECLARE @p0 Int SET @p0 = 5
-- EndRegion
SELECT [t1].[ID], [t1].[Name]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ID], [t0].[Name]) AS [ROW_NUMBER], [t0].[ID], [t0].[Name]
    FROM [Customers] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] > @p0
ORDER BY [t1].[ROW_NUMBER]

Page Method Complete Code

public static IQueryable<T> Page<T, TResult>(this IQueryable<T> query,
                        int pageNum, int pageSize,
                        Expression<Func<T, TResult>> orderByProperty,
                        bool isAscendingOrder, out int rowsCount)
{
    if (pageSize <= 0) pageSize = 20;

    rowsCount = query.Count();

    if (rowsCount <= pageSize || pageNum <= 0) pageNum = 1;

    int excludedRows = (pageNum - 1) * pageSize;

    if (isAscendingOrder)
        query = query.OrderBy(orderByProperty);
    else
        query = query.OrderByDescending(orderByProperty);

    return query.Skip(excludedRows).Take(pageSize);
}

excludedRows is the set of rows should be skipped using Skip method and computed as pageSize multiplied by (pageNum – 1)

I’d like to know your suggestions…

Advertisement