CODEWITHSUNDEEP
c#entity-frameworkpagination
Jason Foglia
Jason Foglia

Reputation: 2521

C# Entity Framework Pagination

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.

Upvotes: 23

Views: 60640

Answers (6)

S.Mohamed Mahdi Ahmadian zadeh
S.Mohamed Mahdi Ahmadian zadeh

Reputation: 2406

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.

Upvotes: 2

Vinez
Vinez

Reputation: 570

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

Upvotes: 0

Chitova263
Chitova263

Reputation: 789

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)

Upvotes: 3

jk1990
jk1990

Reputation: 361

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 :)

Upvotes: 11

Tim Newton
Tim Newton

Reputation: 905

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

Upvotes: 18

Vladimir Perevalov
Vladimir Perevalov

Reputation: 4159

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).

Upvotes: 3

Related Questions