Paging with Entity Framework Core

Paging query results is timeless topic and some days ago I got question about how to do it with Entity Framework Core. Using my previous work on paging I wrote some simple extension methods that work with IQueryable<T> interface. There’s also method that maps entities to models.

Paging classes

Let’s start with paging classes. I typically use two classes:

  • PagedResultBase – abstract base class for paged results, used in views and view components,
  • PagedResult<T> – strongly typed class for results and result set properties.

These two classes are here:


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>();
    }
}

These classes can be extended with other useful methods and properties if needed. It’s just a basic and minimal set needed to get paging done in code and in views.

Paging query results

This is the universal extension method for IQueryable<T> 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<T> to IEnumerable<T> because otherwise regular Count() method of LINQ is called instead of Entity Framework one.

This method can be called without specifying types. Example is here.


var paged = dataContext.Measurements.GetPaged(1, 5);

// Do something with results

This extension method works with evert IEnumerable that supports Count(), Skip(), Take() and ToList() methods. I just mention these methods to point out that there can be LINQ providers that doesn’t support some of these methods but this is rare case.

Paging and mapping query results

Web applications often use model classes for data presentation. These classes have no relations with databases and they carry mostly information needed to render a view. AutoMapper provides some extension methods for IQueryable that can be also used for paging. Here is the paging extension that creates paged result with model type.


public static PagedResult<U> GetPaged<T, U>(this IQueryable<T> query,
int page, int pageSize) where U: class
{
   
var result = new PagedResult<U
>();
    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)
                          .ProjectTo<
U
>()
                          .ToList();

   
return result;
}

As this method needs more than one generic parameter these parameters must be specified. This results in longer lines of code that are still well readable like shown below.


var paged = dataContext.GetPaged<Measurement,TempMeasurementModel>(1, 5);

// Do something with results

NB! This method expects that AutoMapper type mappings are already defined. Don’t forget to define mappings before calling this method!

References

Some older writings covering the same PagedResult class:


8 thoughts on “Paging with Entity Framework Core

Leave a Reply

Your email address will not be published. Required fields are marked *