Working with huge data in EF Core
Tables with millions of rows need different habits. This lesson shows keyset paging, streaming exports with AsAsyncEnumerable, bulk updates and deletes with ExecuteUpdate and ExecuteDelete, chunked inserts, and the settings that keep long queries reliable.
- Select projectionsLoad only the columns the page needs
- AsNoTrackingSkip the change tracker for read-only data
- Keyset paginationWhere Id > lastId instead of a large Skip
- AsAsyncEnumerableStream rows one at a time for exports and jobs
- ExecuteUpdate / ExecuteDeleteOne SQL statement changes many rows, nothing loaded
- Chunked SaveChangesInsert in batches and clear the tracker between them
- SqlBulkCopyLoad millions of rows straight into a table
- AsSplitQueryAvoid huge JOIN results when including several collections
- IndexesMake Where and OrderBy columns fast; check the SQL
Each tool keeps the work in SQL Server and the memory use of your app small.
The idea in plain words
With small tables almost any code works. With 10 million rows, loading everything into memory, tracking every entity or paging with a large Skip will make pages slow or crash the app. The rule is simple: ask the database for exactly the rows and columns you need, and let it do the filtering, sorting, counting and bulk changes.
Every tool below follows that rule. Always check the SQL EF Core generates (it is in the log at Information level) and make sure the columns in Where and OrderBy have indexes.
Fast paging with keyset pagination
Skip(49950).Take(50) makes SQL Server read and throw away 49,950 rows to show page 1,000. Keyset paging remembers the last key the user saw and starts after it, so page 1,000 is as fast as page 1, as long as the column is indexed.
public record OrderRow(int OrderId, DateTime OrderedAt, string Customer, decimal Total);
public Task<List<OrderRow>> NextPageAsync(int? afterId, int size = 50) =>
db.Orders
.Where(o => afterId == null || o.OrderId > afterId)
.OrderBy(o => o.OrderId)
.Select(o => new OrderRow(o.OrderId, o.OrderedAt, o.Customer.Name, o.Total))
.Take(size)
.ToListAsync();
// The view gets a "Next" link with ?afterId=<last OrderId on this page>Stream big results instead of loading them
ToListAsync holds every row in memory at once. AsAsyncEnumerable hands you one row at a time while the rest are still being read, so exporting 5 million orders to CSV uses the same small amount of memory as exporting 50. AsNoTracking stops EF Core keeping a copy of each entity.
await using var file = new StreamWriter("orders.csv");
await file.WriteLineAsync("OrderId,OrderedAt,Total");
await foreach (var o in db.Orders
.AsNoTracking()
.Where(o => o.OrderedAt >= from)
.Select(o => new { o.OrderId, o.OrderedAt, o.Total })
.AsAsyncEnumerable())
{
await file.WriteLineAsync($"{o.OrderId},{o.OrderedAt:yyyy-MM-dd},{o.Total}");
}Change many rows without loading them
ExecuteUpdateAsync and ExecuteDeleteAsync (EF Core 7+) turn LINQ into a single UPDATE or DELETE that runs in the database. Nothing is loaded or tracked. For very large deletes, work in chunks so each transaction, its locks and the transaction log stay small.
// One UPDATE statement for every product in the category
await db.Products
.Where(p => p.CategoryId == 3)
.ExecuteUpdateAsync(s => s.SetProperty(p => p.Price, p => p.Price * 1.10m));
// Delete a year of old logs, 5,000 rows at a time
var cutoff = DateTime.UtcNow.AddYears(-1);
int deleted;
do
{
deleted = await db.AuditLogs
.Where(l => l.CreatedAt < cutoff)
.Take(5000)
.ExecuteDeleteAsync();
} while (deleted > 0);Insert lots of rows
SaveChanges already batches INSERT statements, but the change tracker grows with every entity you add. Save in chunks and clear the tracker between them. For millions of rows, SqlBulkCopy (from Microsoft.Data.SqlClient) streams data straight into a table and is much faster than any ORM.
foreach (var chunk in importedRows.Chunk(2000))
{
db.Products.AddRange(chunk.Select(r => new Product { Name = r.Name, Price = r.Price }));
await db.SaveChangesAsync(); // a few batched INSERT statements
db.ChangeTracker.Clear(); // forget saved entities so memory stays flat
}Settings that help with big queries
- Raise the command timeout for reports and jobs: options.UseSqlServer(cs, sql => sql.CommandTimeout(120)). The default is 30 seconds.
- Use AsSplitQuery when you Include several collections; one big JOIN can multiply rows into millions (a cartesian explosion).
- Use Select projections for lists and AsNoTracking for read-only entities.
- Count wisely: CountAsync on a huge table on every page view is expensive. Cache the total or show Next and Previous instead of page numbers.
- Move heavy aggregates into an indexed view or a scheduled summary table, and map it as shown in the views lesson.
Common mistakes
- Calling ToList() or AsEnumerable() before Where, which loads the whole table and filters in C#.
- Loading entities only to delete or update them one by one in a loop.
- Paging deep into a large table with Skip.
- One giant SaveChanges with 500,000 tracked entities.
- Ordering or filtering on columns with no index, so every query scans the table.
Interview question
How would you show page 10,000 of a 50-million-row table efficiently with EF Core?
Avoid Skip/Take for deep pages, because SQL Server must read and discard every skipped row. Use keyset (seek) pagination: order by an indexed, unique column such as the primary key, remember the last key of the current page, and query Where(o => o.Id > lastId).OrderBy(o => o.Id).Take(pageSize). Project only the needed columns with Select, avoid tracking, and avoid an exact COUNT on every request.
Want the full details? Read the official Microsoft documentation for this topic.