Do you do pagination database side?

Last updated by Tylah Kapa [SSW] 5 months ago.See history

Pagination can be expensive if all the pages are retrieved from the database before grabbing the relevant page. It's much more efficient to get only the page number requested back from the database.

var query = context
    .Sales
    .AsNoTracking()
    .Where(x => x.SalesPersonId == salesPersonId);
var result = await query.ToListAsync();
int count = result.Count;

result = result
    .Skip(page * pageSize)
    .Take(pageSize);
return (count, result);

Figure: Bad example - Reads all the data from the database, counts the records and filters down to the page

var query = context
    .Sales
    .AsNoTracking()
    .Where(x => x.SalesPersonId == salesPersonId);

int count = await query.CountAsync();

query = query
    .Skip(page * pageSize)
    .Take(pageSize);
var result = await query.ToListAsync();    
return (count, result);

Figure: Good example - Reads only the count and 1 page of data from the database

We open source. Powered by GitHub