How to Increase EF Core Performance for Read Queries in .NET

A kitchen that cooks to order

Think about a small tea stall near a railway station. When a customer asks for one cup of tea, a good stall owner pours exactly one cup. He does not boil a giant pot of tea for the whole street, taste every cup, and then hand you one. That would waste milk, gas, and time.

A slow EF Core query is like that wasteful owner. It often asks the database for far more than it needs. It pulls every column of every row, keeps a copy of each one "just in case", and sometimes makes one trip to the kitchen for every single item on the list.

A fast read query is like the careful stall owner. It asks for exactly what it needs, in as few trips as possible, and it does not keep extra copies it will never use. This article shows you the simple habits that turn slow read queries into fast ones.

We will use plain examples. Everything here works on .NET 10 (LTS) with C# 14, and most of it works on older versions too. The good news is that .NET 10 made EF Core materialization (turning rows into objects) faster on its own, so you get some speed for free just by upgrading.

What makes a read query slow?

Before we fix anything, let us see where the time goes. A read query has three main steps.

Most slow queries waste time in one of these spots:

• They ask for too many columns (loading whole entities when a few fields would do).
• They ask for too many rows (no paging, so a million rows come back).
• They make too many trips to the database (the N+1 problem).
• They keep extra copies of every row for change tracking, even for read-only data.
• They run the same query shape again and again and pay the translation cost each time.

Each section below fixes one of these. Let us start with the easiest win.

Tip 1: Use AsNoTracking for read-only data

By default, EF Core watches every entity it loads. It keeps a hidden copy of each one so that later, if you change a property and call SaveChanges, it knows what changed. This is called change tracking, and it is very useful when you plan to edit data.

But on a read-only screen, like a product list or a report, you are never going to edit those rows. So all that watching is wasted work and wasted memory.

AsNoTracking() tells EF Core: "I am only reading. Do not bother remembering these."

// Slow on big lists: EF Core tracks every row.
var products = await context.Products
    .Where(p => p.IsActive)
    .ToListAsync();
 
// Faster: no tracking snapshot is built.
var products = await context.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .ToListAsync();

For large result sets this is often 30 to 50 percent faster and uses much less memory, because EF Core skips building a snapshot of each entity. Use it on every GET endpoint, every list view, every search result, and every background job that only reads.

One small note: if you Include related rows and they appear many times, add AsNoTrackingWithIdentityResolution() so EF Core still returns one shared object per row instead of duplicates. For most flat read lists, plain AsNoTracking() is what you want.

Tip 2: Project into a DTO with Select

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

The cure is a projection. With Select, you tell EF Core exactly which fields you want. EF Core then writes SQL that fetches only those columns.

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

This does three good things at once:

1. It fetches fewer columns, so less data travels back.
2. It builds smaller objects, so less memory is used.
3. It turns off change tracking automatically, because a ProductListItem is not a tracked entity.

So a projection often gives you the benefit of Tip 1 for free, plus the column savings. For read-only lists, projecting into a small DTO or record is usually the single best habit you can build.

Tip 3: Fix the N+1 problem with Include or projection

Here is a trap that catches almost everyone. Suppose you load a list of orders, then loop over them and read each order's customer name.

var orders = await context.Orders.ToListAsync();
foreach (var order in orders)
{
    // Each access here may fire a NEW query!
    Console.WriteLine(order.Customer.Name);
}

If lazy loading is on, this runs one query for the orders, then one more query per order to fetch each customer. With 100 orders, that is 101 queries. This is the famous N+1 problem, and it can make a fast page crawl.

There are two clean fixes.

Fix A: eager load with Include. This brings the related data in the same trip using a JOIN.

var orders = await context.Orders
    .AsNoTracking()
    .Include(o => o.Customer)
    .ToListAsync();

Fix B: project only what you need. This is even leaner, because you never load the full related entity at all.

var rows = await context.Orders
    .Select(o => new
    {
        o.Id,
        o.Total,
        CustomerName = o.Customer.Name
    })
    .ToListAsync();

Both turn 101 queries into one. When you only need a couple of fields from the related table, prefer the projection.

Tip 4: Use split queries to avoid cartesian explosion

Include is great, but it has a sharp edge. When you Include two or more collections in one query, EF Core builds one big JOIN. The database then pairs every child row of one collection with every child row of the other. The rows multiply.

If a blog has 10 posts and 10 tags, a single JOIN query returns 10 × 10 = 100 rows to describe only 20 real things. With bigger collections this blows up fast and floods your app with duplicated data. This is called cartesian explosion.

The fix is AsSplitQuery(). It tells EF Core to load each collection with its own SQL query and stitch the results together in memory. So instead of 100 multiplied rows, you get 10 posts and 10 tags, separately.

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

A simple rule to remember:

One caution: a split query runs several separate SQL statements, so data could change between them. If you need the reads to be perfectly consistent, wrap them in a transaction with a suitable isolation level. Also note that EF Core 10 fixed a subtle bug where AsSplitQuery combined with Take and Include could order rows differently across the split parts. On .NET 10 the ordering is now consistent, which is one more reason to be on the latest version.

Tip 5: Always page large results

Never send a million rows to a web page. No human reads them, and your server has to hold them all in memory first. Instead, fetch one page at a time with Skip and Take.

var pageNumber = 3;
var pageSize = 20;
 
var page = await context.Products
    .AsNoTracking()
    .OrderBy(p => p.Id)              // paging needs a stable order
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .Select(p => new ProductListItem(p.Id, p.Name, p.Price))
    .ToListAsync();

Two things matter here:

• You must add an OrderBy. Without a stable order, the database may return different rows for the "same" page.
• Skip/Take (offset paging) is simple and great for small page numbers. But deep pages (like page 5,000) get slow because the database still counts past all the skipped rows. For very deep paging, use keyset (cursor) pagination, where you ask for rows after the last id you saw. That stays fast no matter how deep you go.

Tip 6: Help the database with indexes

Even a perfect LINQ query is slow if the database has to scan every row to find your matches. An index is like the index at the back of a textbook: instead of reading every page, the database jumps straight to the right spot.

If you often filter or sort by a column, add an index for it. You can do this in your model configuration.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .HasIndex(o => o.CustomerId);
 
    // A composite index for a common filter + sort combination.
    modelBuilder.Entity<Order>()
        .HasIndex(o => new { o.CustomerId, o.CreatedAt });
}

A quick way to find missing indexes is to log the SQL EF Core generates, run it in your database tool, and look at the execution plan. If you see a full table scan on a column you filter by, an index usually helps. Be careful though: every index makes writes a little slower and uses disk, so add them where reads truly need them, not everywhere.

Tip 7: Use compiled queries for very hot paths

Every time EF Core runs a LINQ query, it must translate that expression tree into SQL. EF Core caches a lot of this, but for a query that runs thousands of times per second with the same shape, you can skip even that small cost with a compiled query.

private static readonly Func<AppDbContext, int, Task<Product?>> GetProductById =
    EF.CompileAsyncQuery((AppDbContext db, int id) =>
        db.Products.AsNoTracking().FirstOrDefault(p => p.Id == id));
 
// Use it like a fast, pre-built function.
var product = await GetProductById(context, 42);

Compiling once and reusing the delegate removes the per-call translation work. The saving is small per call, so this only pays off for truly hot queries that run constantly. For a query you run a few times a day, do not bother; the plain LINQ version is clearer.

Putting it all together

For a typical list endpoint, you rarely need exotic tricks. The "80 percent solution" is just three habits stacked together: projection plus no tracking plus paging. Add an index where you filter, and reach for split queries only when you include more than one collection.

Here is a single endpoint that uses the core ideas at once.

public async Task<List<ProductListItem>> GetActiveProductsAsync(
    AppDbContext context, int page, int size)
{
    return await context.Products
        .AsNoTracking()
        .Where(p => p.IsActive)
        .OrderBy(p => p.Name)
        .Skip((page - 1) * size)
        .Take(size)
        .Select(p => new ProductListItem(p.Id, p.Name, p.Price))
        .ToListAsync();
}

This query loads only three columns, tracks nothing, returns one page, and runs in a single trip to the database. That is what a fast read query looks like.

A quick word on measuring

Do not guess where the time goes. Turn on SQL logging in development so you can see the exact queries EF Core sends. If you see the same query repeating in a loop, you found an N+1 problem. If you see a query returning thousands of duplicated rows, you found a cartesian explosion. Measure first, then fix the real bottleneck. A change that looks clever but fixes nothing is just extra code.

Also remember that the database itself matters. Sometimes the fastest fix is not in C# at all, but a missing index or a badly shaped table. EF Core can only be as fast as the database underneath it.

Quick recap

• AsNoTracking for read-only data skips change tracking. Often 30 to 50 percent faster and lighter on memory.
• Project into a DTO with Select to fetch only the columns you need. It also turns off tracking for free.
• Fix N+1 with Include or a projection, so related data comes in one trip instead of one trip per row.
• Use split queries (AsSplitQuery) when you include two or more collections, to avoid cartesian explosion. EF Core 10 made split ordering consistent.
• Always page big results with Skip/Take plus an OrderBy. Use keyset paging for very deep pages.
• Add indexes on columns you filter or sort by, but not everywhere.
• Compile only your hottest, most repeated queries.
• Measure with SQL logging before optimizing, and remember the database matters too.
• On .NET 10 you also get faster materialization for free, so keep your projects up to date.

References and further reading

• Efficient Querying — EF Core (Microsoft Learn)
• Single vs. Split Queries — EF Core (Microsoft Learn)
• How To Increase EF Core Performance for Read Queries in .NET (antondevtips)
• 10 EF Core Performance Mistakes (and How to Fix Them) in .NET 10 (codewithmukesh)
• 5 EF Core Query Techniques I Use in Every .NET 10 Project (TheCodeMan)