Paging increases performance by reducing the amount of records returned by SQL queries. Combine paging with Entity Framework projection queries, and you’ll notice a major increase in performance. In this post, you will learn how to implement paging with Entity Framework.
Let’s assume you have a Post
model that contains the fields: Id
, Title
and Body
, and you want to display a list of posts to edit.
To get a list of posts without paging, the code might look like:
var posts = context.Posts.Select(p => new
{
Id = p.Id,
Title = p.Title
})
.ToList(); //DONT DO THIS
This code is not recommended, your application will crawl, because it retrieves all posts stored in the database. To fix this problem, we will create an extension method that returns a paged list of posts.
Here is what the code looks like for returning a paged list of posts:
var posts = context.Posts.Select(p => new
{
Id = p.Id,
Title = p.Title
})
.OrderBy(o => o.Id)
.ToPagedList(1, 10);
The ToList
has been replaced with a new extension method called ToPagedList
. This method has 2 arguments that accept a page number and the number of items to return. In the example above, we are returning 10 posts from the first page.
Also, notice OrderBy
has been called before calling the ToPagedList
, this is required because the implementation of ToPagedList
uses the Skip
function which requires the list to be ordered.
How to create the PagedList and ToPagedList extension method?
Create a new interface called IPagedList
:
public interface IPagedList
{
int TotalCount { get; }
int PageCount { get; }
int Page { get; }
int PageSize { get; }
}
TotalCount
will be used to store the total amount of posts for the query. PageCount
contains the amount of pages that exist for the given Page
and PageSize
combination. Page
and PageSize
contain what is passed into the ToPagedList
method.
Now that we have our interface defined, create a new class called PagedList
:
public class PagedList<T> : List<T>, IPagedList
{
public int TotalCount { get; private set; }
public int PageCount { get; private set; }
public int Page { get; private set; }
public int PageSize { get; private set; }
public PagedList(IQueryable<T> source, int page, int pageSize)
{
TotalCount = source.Count();
PageCount = GetPageCount(pageSize, TotalCount);
Page = page < 1 ? 0 : page - 1;
PageSize = pageSize;
AddRange(source.Skip(Page * PageSize).Take(PageSize).ToList());
}
private int GetPageCount(int pageSize, int totalCount)
{
if (pageSize == 0)
return 0;
var remainder = totalCount % pageSize;
return (totalCount / pageSize) + (remainder == 0 ? 0 : 1);
}
}
The extension method, which we will create next, will create and return this PagedList
.
Now create a new class called PagedListExtensions
with the following code:
public static class PagedListExtensions
{
public static PagedList<T> ToPagedList<T>(this IQueryable<T> source, int page, int pageSize)
{
return new PagedList<T>(source, page, pageSize);
}
}
You should now be able to use ToPagedList
instead of ToList
. You will need to add the namespace of the PagedListExtensions
to the class where you are calling ToPagedList
. Alternatively, you could put the PagedListExtensions
class in the System.Linq
or an empty namespace.
And that’s how you boost performance with paging in Entity Framework.
Further Reading
More detailed discussion on Entity Framework can be found in the following publications:
- Lerman, J. 2010. Programming Entity Framework. 2nd ed. O’Reilly Media.
- Lerman, J. and Miller, R. 2011. Programming Entity Framework: Code First. O’Reilly Media.
- Lerman, J. and Miller, R. 2012. Programming Entity Framework: DbContext. O’Reilly Media.