C# Entity Framework Pagination

Question

Is there a way to get the row count of a complex Linq query and millions of records without hitting the db twice or writing 2 separate queries??

I might have my own suggestion. Write a stored procedure, but I'm good with MySQL not MSSQL.

Any better suggestions would be great. Also, if anyone knows if Microsoft is working on adding this feature to the entity framework.

Answer 1

I'd suggest using the Take() function. This can be used to specify the number of records to take from a linq query or List. For example

List<customers> _customers = (from a in db.customers select a).ToList();
var _dataToWebPage = _customers.Take(50);

I use a similar technique in an MVC app where I write the _customers list to the session and then use this list for further pagination queries when the user clicks on page 2, 3 etc. This saves multiple database hits. However if your list is very large then writing it too the session is probably not a good idea.

For pagination you can use the Skip() and Take() function together. For example to get page 2 of the data :

var _dataToWebPage = _customers.Skip(50).Take(50);

Answer 2

I was recently inspired by (copied from) this Code Project article Entity Framework pagination

The code:

public async Task<IList<SavedSearch>> FindAllSavedSearches(int page, int limit)
{
    if (page == 0)
        page = 1;

    if (limit == 0)
        limit = int.MaxValue;

    var skip = (page - 1) * limit;

    var savedSearches = _databaseContext.SavedSearches.Skip(skip).Take(limit).Include(x => x.Parameters);
    return await savedSearches.ToArrayAsync();
}

I'm not experienced with Entity Framework and I've not tested it for performance, so use with caution :)

Answer 3

The common way to show millions of records is simply not to display all pages. Think of it: if you have millions of records, say 20 or even 100 items per page, then you'll have tens of thousands of pages. It does not make sense to show them all. You can simply load the current page and provide a link to the next page, that's it. Or you may load say 100-500 records, but still show only one page and use the loaded records information to generate page links for first several pages (so know for sure how many next pages are available).

Answer 4

It is so easy on SQL Server.

You can write this query:

select count() over(), table.* from table

The count () over() will return the count of the total rows in the result, so you don't need to run two queries. Remember that you should run raw SQL on your context or use Dapper, which returns the result as a view model.

Answer 5

If you need a quick solution you can use XPagedList https://github.com/dncuug/X.PagedList. XPagedList is a library that enables you to easily take an IEnumerable/IQueryable, chop it up into "pages", and grab a specific "page" by an index. For example

var products = await _context.Products.ToPagedListAsync(pageNumber, pageSize)

Answer 6

I created a nuget library that does pagination for you. https://github.com/wdunn001/EntityFrameworkPaginateCore

add nuget to project

Install-Package EntityFrameworkPaginateCore add

using EntityFrameworkPaginateCore; to you provider

Has 1 method and 2 overloads for that method overloads allow sorting and filtering. use the sort object and the filter objects

public async Task<Page<Example>> GetPaginatedExample(
            int pageSize = 10, 
            int currentPage = 1, 
            string searchText = "", 
            int sortBy = 2
            )
        {
            var filters = new Filters<Example>();
                filters.Add(!string.IsNullOrEmpty(searchText), x => x.Title.Contains(searchText));

            var sorts = new Sorts<Example>();
            sorts.Add(sortBy == 1, x => x.ExampleId);
            sorts.Add(sortBy == 2, x => x.Edited);
            sorts.Add(sortBy == 3, x => x.Title);

            try
            {
                return await _Context.EfExample.Select(e => _mapper.Map<Example>(e)).PaginateAsync(currentPage, pageSize, sorts, filters);
            }
            catch (Exception ex)
            {
                throw new KeyNotFoundException(ex.Message);
            }
        }