Click here to Skip to main content
15,314,449 members
Articles / Web Development / ASP.NET
Posted 14 Apr 2014


17 bookmarked

Dynamic paging in Entity Framework

Rate me:
Please Sign up or sign in to vote.
5.00/5 (6 votes)
14 Apr 2014CPOL1 min read
Appropriate query to fetch required no of results from DB instead of getting all at on time for binding controls like Gridview, listview etc


There are some applications where we need to display large sets of data. In order to display this large sets of data we usually follow the approach of Data paging.

In ASP.NET there are data bound controls like Gridview, listview etc that can facilitate paging.

The problem with this databound controls is that they perform paging on complete datasets.

The databound controls brings the complete datasets and then perform paging at the application serverside. This can led to network overload and server memory consuming every time user requests new data page.

Paging done at the database level is the best technique to optimize performance. Inorder to achieve this we have to build the appropriate query that will return only the required page result items.

Using the code

The following is an implementation of a simple extension method to offer paging functionality to simple Enitity framework or Linq to Sql query providers.

/// <summary>
/// Pages the specified query.
/// </summary>
/// <typeparam name="T">Generic Type Object</typeparam>
/// <typeparam name="TResult">The type of the result.</typeparam>
/// <param name="query">The Object query where paging needs to be applied.</param>
/// <param name="pageNum">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="orderByProperty">The order by property.</param>
/// <param name="isAscendingOrder">if set to <c>true</c> [is ascending order].</param>
/// <param name="rowsCount">The total rows count.</param>
/// <returns></returns>
private static IQueryable<T> PagedResult<T, TResult>(IQueryable<T> query, int pageNum, int pageSize,
                Expression<Func<T, TResult>> orderByProperty, bool isAscendingOrder, out int rowsCount)
    if (pageSize <= 0) pageSize = 20;
    //Total result count
    rowsCount = query.Count();
    //If page number should be > 0 else set to first page
    if (rowsCount <= pageSize || pageNum <= 0) pageNum = 1;
    //Calculate nunber of rows to skip on pagesize
    int excludedRows = (pageNum - 1) * pageSize;

    query = isAscendingOrder ? query.OrderBy(orderByProperty) : query.OrderByDescending(orderByProperty);
    //Skip the required rows for the current page and take the next records of pagesize count
    return query.Skip(excludedRows).Take(pageSize);

Example to use:

Lets assume a simple query that returns some articles from an Articles table.

We will call this method to get the first 20 items for the first page.

var articles = (from article in Articles
                where article.Author == "Abc"
                select article);

int totalArticles;

var firstPageData =     PagedResult(articles, 1, 20, article => article.PublishedDate, false, out totalArticles);

or simply for any entity

var context = new AtricleEntityModel(); 
var query = context.ArticlesPagedResult(articles, <pageNumber>, 20, article => article.PublishedDate, false, out totalArticles);

Accordingly this method can be invoked by passing the page number for other pages.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Maninder Singh Puhi
Software Developer
India India
No Biography provided

Comments and Discussions

QuestionExtension Method Pin
David Glass29-May-14 23:09
MemberDavid Glass29-May-14 23:09 
AnswerRe: Extension Method Pin
Maninder Singh Puhi5-Sep-14 1:28
MemberManinder Singh Puhi5-Sep-14 1:28 
GeneralRe: Extension Method Pin
Prusha Sidha16-Jun-16 6:55
MemberPrusha Sidha16-Jun-16 6:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.