How to Increase the Performance of Entity Framework with Paging • Developing Software

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.

How to create the PagedList and ToPagedList extension method?

Further Reading