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:
The code can be improved to parameterise the skip and take values.
See here https://www.simple-talk.com/dotnet/net-tools/entity-framework-performance-and-what-you-can-do-about-it/#crayon-5874c4815dda9144613486
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’.
My mistake!
My previous comment was with about Entity Framework 6.
I do not know if Entity Framework Core has the same behavior.
pacoweb, it’s different with EF Core. EF Core doesn’t need ordered queryable for paging.
Wil, there’s no versions of Skip() and Take() with lambda in Entity Framework Core.
Thanks
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?
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.
I fail to see how the base class is useful for anything.
Stilgar, example of base class here: http://gunnarpeipman.com/2016/07/building-pager-view-component/ In pager view component, by example, I don’t need to mess with generics when using this base class that carries minimal needed information to get pager rendered.
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;
}
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.
so, can you please show another solution that we don’t need to use extension method so, i can paging inside function
If you want to do paging inside database then here is one good source: https://www.sqlshack.com/introduction-pagination-sql-server/
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.
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
Thanks for the information on the initialisation of Automapper in the Startup class. Thanks :)
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.
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.
Loved your code.
Very very clean. :)
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!
with this way, do you meet problem about memory allocation for STATIC …
Please be more specific. I don’t understand your question.
public static PagedResult….
This is method static, it has safe when using multi-thread
Although static it is thread-safe. It is extension method meaning that it gets instance of query.
Here is my writing that illustrates that extension method is actually clever illusion: https://gunnarpeipman.com/csharp/extension-methods/
Hi,
Is there an example passing dynamic filters?
Where (x => x ….)
GetPaged() works with IQueryable and it’s therefore up to you how you build up query. Important things is to build the whole query in code before calling GetPaged().
Example:
var query = _dbContext.Invoices.Include(i => i.Customer) if(query.DueDate.HasValue) { query = query.Where(i => i.DueDate <= query.DueDate.Value); } if(query.CustomerId.HasValue) { query = query.Where(i => i.Customer.Id == query.CustomerId.Value); } var invoices = query.OrderBy(i => i.InvoiceNo).GetPaged(page, pageSize);In case of EF Core check always out the resulting SQL query as this can be ugly and not very performant.
Dear Gunnar,
When using paging with AutoMapper ProjectTo, assuming an example of Parents and Childs, wish to acquire parents list with childCount (automapper projects automatically to childCount property).
The issue happens that when paginating, several round trips to count child happens along with ProjectTo, if I remove paging, I have a single (wished) round trip to get parents and counting child.
Can you advise what to do in order to avoid those roundtrips (that is happening one count(*) for each parent record).
Thanks
Aside from being good, you need to also look sensible. By this, we mean that you want to choose a site that finest corresponds to your wants and requirements. To do that, register your profile, login and merely enter the info that the Pretty Ukrainian Lady of your dream needs to be endowed with. Let’s explore how John can navigate these challenges and make his dream of marrying Olga a actuality. They want someone who is aware of methods to make decisions and may take management of every little thing. Real Ukrainian girls have nice respect for men and will never offend their beloved, which is why they need to be treated the identical approach. There are tens of millions of the reason why it’s best to date these incredible locals. It is also one of the simplest ways to fulfill Ukrainian locals. TrulyRussian is one of the reliable dating sites accessible on-line where you’ll be able to meet international singles like Ukrainian, Russian, and Belarusan without spending a dime. Why must you date Ukrainian singles? They are very curious about the world that’s the reason they will ask you quite a lot of questions.
So cease wondering why they wear you down with numerous questions. When relationship a Ukrainian, you’ve received to be prepared to answer all their questions. Reputable platforms function legally available on the market and deliver relationship companies based on international phrases and rules. Limitation of Liability. Except in jurisdictions where such provisions are restricted, in no event will AnastasiaDate be liable to You or any third person for any indirect, consequential, exemplary, incidental, special or punitive damages, together with additionally misplaced profits arising out of your use of the Websites or the Service, in addition to damages for loss of date, loss of packages, value of procurement of substitute Services or Service interruptions, even when AnastasiaDate knows or has been advised of the opportunity of such damages. Even at a younger age, they’re taught by their mother and father how to take good care of their youthful siblings and do chores around the home.
1. “Artificial conduct.†At the stage of acquaintance and the next meetings, a very powerful is a natural and even considerably spontaneous habits. But most importantly that power flows, which naturally feed the developing feelings, are blocked by artificial conduct. Communicate overtly and honestly: Foster a powerful, healthy relationship by sharing your thoughts, feelings, and needs with each other respectfully. There are too much of how to get success in dating Ukrainian singles despite language limitations. Both produce potential matches among Ukrainian women fast, so daters can attempt each methods and decide essentially the most snug method to fulfill eligible singles. This site is a goldmine for relationship, particularly if you’re eyeing potential partners from Ukraine. If you’re enthusiastic about exploring this distinctive relationship tradition additional, there are many authentic Ukrainian dating sites you can explore! While you arrive in Ukraine or join a Ukrainian dating site, you will be astounded as to how enticing Ukrainians may very well be. Ukrainian ladies have numerous benefits and make best life companions and wives.
She’s desirous to make plans for future dates or activities and seems genuinely enthusiastic about spending time with you. Spending just a few dollars on a premium membership charmdate is so much cheaper compared to traveling to Ukraine. Ukrainians would sacrifice numerous things to be a superb partner and be a superb dad or mum to your future kids. Language limitations can put a lot of pressure on a fledgling relationship, however with somewhat exhausting work, you may each be taught and fall in love at the same time. What language do they converse? From a young age, ladies are raised to worth tight-knit family bonds and embrace roles like devoted wives and nurturing mothers. Almost the entire folks in Ukraine worth their family so much. JollyRomance supplies the most spectacular mix of premium communication tools at affordable prices, which makes it the best Ukrainian dating site for seasoned daters and folks new to online relationship. Instead, it’s a worldwide dating site with people discovering matches around the globe. The thing we enjoyed about SofiaDate is the fact that it has a whole bunch of gorgeous ladies alive at any time, supplying you with loads of dating opportunities. You don’t need you to convey your date to the fanciest restaurants or spend hundreds of dollars on gifts.
Thanks for your post. I like your work you can also check mine anchor text
Tu salud con descuentos, gracias a Laboratorios Chopo y su participación en el Hot Sale
2025.