Paging with Entity Framework Core

Paging in Entity Framework Core

Paging query results is timeless topic. Here is the clean and simple example about data paging in Entity Framework Core. This is the paging mechanism I'm using in real applications.

Paged results classes

I'm using two classes for paged results:

• PagedResult - generic class for paged results
• PagedResultBase - base class used for paged data where type of data doesn't matter

public abstract class PagedResultBase
{
    public int CurrentPage { get; set; } 
    public int PageCount { get; set; } 
    public int PageSize { get; set; } 
    public int RowCount { get; set; }
 
    public int FirstRowOnPage
    {
 
        get { return (CurrentPage - 1) * PageSize + 1; }
    }
 
    public int LastRowOnPage
    {
        get { return Math.Min(CurrentPage * PageSize, RowCount); }
    }
}
 
public class PagedResult<T> : PagedResultBase where T : class
{
    public IList<T> Results { get; set; }
 
    public PagedResult()
    {
        Results = new List<T>();
    }
}

Although the sample code here uses in-memory database context the same code works also well with SQL Server, PostgreSQL and MySQL. I'm sure it works okay also with other databases that have Entity Framework 2.0 provider available.

Paging query results

This is the universal extension method for IQueryable that returns one page of results and some numbers that describe the result set.

public static PagedResult<T> GetPaged<T>(this IQueryable<T> query, 
                                         int page, int pageSize) where T : class
{
     var result = new PagedResult<T>();
     result.CurrentPage = page;
     result.PageSize = pageSize;
     result.RowCount = query.Count();


     var pageCount = (double)result.RowCount / pageSize;
     result.PageCount = (int)Math.Ceiling(pageCount);
 
     var skip = (page - 1) * pageSize;     
     result.Results = query.Skip(skip).Take(pageSize).ToList();
 
     return result;
}

NB! Don’t change IQueryable to IEnumerable because otherwise regular Count() method of LINQ is called instead of Entity Framework one.

Example database context

Here are the example database context used in this example and definition of Contact entity.

public class SampleDbContext : DbContext
{
    public DbSet<Contact> Contacts { get; set; }

    public SampleDbContext(DbContextOptions<SampleDbContext> options) : base(options)
    {
    }

    public void GenerateSampleData()
    {
        Contacts.Add(new Contact { Id = 1, FirstName = "John", LastName = "Doe", Email = "john@example.com" });
        Contacts.Add(new Contact { Id = 2, FirstName = "Peter", LastName = "Jones", Email = "peter@example.com" });
        Contacts.Add(new Contact { Id = 3, FirstName = "Mary", LastName = "Smith", Email = "mary@example.com" });
        Contacts.Add(new Contact { Id = 4, FirstName = "Ian", LastName = "Green", Email = "ian@example.com" });
        Contacts.Add(new Contact { Id = 5, FirstName = "Nancy", LastName = "Brownwood", Email = "nancy@example.com" });
        Contacts.Add(new Contact { Id = 6, FirstName = "Tommy", LastName = "High", Email = "tommy@example.com" });
        Contacts.Add(new Contact { Id = 7, FirstName = "Gabriel", LastName = "Santos", Email = "gabriel@example.com" });
        Contacts.Add(new Contact { Id = 8, FirstName = "Ryan", LastName = "James", Email = "ryan@example.com" });
        Contacts.Add(new Contact { Id = 9, FirstName = "Marc", LastName = "James", Email = "marc@example.com" });
        Contacts.Add(new Contact { Id = 10, FirstName = "James", LastName = "Dallas", Email = "james@example.com" });
        Contacts.Add(new Contact { Id = 11, FirstName = "Ron", LastName = "Steer", Email = "ron@example.com" });
        Contacts.Add(new Contact { Id = 12, FirstName = "Liam", LastName = "Schwarz", Email = "liam@example.com" });

        SaveChanges();
    }
}

public class Contact
{
    [Key]
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
}

Displaying paged results

This is how paging is done in view. For real applications I have pager view component that does all the nice paging.

<table class="table table-bordered table-hover">
    <thead>
        <tr>
            <th>#</th>
            <th>First name</th>
            <th>Last name</th>
            <th>E-mail</th>
        </tr>
    </thead>
    <tbody>
        @{ var i = 1; }
        @foreach (var contact in Model.Results)
        {
            var rowNo = (Model.CurrentPage - 1) * Model.PageSize + i;
            i++;
            <tr>
                <td>@rowNo</td>
                <td>@contact.FirstName</td>
                <td>@contact.LastName</td>
                <td>@contact.Email</td>
            </tr>
        }
    </tbody>
</table>

@for (var p = 1; p <= Model.PageCount; p++)
{
    <a href="@Url.Action("Index", new { page = p })" class="btn btn-default">@p</a>
}

Demo

The last thing to stitch things together is adding controller action that uses paged results.

References

• Paging with Entity Framework Core (Gunnar Peipman)
• Returning paged results from repositories using PagedResult (Gunnar Peipman)