EF Core Pagination: The Right Way to Handle Large Data Sets

Returning thousands of records from an API might work during development.

But what happens when your table has millions of rows?

This is where pagination becomes essential.

---

A common mistake:

var users = await _context.Users
    .ToListAsync();

It loads everything into memory.

Problems:

❌ Higher memory usage

❌ Slower response time

❌ Increased database load

❌ Poor user experience

---

Better Approach: Pagination with EF Core

var users = await _context.Users
    .OrderBy(x => x.Id)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

How it works:

Skip()
➡️ Ignores previous pages

Take()
➡️ Returns only the required number of records

Example:

PageNumber = 3
PageSize = 20

EF Core skips:

(3 - 1) × 20 = 40 records

Then returns:

Next 20 records

---

Important Pagination Practices

✅ Always use OrderBy()

Without ordering, database results are not guaranteed.

✅ Use projection when possible

Instead of:

.ToListAsync()

Prefer:

.Select(x => new UserDto
{
    Id = x.Id,
    Name = x.Name
})
.ToListAsync();

Only fetch the data you need.

✅ Return pagination metadata

Example:

{
  "pageNumber": 1,
  "pageSize": 20,
  "totalRecords": 500
}

---

For Large Tables

Skip() and Take() work well for most scenarios.

But for millions of records, consider:

Keyset Pagination (Seek Pagination)

It avoids the performance cost of skipping large numbers of rows.

---

💡 Rule of thumb:

Pagination is not just a UI feature.

It is a database performance strategy.

A scalable API should control how much data it retrieves and sends.

---

👇 What pagination approach do you prefer in your APIs?

Offset pagination (Skip/Take) or Keyset pagination?

♻️ If you found this helpful, feel free to repost and share it with your network.

👉 Follow Faiz Ahmed Rasel for more .NET tips, tutorials, and deep dives.

#DotNet #EntityFrameworkCore #CSharp #WebAPI #BackendEngineering