• Home
  • Tutorials
  • Development Tools
  • Contact Us

Developing Software

Mastering Software Craftsmanship

How to Increase the Performance of Entity Framework with Paging

25th August 2014 by @developingsoft

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.
Share this on:

Filed Under: Tutorials Tagged With: C#, Entity Framework

Search

Advertisement

Newsletter

Subscribe now to receive practical tips on how to become a better software developer.

Free - No Spam - 100% Email Privacy

Featured Posts

Abstract Factory Pattern: C# Example Using the Unity Game Engine

23 Software Design Patterns That Will Make You a More Effective Programmer

How to Deploy an ASP.NET Core Website to Ubuntu with Git

How to Run an ASP.NET Core Website in Production on Ubuntu Linux

How to Install the Edimax Wireless nano USB Adapter on Windows IoT Core for Raspberry Pi

How to Convert a Post Title into a Friendly URL (Slug) in C#

How to Convert Markdown to HTML in ASP.NET Core

How to Send an E-Mail with ASP.NET Core and Mailgun

How to Generate a Sitemap in ASP.NET MVC and ASP.NET Core

How to Create an MD5 Hash of a String in C# and Displaying a Gravatar Image

© 2014–2023 Developing SoftwareTerms • Privacy