EF Core Read Query Performance – Optimization Techniques for .NET

If you’ve ever watched an API endpoint crawl because of a “simple” EF Core query, you already know the pain. Entity Framework Core is a fantastic productivity tool, but its defaults are tuned for correctness and convenience, not raw speed. Left unchecked, an innocent-looking DbContext call can trigger change tracking overhead, N+1 queries, cartesian explosions, or full table scans — and none of it shows up until your data grows or your traffic spikes.

This article walks through the techniques that actually move the needle on read query performance in EF Core, in the order you should reach for them. Whether you’re new to EF Core, a mid-level engineer optimizing an existing API, or a senior developer tuning a high-throughput service, you’ll find concrete, production-ready patterns here — not just theory.

Note: Every technique below assumes you’re optimizing read paths (queries, not SaveChanges()). Write-path performance (batching inserts/updates, SaveChanges tuning) deserves its own article and isn’t covered here.

Before optimizing anything, it helps to know what actually happens between _context.Products.Where(…) and the data landing in your object.

• LINQ expression tree — your query is built as an expression tree, not executed immediately.

• Translation — the EF Core query pipeline translates that expression tree into SQL specific to your database provider.

• Execution — the SQL is sent to the database, which parses it, builds an execution plan (using indexes if available), and returns rows.

• Materialization — EF Core maps the returned rows into .NET objects, optionally tracking them in the DbContext‘s change tracker.

Every technique in this article targets one of these four stages: writing SQL the database can execute efficiently (translation), reducing the data that needs to move (execution), or skipping unnecessary object-mapping work (materialization). Keeping this mental model in mind makes it much easier to reason about why a given optimization helps.

No amount of LINQ tuning will save you from a missing index. If a query filters, sorts, or joins on a column without a supporting index, the database falls back to a full table scan — and that cost grows linearly (or worse) with your table size.

Add an index via the Fluent API on columns you frequently filter or sort by:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .HasIndex(o => o.CustomerId);

    modelBuilder.Entity<Order>()
        .HasIndex(o => new { o.Status, o.CreatedAtUtc })
        .HasDatabaseName("IX_Order_Status_CreatedAtUtc");
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .HasIndex(o => o.CustomerId);

    modelBuilder.Entity<Order>()
        .HasIndex(o => new { o.Status, o.CreatedAtUtc })
        .HasDatabaseName("IX_Order_Status_CreatedAtUtc");
}

A few rules of thumb:

• Composite indexes should list the most selective / most-filtered column first, matching your typical WHERE clause order.
• Covering indexes (including extra columns via .IncludeProperties() on SQL Server) let the database satisfy a query straight from the index, without touching the table at all.
• Don’t over-index. Every index speeds up reads but slows down writes and consumes storage — index what you actually query, not every column.
• Use your database’s query plan tool (e.g., SQL Server’s Actual Execution Plan, PostgreSQL’s EXPLAIN ANALYZE) to confirm an index is actually being used, rather than assuming it is.

Indexing is a database-level concern, but it’s the single highest-leverage fix on this list — get it right before reaching for anything else.

By default, EF Core tracks every entity it loads so it can detect changes for SaveChanges(). For read-only queries — the majority of queries in most APIs — this tracking is pure overhead: extra memory, extra CPU for snapshotting, and slower materialization.

var activeCustomers = await _context.Customers
    .Where(c => c.IsActive)
    .AsNoTracking()
    .ToListAsync(cancellationToken);

var activeCustomers = await _context.Customers
    .Where(c => c.IsActive)
    .AsNoTracking()
    .ToListAsync(cancellationToken);

For services that are almost entirely read-heavy (reporting APIs, read replicas, CQRS query handlers), it’s often worth setting the default at the DbContext level:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}

You can then opt back into tracking explicitly with .AsTracking() on the few queries that need it (typically ones followed by an update).

Loading full entities when you only need three fields is one of the most common sources of wasted bandwidth and materialization time. Projections tell EF Core to generate SQL that only selects the columns you actually use.

var summaries = await _context.Orders
    .Where(o => o.Status == OrderStatus.Shipped)
    .Select(o => new OrderSummaryDto
    {
        Id = o.Id,
        CustomerName = o.Customer.Name,
        Total = o.Total
    })
    .AsNoTracking()
    .ToListAsync(cancellationToken);

var summaries = await _context.Orders
    .Where(o => o.Status == OrderStatus.Shipped)
    .Select(o => new OrderSummaryDto
    {
        Id = o.Id,
        CustomerName = o.Customer.Name,
        Total = o.Total
    })
    .AsNoTracking()
    .ToListAsync(cancellationToken);

This gives you two wins at once: the generated SQL only pulls the needed columns (less I/O, smaller result set), and EF Core skips materializing full entity graphs it would otherwise discard. Projections also sidestep the need for Include() entirely in many cases, since EF Core can resolve related data (like o.Customer.Name) through a join in the same query.

Rule of thumb: if your endpoint returns a DTO, project directly to that DTO in the query. Don’t load the full entity and map afterward in memory.

The N+1 problem happens when a query triggers one additional round-trip to the database per row — usually because a navigation property is being lazy-loaded inside a loop.

// N+1: one query for orders, then one query per order for its items
var orders = await _context.Orders.ToListAsync(cancellationToken);
foreach (var order in orders)
{
    var itemCount = order.Items.Count; // triggers a lazy-load query each time
}

// N+1: one query for orders, then one query per order for its items
var orders = await _context.Orders.ToListAsync(cancellationToken);
foreach (var order in orders)
{
    var itemCount = order.Items.Count; // triggers a lazy-load query each time
}

Fix it with eager loading via Include/ThenInclude, so everything comes back in a single round trip:

var orders = await _context.Orders
    .Include(o => o.Items)
    .AsNoTracking()
    .ToListAsync(cancellationToken);

var orders = await _context.Orders
    .Include(o => o.Items)
    .AsNoTracking()
    .ToListAsync(cancellationToken);

For large related collections, filtered includes let you load only what you need instead of the entire child collection:

var orders = await _context.Orders
    .Include(o => o.Items.Where(i => i.Quantity > 0))
    .AsNoTracking()
    .ToListAsync(cancellationToken);

var orders = await _context.Orders
    .Include(o => o.Items.Where(i => i.Quantity > 0))
    .AsNoTracking()
    .ToListAsync(cancellationToken);

If you’re not using lazy loading at all (recommended for most APIs), disable it explicitly so an accidental navigation access fails loudly in development instead of silently degrading performance in production:

optionsBuilder.UseLazyLoadingProxies(false); // default, but worth being explicit

optionsBuilder.UseLazyLoadingProxies(false); // default, but worth being explicit

When you Include multiple collection navigations in one query, EF Core generates a single SQL query with joins across all of them. If Order has both Items and Payments, joining both collections at once multiplies rows — this is the cartesian explosion problem, and it can make result sets balloon in size for no good reason.

AsSplitQuery() tells EF Core to issue separate SQL queries for each included collection instead:

var orders = await _context.Orders
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .AsSplitQuery()
    .AsNoTracking()
    .ToListAsync(cancellationToken);

var orders = await _context.Orders
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .AsSplitQuery()
    .AsNoTracking()
    .ToListAsync(cancellationToken);

Neither option is universally “faster” — single query wins when data is small and network round-trips are the bottleneck; split queries win when the cartesian product would otherwise multiply your data several times over. Measure both on your actual data shape before deciding.

Returning thousands of rows from an endpoint is rarely necessary and always slow. Pagination limits both the data pulled from the database and the memory used to materialize it.

Offset pagination (simple, but degrades on large offsets):

var page = await _context.Products
    .OrderBy(p => p.Id)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .AsNoTracking()
    .ToListAsync(cancellationToken);

var page = await _context.Products
    .OrderBy(p => p.Id)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .AsNoTracking()
    .ToListAsync(cancellationToken);

Cursor (keyset) pagination (consistently fast, regardless of how deep you page):

var page = await _context.Products
    .Where(p => p.Id > lastSeenId)
    .OrderBy(p => p.Id)
    .Take(pageSize)
    .AsNoTracking()
    .ToListAsync(cancellationToken);

var page = await _context.Products
    .Where(p => p.Id > lastSeenId)
    .OrderBy(p => p.Id)
    .Take(pageSize)
    .AsNoTracking()
    .ToListAsync(cancellationToken);

Use offset pagination for small, admin-style tables where simplicity matters more than scale. Use cursor pagination for anything public-facing, high-volume, or infinite-scroll style.

Every LINQ query EF Core sees for the first time goes through translation to SQL, which is then cached internally. For most apps that cache is enough. But for extremely hot paths — queries executed thousands of times per second with the exact same shape — compiled queries skip the query-cache lookup itself.

private static readonly Func<AppDbContext, int, Task<Product?>> GetProductById =
    EF.CompileAsyncQuery((AppDbContext context, int id) =>
        context.Products.AsNoTracking().FirstOrDefault(p => p.Id == id));

// usage
var product = await GetProductById(_context, productId);

private static readonly Func<AppDbContext, int, Task<Product?>> GetProductById =
    EF.CompileAsyncQuery((AppDbContext context, int id) =>
        context.Products.AsNoTracking().FirstOrDefault(p => p.Id == id));

// usage
var product = await GetProductById(_context, productId);

This is a micro-optimization — reach for it only after profiling shows a specific query is a genuine hot path. Applying it everywhere adds noise without meaningful benefit for most queries.

Not every read query is a good fit for LINQ. Complex aggregations, window functions, or queries with database-specific features are often clearer — and faster — written as raw SQL.

var topCustomers = await _context.Database
    .SqlQuery<CustomerRevenueDto>($"""
        SELECT c.Id, c.Name, SUM(o.Total) AS Revenue
        FROM Customers c
        JOIN Orders o ON o.CustomerId = c.Id
        WHERE o.CreatedAtUtc >= {startDate}
        GROUP BY c.Id, c.Name
        ORDER BY Revenue DESC
    """)
    .ToListAsync();

var topCustomers = await _context.Database
    .SqlQuery<CustomerRevenueDto>($"""
        SELECT c.Id, c.Name, SUM(o.Total) AS Revenue
        FROM Customers c
        JOIN Orders o ON o.CustomerId = c.Id
        WHERE o.CreatedAtUtc >= {startDate}
        GROUP BY c.Id, c.Name
        ORDER BY Revenue DESC
    """)
    .ToListAsync();

SqlQuery<T> (EF Core 8+) is parameterized safely through interpolation, so you get raw SQL performance without opening yourself up to SQL injection. Reach for this when the LINQ equivalent would be convoluted, when you need database-specific functions EF Core doesn’t translate, or when you’ve profiled a LINQ query and found the generated SQL isn’t as efficient as hand-written SQL would be.

Some read queries don’t need to hit the database at all on every request — reference data, configuration, rarely-changing lookups. Caching removes the database round-trip entirely for these cases.

• In-memory caching (IMemoryCache) works well for single-instance apps or data that’s fine being slightly stale per-instance.
• Distributed caching (Redis via IDistributedCache) is necessary once you scale to multiple instances and need a shared cache.
• HybridCache (introduced in .NET 9) combines both: an in-process L1 cache backed by a distributed L2 cache, with built-in stampede protection so concurrent requests for the same missing key don’t all hit the database simultaneously.

var product = await _hybridCache.GetOrCreateAsync(
    $"product:{id}",
    async cancel => await _context.Products
        .AsNoTracking()
        .FirstOrDefaultAsync(p => p.Id == id, cancellationToken));

var product = await _hybridCache.GetOrCreateAsync(
    $"product:{id}",
    async cancel => await _context.Products
        .AsNoTracking()
        .FirstOrDefaultAsync(p => p.Id == id, cancellationToken));

Cache invalidation is the hard part here — only cache data where you have a clear invalidation strategy (TTL, explicit eviction on write, or both). Caching a query you can’t invalidate correctly trades a performance problem for a correctness bug.

A handful of smaller changes that add up, especially in high-throughput services:

• DbContext pooling — AddDbContextPool reuses DbContext instances instead of allocating a new one per request, reducing GC pressure.

• .AnyAsync() instead of .CountAsync() > 0 — Any translates to an EXISTS query that stops at the first match; Count forces the database to count every matching row.

• Avoid client-side evaluation — if part of your query can’t be translated to SQL, EF Core silently pulls data into memory and finishes the filter there. Watch your logs for EF Core Client evaluation warnings; they usually point to a filter that should be rewritten in a translatable form.

• Batch multiple lookups — instead of looping and querying per item, use .Where(x => ids.Contains(x.Id)) to fetch everything in one round trip.

• .AsNoTracking() + .TagWith() — tagging queries makes them identifiable in SQL Server Profiler or your database logs, which speeds up finding the exact query behind a slow trace.

None of these alone will fix a badly designed query, but together they remove a steady drip of avoidable overhead.

Consider a typical GET /api/products?category=electronics&page=2 endpoint backed by this original implementation:

var products = await _context.Products
    .Include(p => p.Category)
    .Include(p => p.Reviews)
    .Where(p => p.Category.Name == category)
    .ToListAsync(cancellationToken);

var page = products
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToList();

var products = await _context.Products
    .Include(p => p.Category)
    .Include(p => p.Reviews)
    .Where(p => p.Category.Name == category)
    .ToListAsync(cancellationToken);

var page = products
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToList();

This has three compounding problems: it loads the entire category's products into memory before paginating, it eagerly loads full Reviews collections that might contain hundreds of rows per product, and it tracks every entity even though the response is read-only.

Applying Techniques 1, 2, 3, 5, and 6 together:

var page = await _context.Products
    .Where(p => p.Category.Name == category)   // supported by an index on Category.Name
    .OrderBy(p => p.Id)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .Select(p => new ProductListDto
    {
        Id = p.Id,
        Name = p.Name,
        Price = p.Price,
        AverageRating = p.Reviews.Average(r => (double?)r.Rating) ?? 0,
        ReviewCount = p.Reviews.Count
    })
    .AsNoTracking()
    .ToListAsync(cancellationToken);

var page = await _context.Products
    .Where(p => p.Category.Name == category)   // supported by an index on Category.Name
    .OrderBy(p => p.Id)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .Select(p => new ProductListDto
    {
        Id = p.Id,
        Name = p.Name,
        Price = p.Price,
        AverageRating = p.Reviews.Average(r => (double?)r.Rating) ?? 0,
        ReviewCount = p.Reviews.Count
    })
    .AsNoTracking()
    .ToListAsync(cancellationToken);

The database now does the filtering, pagination, and aggregation — EF Core only materializes exactly the rows and columns the endpoint returns. In practice, changes like this on a moderately sized table (tens of thousands of rows) typically cut response times from several hundred milliseconds down to single digits, simply by moving work from application memory into the database engine where it belongs.

Note: Always validate improvements with real data volumes and real query plans. A technique that helps on a 10,000-row table may behave differently at 10 million rows — measure before and after, don’t assume.

EF Core read performance rarely comes down to one silver-bullet fix — it’s the layering of several small, deliberate decisions: the right indexes underneath, no unnecessary tracking, precise projections, controlled includes, sensible pagination, and caching where it earns its keep. Start with indexing and AsNoTracking(), since they apply almost everywhere with minimal risk, then reach for the more targeted techniques (split queries, compiled queries, raw SQL) once profiling shows exactly where your bottleneck actually lives. Measure before and after every change — EF Core’s abstractions are convenient, but the database underneath is where performance is ultimately won or lost.