EF Core Bulk Data Retrieval: 5 Methods You Should Know

A library cart problem

Imagine you work at a big city library. A reader walks up and says: "Bring me every book in the building."

You could push a giant cart through every aisle, pile thousands of books on it, drag the whole pile to the front desk, and dump it there. The reader only wanted to glance at a few titles, but now there is a mountain of books between you both, your arms hurt, and the cart almost broke.

A smart librarian does not do this. A smart librarian asks better questions: Which books? Do you need the whole book, or just the title and author on a card? Should I bring them one shelf at a time?

Reading data from a database with EF Core works exactly the same way. If you grab everything at once, your app slows down and uses a lot of memory. If you ask smart, focused questions, everything stays fast.

This post shows 5 simple methods to read large amounts of data the smart way. Each one is a small change, but together they make a huge difference.

Method 1 — AsNoTracking for read-only data

By default, EF Core tracks every entity it returns. Tracking means EF Core keeps a hidden copy (a snapshot) of each row so it can notice if you change it later and save those changes. This is useful when you plan to edit and save data. But when you only read data and never change it, that snapshot is wasted work.

Think of it like the librarian writing down the exact position of every book "just in case you move it." If you are only reading, that note-taking is pointless and slow.

For any read-only query, add AsNoTracking():

// Read-only list for a GET endpoint
var products = await context.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .ToListAsync();

This tells EF Core: "Do not bother tracking these. I will not save changes to them." EF Core then skips the snapshot step, does less work per row, and uses less memory.

On large result sets, AsNoTracking() is commonly 30 to 50 percent faster and uses around half the memory. The more rows you read, the bigger the win.

When should you use it? Use AsNoTracking() for GET endpoints, list views, search results, reports, and background jobs that only read. Do not use it when you load an entity, change it, and call SaveChanges() — because then you actually need tracking.

Method 2 — Project only the columns you need

Even with no tracking, loading a full entity pulls every column of the table. If your Product has 25 columns but the screen only shows the name and price, you are dragging 23 extra columns across the network for nothing.

The fix is projection with Select. You build a small object (often a DTO) that holds only the fields you need:

public record ProductCard(int Id, string Name, decimal Price);
 
var cards = await context.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .Select(p => new ProductCard(p.Id, p.Name, p.Price))
    .ToListAsync();

Now EF Core generates SQL that selects only Id, Name, and Price. Less data leaves the database, less data crosses the network, and less memory is used in your app. Projection also makes AsNoTracking() automatic for the projected shape, because a plain DTO is not an entity EF Core needs to track.

Here is how the two approaches compare for a wide table:

A good habit: never return full entities from a read API. Project into a DTO that matches exactly what the caller needs.

Method 3 — Pagination so you never load everything

The library reader rarely needs all the books at once. They look at one page of results, then maybe the next. Your app is the same. A list screen usually shows 20 or 50 rows, not 50,000.

Pagination means returning results one page at a time. The classic way uses Skip and Take:

public async Task<List<ProductCard>> GetPageAsync(int page, int pageSize)
{
    return await context.Products
        .AsNoTracking()
        .OrderBy(p => p.Id)          // paging needs a stable order
        .Skip((page - 1) * pageSize) // jump past earlier pages
        .Take(pageSize)              // take just this page
        .Select(p => new ProductCard(p.Id, p.Name, p.Price))
        .ToListAsync();
}

Skip becomes OFFSET and Take becomes FETCH in SQL. Always add an OrderBy — without a stable order, the database can return rows in different orders and your pages will overlap or skip items.

The hidden cost of OFFSET

Offset paging has a catch. To return page 5000, the database must still scan past all the rows before it. Page 1 is cheap. Page 5000 means walking past tens of thousands of rows every single time. On huge tables this gets slow.

The cure is keyset pagination (also called seek paging). Instead of "skip 100000 rows," you remember the last row you saw and ask for rows after it:

// Next page after the last Id we showed
var nextPage = await context.Products
    .AsNoTracking()
    .OrderBy(p => p.Id)
    .Where(p => p.Id > lastSeenId) // seek, do not skip
    .Take(pageSize)
    .Select(p => new ProductCard(p.Id, p.Name, p.Price))
    .ToListAsync();

Because the database can jump straight to Id > lastSeenId using the index, the cost stays the same no matter how deep you page.

When to use which:

Method 4 — Split queries for multiple includes

Sometimes you need related data. You load a Blog and also want its Posts and its Tags. The natural code looks fine:

var blogs = await context.Blogs
    .AsNoTracking()
    .Include(b => b.Posts)
    .Include(b => b.Tags)
    .ToListAsync();

But behind the scenes, EF Core turns this into one SQL query that joins blogs to posts and to tags at the same time. To put both collections in one flat table, the database pairs every post with every tag. A blog with 20 posts and 20 tags does not give 40 rows — it gives 20 × 20 = 400 rows, with the same data repeated again and again. This is called the cartesian explosion, and it can make a small query return a mountain of duplicated data.

The fix is AsSplitQuery(). EF Core then runs a separate SQL query for each collection and stitches the results together in memory:

var blogs = await context.Blogs
    .AsNoTracking()
    .Include(b => b.Posts)
    .Include(b => b.Tags)
    .AsSplitQuery() // one query per collection, no explosion
    .ToListAsync();

Now you get 20 post rows and 20 tag rows, not 400. Much less data travels from the database.

One caution: split queries run as several separate SQL statements. If the data changes between them, results could be slightly inconsistent unless you wrap them in a transaction. For most read screens this is fine. Use split queries when you include two or more collections. For a single collection or no collection, a single query is usually faster because it makes one round-trip. Always measure with real data.

Method 5 — Stream rows instead of loading all at once

ToListAsync() pulls every matching row into a list in memory before you touch any of it. For a page of 20 rows that is fine. For a background job that processes a million rows, that list could eat a lot of RAM.

For very large reads, stream the rows. EF Core lets you use IAsyncEnumerable with await foreach, so you handle one row at a time and the memory stays flat:

// Process millions of rows without loading them all
var query = context.Orders
    .AsNoTracking()
    .Where(o => o.Status == OrderStatus.Pending)
    .AsAsyncEnumerable();
 
await foreach (var order in query)
{
    await ProcessOrderAsync(order);
    // each order is handled, then released — memory stays low
}

Instead of one giant cart, the librarian hands you books one at a time. You read each, set it down, and pick up the next. The pile never builds up.

Streaming is ideal for exports, migrations, and batch jobs. For small UI lists, plain ToListAsync() is simpler and totally fine — you only need streaming when the result set is genuinely large.

Putting it all together

The real power comes from combining these methods. Here is a single read query that uses no-tracking, projection, and pagination together:

public async Task<List<ProductCard>> GetActiveProductsPageAsync(
    int page, int pageSize)
{
    return await context.Products
        .AsNoTracking()                            // method 1
        .Where(p => p.IsActive)
        .OrderBy(p => p.Id)
        .Skip((page - 1) * pageSize)               // method 3
        .Take(pageSize)
        .Select(p => new ProductCard(              // method 2
            p.Id, p.Name, p.Price))
        .ToListAsync();
}

This one method reads only the rows you need, only the columns you need, and never tracks them. For most list endpoints, this is the pattern to reach for first.

A few extra notes for real projects on .NET 10 (the current LTS release) and C# 14:

• You can set no-tracking as the default for a DbContext with UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking), then opt back into tracking only where you save changes.
• You can make split queries the default with UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery) in OnConfiguring.
• For queries you run over and over with the same shape, look at compiled queries to skip re-building the query plan each time.

These are small switches, but on a busy API they reduce database load and keep response times low.

Common mistakes to avoid

• Forgetting OrderBy before paging. Without a stable sort, pages overlap or drop rows. The database is free to return rows in any order.
• Returning full entities from a read API. Project into a DTO so you never leak extra columns or trigger lazy loads.
• Using AsSplitQuery for a single collection. That just adds extra round-trips with no benefit. Split only when you include two or more collections.
• Calling ToListAsync() on a million-row job. Stream instead, or you risk running out of memory.
• Using AsNoTracking() and then trying to save changes. Untracked entities are not watched, so SaveChanges() will not pick up your edits.

Quick recap

• AsNoTracking skips change tracking for read-only data — often 30 to 50 percent faster and about half the memory.
• Projection with Select reads only the columns you need, shrinking network and memory cost.
• Pagination returns one page at a time. Use Skip/Take for jumping to pages, and keyset paging to stay fast on deep pages.
• Split queries (AsSplitQuery) stop the cartesian explosion when you include two or more collections.
• Streaming with IAsyncEnumerable and await foreach keeps memory flat for very large batch reads.
• Combine no-tracking, projection, and pagination in one query for the best default read pattern.
• Always measure with real data before deciding which method fits.

References and further reading

• Efficient Querying — EF Core (Microsoft Learn)
• Single vs. Split Queries — EF Core (Microsoft Learn)
• Tracking vs. No-Tracking Queries — EF Core (Microsoft Learn)
• Pagination — EF Core (Microsoft Learn)
• How To Increase EF Core Performance for Read Queries (antondevtips)