PagedList with Entity Framework getting all records

Question

PagedList is an Paging library.

_dbContext.Products.ToList().ToPagedList(1, 25);

Above code will get first 25 record in database for Page 1.

The problem is that the ToList() call will get all record in the database. Then the ToPageList() call will select the first 25 records.

How do I combine EF with PagedList so that I only get the first 25 records in the database? And not get all records, and then take the first 25 record.

PS: Should I write my own Paging library or use an online library? Please suggest me any other library.

Answer 1

Indeed, you are doing a ToList(), so the query will be executed, and deferred execution won't be deferred anymore.

You can try it without the ToList() call, but it all depends on the implementation of the ToPagedList(int, int) method.

You can just do the paging yourself.

const int pageSize = 25;
const int pageNumber = 1;

IQueryable<Product> query = _dbContext.Products;
var pagedQuery = query.Skip(pageSize * (pageNumber - 1)).Take(pageSize).ToList();

And yes, you can just hide this logic behind an extension method on IQueryable<T>.

public static class IQueryableExtensions {
    public static IQueryable<T> ToPagedQuery<T>(this IQueryable<T> query, int pageSize, int pageNumber) {
        return query.Skip(pageSize * (pageNumber - 1)).Take(pageSize);
    }
}

Answer 2

The simplest solution is to use AsEnumerable() but it requires ordering. Unlike ToList(), AsEnumerable() doesn't hit the database.

_dbContext.Products.OrderBy(p => p.Id).AsEnumerable().ToPagedList(1, 25);