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:

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    23 thoughts on “Paging with Entity Framework Core

    • January 10, 2017 at 1:44 pm
      Permalink

      Great article

      Please note that if you use this method with Entity Framework you have to make sure to sort the query to avoid this error: The method ‘Skip’ is only supported for sorted input in LINQ to Entities. The method ‘OrderBy’ must be called before the method ‘Skip’.

    • January 10, 2017 at 2:01 pm
      Permalink

      My mistake!

      My previous comment was with about Entity Framework 6.

      I do not know if Entity Framework Core has the same behavior.

    • January 10, 2017 at 2:08 pm
      Permalink

      pacoweb, it’s different with EF Core. EF Core doesn’t need ordered queryable for paging.

    • January 10, 2017 at 2:17 pm
      Permalink

      Wil, there’s no versions of Skip() and Take() with lambda in Entity Framework Core.

    • January 10, 2017 at 2:26 pm
      Permalink

      Thanks

    • January 10, 2017 at 7:17 pm
      Permalink

      Doesn’t this code actually make 2 trips to the database? One for the .Count() call, and one for the .Skip(…).Take(…).ToList() call? If so, anyway to avoid that?

    • January 10, 2017 at 7:40 pm
      Permalink

      Yes, it takes two queries to database. Count() gives back the number of rows that match query and second query takes just requested page of results.

      If you don’t need Count() you can remove it from method and from PagedResult class ending up with paging where user can go to next page until empty page is returned.

    • January 22, 2017 at 8:23 pm
      Permalink

      I fail to see how the base class is useful for anything.

    • March 11, 2019 at 5:50 pm
      Permalink

      thank you

      how to pass EF Query using a Function inside the form :
      public object Get_result(“which best type?” query)
      {
      // Result
      int currentPage = Public_CurrentPage;
      int CountPerPage = Convert.ToInt32(PAG_CountPerPage.Text);
      var result = mainPAG.GetPaged(query, currentPage, CountPerPage);
      return result;
      }

    • March 12, 2019 at 8:03 am
      Permalink

      This paging solution expects IQeuryable that is translated to SELECT query. As long as it is possible to create SELECT with function everything should work. If you need paging inside function then this solution doesn’t work for you. Based on information you delivered it is not possible to know what function and how you are using.

    • March 12, 2019 at 11:37 am
      Permalink

      so, can you please show another solution that we don’t need to use extension method so, i can paging inside function

    • April 27, 2019 at 2:56 pm
      Permalink

      Hi Gunnar, thank you for the great article.
      I’m using .NET Core 2.2 and Automapper 8.
      I had to explicitly pass the Automapper map logic into the paging extension method

      public static PagedResult GetPaged(this IQueryable query, int page, int pageSize, IMapper _mapper) where U : class

      to use with

      .ProjectTo(_mapper.ConfigurationProvider)

      Then I call the method like so:

      // var viewModel = dataContext..GetPaged(1, 5, _mapper);

      I could of course be some sort of problem with my own project setup, but I thought I would bring it to your attention in any case.

      Keep up the good work.

    • May 7, 2019 at 7:03 am
      Permalink

      I just updated my paging samples to latest .NET/.NET Core: https://github.com/gpeipman/DotNetPaging

      To use ProjectTo() you don’t need instance of mapper necessarily. Here’s how you should initialize AutoMapper 8 in Startup class of web application:

      services.AddAutoMapper(GetType().Assembly);

      Mapper.Initialize(cfg =>
      {
      cfg.AddProfile();
      });

      This is what worked for me with ASP.NET Core 2.2

    • May 7, 2019 at 11:22 am
      Permalink

      Thanks for the information on the initialisation of Automapper in the Startup class. Thanks :)

    • May 12, 2019 at 10:39 pm
      Permalink

      Hi Gunnar,
      i’m sorry but i’m new

      if i have 25,000 record in the database and i defined to show 20 record per page , this code will load only the 20 record from the database or it will load 25,000 record then show 20 of them.

    • May 12, 2019 at 11:38 pm
      Permalink

      Hi,

      Code here is written specially to avoid situations where million rows are loaded to memory and 10 of these are displayed. With this solution paging is done in database. If your page size is 20 then maximum 20 rows is read from database.

    • May 29, 2019 at 10:18 pm
      Permalink

      Loved your code.
      Very very clean. :)

    • July 9, 2019 at 7:12 am
      Permalink

      Hi Gunnar, thank you for the nice article and code. Exactly what I needed for a .NET Core 2.2, project with Automapper 8.1.1 and database with 28 million records. Works like a charm!

    • August 15, 2019 at 4:14 pm
      Permalink

      with this way, do you meet problem about memory allocation for STATIC …

    • August 15, 2019 at 5:00 pm
      Permalink

      Please be more specific. I don’t understand your question.

    Leave a Reply

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