How do I avoid N+1 when I need related data in a list?

Use Include() and ThenInclude() to eager load the related data in one query, or use projection (Select to a DTO) so that related columns are loaded in a single query. Never loop and access navigation properties without including or projecting.

Article

Database Optimization with Entity Framework Core

Q: How do I find N+1 queries in my EF Core application?

Enable EF Core logging (e.g. LogLevel.Microsoft.EntityFrameworkCore.Database.Command) or use Application Insights with EF Core telemetry to see every SQL statement. Look for repeated queries with the same pattern (e.g. one per order for customer).

Q: When should I use compiled queries?

Use compiled queries for hot paths that run frequently (e.g. get order by ID) and have a fixed shape. Avoid for ad-hoc or highly dynamic queries; the cache is per context type and query shape.

Q: What is the difference between Include and projection?

Include loads full related entities (e.g. Order + Customer + LineItems). Projection (Select to a DTO) loads only the columns you need and often produces a single, efficient query. Prefer projection when you do not need full entities.

Q: How do I choose which indexes to add?

Capture the actual SQL from EF Core logging; run it in SSMS or Azure Data Studio and look at the execution plan. Add indexes for columns used in WHERE, JOIN, and ORDER BY; use covering indexes (INCLUDE) when the query only needs a few columns.

Q: Can EF Core cause memory issues with large result sets?

Yes. Loading millions of rows with ToListAsync() can exhaust memory. Use pagination (Skip/Take), streaming (AsAsyncEnumerable()), or projection to limit data; avoid loading full entity graphs for large sets.

Q: How do I measure EF Core performance in production?

Use Application Insights (or your APM) with EF Core telemetry; add custom metrics for slow queries. Use distributed tracing so that you can correlate slow requests with the SQL they execute.

Q: What is AsNoTracking and when should I use it?

AsNoTracking() tells EF Core not to track loaded entities. Use it for read-only queries (reports, APIs that only return data) to reduce overhead and memory; use tracking only when you need to update and save.

Q: What is the difference between FirstOrDefault and SingleOrDefault?

FirstOrDefault returns the first match (or null); SingleOrDefault returns the single match and throws if more than one. Use FirstOrDefault when you expect at most one without enforcing uniqueness.

Q: When should I use raw SQL with EF Core?

Use raw SQL when EF Core cannot express the query efficiently (complex CTEs, bulk ops, vendor-specific features) or you need full control. Use FromSqlRaw/ExecuteSqlRaw with parameters for plan reuse and security.

EF Core optimization: N+1, projection, batching, and tuning for high-throughput systems.

October 7, 2024 · Waqas Ahmad

Read the article

Introduction

This guidance is relevant when the topic of this article applies to your system or design choices; it breaks down when constraints or context differ. I’ve applied it in real projects and refined the takeaways over time (as of 2026).

Entity Framework Core’s abstraction can introduce N+1 queries, slow LINQ, unnecessary data transfer, and memory pressure when used without care—often making it the bottleneck in high-throughput systems. This article explains how to optimise EF Core: N+1 and eager loading, projection to DTOs, AsNoTracking, compiled queries, batching, pagination, indexing, and raw SQL when LINQ is not enough, with concrete C# and LINQ examples. For architects and tech leads, measuring first and applying these patterns keeps the database layer scalable and predictable.

If you are new to EF Core optimization, start with Topics covered and EF Core optimization at a glance.

For a deeper overview of this topic, explore the full Database Design & Optimization guide.

Topics covered

Decision Context
What is Entity Framework Core and why does tuning matter?
What is the N+1 problem?
What is a compiled query?
EF Core optimization at a glance

Decision Context

System scale: Apps using Entity Framework Core against SQL Server (or compatible); from small to large datasets; applies when you’re tuning queries, reducing N+1, or improving EF-generated SQL.
Team size: Backend or full-stack team; someone must own query patterns and indexing so performance doesn’t regress.
Time / budget pressure: Fits when you have time to profile and fix; breaks down when “we’ll optimize later” and the app is already slow in production.
Technical constraints: EF Core, SQL Server (or Azure SQL, PostgreSQL with EF); assumes you can add indexes, change queries, and use async.
Non-goals: This article does not optimize for raw ADO or non-EF stacks; it optimises for EF Core and related tooling.

What is Entity Framework Core and why does tuning matter?

Entity Framework Core (EF Core) is the object-relational mapper (ORM) for .NET. It lets you work with the database using LINQ and entities instead of raw SQL: you write context.Orders.Where(o => o.CustomerId == id) and EF Core translates that into SQL, runs it, and materialises the results as objects. That abstraction is convenient but it can hide cost. EF Core tracks entities by default (for change detection), loads related data only when you access it (lazy loading, if enabled), and generates SQL that may not match what you would write by hand. In high-throughput systems, small inefficiencies—an N+1 here, a table scan there, or loading full graphs when you only need a few columns—add up. Tuning EF Core is not about abandoning it; it is about using it deliberately: eager loading and projection to control what gets loaded, compiled queries for hot paths, AsNoTracking for read-only scenarios, and aligning indexes with the SQL EF Core produces. This article covers those patterns with maximum code examples so your database layer scales.

What is the N+1 problem?

The N+1 problem occurs when you load a list of entities and then access a navigation property on each: EF Core issues one query for the list and then one query per item for the related data. So for 100 orders you get 1 + 100 = 101 queries. That blows up latency and load. The fix is eager loading with .Include() / .ThenInclude() (load related data in the same query) or projection with .Select() (load only the columns you need in a single query). We show both with code below.

What is a compiled query?

A compiled query is a LINQ expression that EF Core parses and compiles once and reuses for every call. For code paths that run frequently (e.g. get order by ID on every request), that reduces the overhead of parsing and compiling the query tree on each invocation. You define it with EF.CompileQuery or EF.CompileAsyncQuery and call it with the context and parameters. Use compiled queries for hot paths with a fixed shape; avoid for highly dynamic filters that would require many variants.

EF Core optimization at a glance

Technique	What it is	When to use
Eager loading	`.Include()` / `.ThenInclude()` load related entities in one query	When you need full related entities (e.g. Order + Customer) and will use them
Projection	`.Select()` to a DTO or anonymous type; only selected columns are queried	When you only need a subset of columns; often removes N+1 and reduces data transfer
AsNoTracking()	EF Core does not track loaded entities	Read-only queries (APIs, reports); reduces memory and CPU
Compiled query	`EF.CompileAsyncQuery` caches the compiled query	Hot paths (e.g. get by ID) with fixed shape
Batching	`ExecuteUpdate` / `ExecuteDelete` / `AddRange`	Bulk updates, deletes, or inserts with fewer round-trips
Pagination	`.Skip().Take()` before `.ToListAsync()`	Large result sets; only one page is read from the DB
Streaming	`.AsAsyncEnumerable()`	Process large sets without loading all into memory
Raw SQL	`FromSqlRaw` / `ExecuteSqlRaw`	When LINQ cannot express the query efficiently or you need full control
Indexes	Match `WHERE`, `JOIN`, `ORDER BY` columns	After capturing SQL and checking execution plans

Loading diagram…

DbContext and logging: see the SQL

Before optimising, you must see what EF Core sends to the database. Enable logging for executed commands.

// Program.cs or Startup – enable EF Core command logging
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<MyDbContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("Default"));
    // Log every SQL command (disable in production or use level based on config)
    options.LogTo(Console.WriteLine, LogLevel.Information);
    // Or only when debugging: options.EnableSensitiveDataLogging();
});

What this does: LogTo(Console.WriteLine, LogLevel.Information) prints every SQL command and its duration. In production, use Application Insights or your APM with EF Core telemetry so you can correlate slow requests with the SQL they execute. Once you see the SQL, run it in SSMS or Azure Data Studio and check the execution plan to find missing indexes or inefficient shapes.

N+1: bad vs eager loading vs projection

Step 1: Bad – N+1

// BAD: N+1 – one query for orders, then one per order for Customer
// MyApp.Services/OrderService.cs
public async Task<List<Order>> GetOrdersWithCustomerNamesAsync(CancellationToken ct = default)
{
    var orders = await _context.Orders.ToListAsync(ct);
    foreach (var order in orders)
        _ = order.Customer.Name; // Triggers a separate query each time!
    return orders;
}

What goes wrong: ToListAsync runs one SELECT * FROM Orders. Then each order.Customer.Name triggers another SELECT * FROM Customers WHERE Id = @p0. For 100 orders you get 101 queries.

Step 2: Good – eager loading with Include

// GOOD: Eager load – one query with JOIN
public async Task<List<Order>> GetOrdersWithCustomerAsync(CancellationToken ct = default)
{
    return await _context.Orders
        .Include(o => o.Customer)
        .Include(o => o.LineItems)
        .ToListAsync(ct);
}

What this does: EF Core generates a single query (or a few if using split queries) that JOINs Orders, Customers, and LineItems so that all data is loaded in one round-trip. Use only when you need the full Customer and LineItems entities.

Step 3: Better – projection when you only need a few columns

// BETTER: Projection – single query, only the columns you need
public async Task<List<OrderSummaryDto>> GetOrderSummariesAsync(CancellationToken ct = default)
{
    return await _context.Orders
        .Select(o => new OrderSummaryDto
        {
            Id = o.Id,
            OrderDate = o.OrderDate,
            CustomerName = o.Customer.Name,
            Total = o.LineItems.Sum(l => l.Amount)
        })
        .ToListAsync(ct);
}

What this does: EF Core translates the Select into a single SQL query with the needed columns and JOINs/aggregates. No N+1, and less data transferred than loading full entities. Prefer projection when you do not need full entities.

AsNoTracking for read-only queries

By default, EF Core tracks every entity it loads so it can detect changes and persist them on SaveChanges. Tracking costs memory and CPU. For read-only scenarios (e.g. APIs that return data, reports), use AsNoTracking().

// Read-only: no change tracking
public async Task<OrderDto?> GetOrderByIdAsync(int id, CancellationToken ct = default)
{
    return await _context.Orders
        .AsNoTracking()
        .Where(o => o.Id == id)
        .Select(o => new OrderDto
        {
            Id = o.Id,
            CustomerName = o.Customer.Name,
            LineItems = o.LineItems.Select(l => new LineItemDto { ProductName = l.ProductName, Amount = l.Amount }).ToList()
        })
        .FirstOrDefaultAsync(ct);
}

What this does: AsNoTracking() tells EF Core not to put entities in the change tracker. Queries run faster and use less memory. Use tracking only when you need to modify and save the same entities in the same context.

Global default for read-only DbContext (optional):

// DbContext for read-only queries – disable tracking by default
public class ReadOnlyDbContext : DbContext
{
    public ReadOnlyDbContext(DbContextOptions<ReadOnlyDbContext> options) : base(options) { }

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

Include and ThenInclude: eager loading in depth

When you do need full related entities, use Include and ThenInclude so that they are loaded in one (or a few) queries, not N+1.

// One level: Order + Customer
var ordersWithCustomer = await _context.Orders
    .Include(o => o.Customer)
    .ToListAsync(ct);

// Two levels: Order + Customer + Customer.Address
var ordersWithCustomerAndAddress = await _context.Orders
    .Include(o => o.Customer)
        .ThenInclude(c => c.Address)
    .ToListAsync(ct);

// Multiple includes: Order + Customer and Order + LineItems
var ordersFull = await _context.Orders
    .Include(o => o.Customer)
    .Include(o => o.LineItems)
        .ThenInclude(l => l.Product)
    .ToListAsync(ct);

What this does: Each Include / ThenInclude adds related data to the same (or split) query. Do not over-include: only load what you use. If you only need Customer.Name, prefer projection (Select) instead of loading the full Customer entity.

Projection with Select: LINQ to DTOs

Projection means using .Select() to shape the result into a DTO or anonymous type. EF Core translates it into SQL that only selects the columns you need; often a single query with JOINs.

// Project to DTO – single query, no N+1, minimal data
public async Task<List<OrderListDto>> GetOrderListAsync(int customerId, CancellationToken ct = default)
{
    return await _context.Orders
        .AsNoTracking()
        .Where(o => o.CustomerId == customerId)
        .Select(o => new OrderListDto
        {
            Id = o.Id,
            OrderDate = o.OrderDate,
            CustomerName = o.Customer.Name,
            ItemCount = o.LineItems.Count,
            Total = o.LineItems.Sum(l => l.Amount)
        })
        .ToListAsync(ct);
}

What this does: Customer.Name and LineItems.Count / Sum are evaluated in the database; EF Core generates a single SQL query with the appropriate JOINs and aggregates. No N+1, and only the required columns are returned.

Projection with nested DTOs:

return await _context.Orders
    .AsNoTracking()
    .Where(o => o.Id == orderId)
    .Select(o => new OrderDetailDto
    {
        Id = o.Id,
        Customer = new CustomerSummaryDto { Id = o.Customer.Id, Name = o.Customer.Name },
        LineItems = o.LineItems.Select(l => new LineItemDto { ProductName = l.ProductName, Amount = l.Amount }).ToList()
    })
    .FirstOrDefaultAsync(ct);

What this does: Nested Select for Customer and LineItems is translated into a single query (or split query if the provider chooses). You get a DTO graph without loading full entities or N+1.

Compiled queries for hot paths

For code paths that run very frequently (e.g. get order by ID on every request), compiled queries avoid re-parsing and re-compiling the query tree on each call.

// Static field – compiled once per process
private static readonly Func<MyDbContext, int, Task<Order?>> GetOrderByIdAsync =
    EF.CompileAsyncQuery((MyDbContext ctx, int id) =>
        ctx.Orders
            .Include(o => o.Customer)
            .Include(o => o.LineItems)
            .FirstOrDefault(o => o.Id == id));

// Usage
public async Task<Order?> GetOrderAsync(int id, CancellationToken ct = default)
{
    return await GetOrderByIdAsync(_context, id);
}

What this does: EF.CompileAsyncQuery compiles the LINQ expression once; subsequent calls reuse the compiled delegate. Use for hot paths with a fixed shape. Avoid for highly dynamic queries (e.g. many optional filters) because each distinct shape can become a separate compiled query.

Sync compiled query:

private static readonly Func<MyDbContext, int, Order?> GetOrderById =
    EF.CompileQuery((MyDbContext ctx, int id) =>
        ctx.Orders
            .AsNoTracking()
            .Include(o => o.Customer)
            .FirstOrDefault(o => o.Id == id));

Batching: ExecuteUpdate, ExecuteDelete, AddRange

Bulk update and delete (EF Core 7+) reduce round-trips by executing a single statement instead of one per entity.

// Bulk update – one UPDATE statement
await _context.Orders
    .Where(o => o.Status == OrderStatus.Pending && o.CreatedAt < cutoff)
    .ExecuteUpdateAsync(s => s.SetProperty(o => o.Status, OrderStatus.Expired), ct);

// Bulk delete – one DELETE statement
await _context.Orders
    .Where(o => o.Status == OrderStatus.Cancelled)
    .ExecuteDeleteAsync(ct);

What this does: ExecuteUpdateAsync and ExecuteDeleteAsync generate a single UPDATE or DELETE with a WHERE clause; no need to load entities into memory. Use when you are updating or deleting many rows by condition.

Bulk insert with AddRange:

// Batch insert – more efficient than Add() in a loop
var newOrders = new List<Order>();
for (int i = 0; i < 1000; i++)
    newOrders.Add(new Order { ... });

_context.Orders.AddRange(newOrders);
await _context.SaveChangesAsync(ct);

What this does: AddRange attaches all entities in one go; SaveChangesAsync can batch the inserts (depending on the provider). For very large inserts, consider bulk copy (e.g. SqlBulkCopy) or raw SQL.

Pagination and streaming

Pagination: Use .Skip().Take() before .ToListAsync() so that only one page is read from the database.

public async Task<List<OrderListDto>> GetOrdersPageAsync(int page, int pageSize, CancellationToken ct = default)
{
    return await _context.Orders
        .AsNoTracking()
        .OrderBy(o => o.OrderDate)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .Select(o => new OrderListDto { Id = o.Id, OrderDate = o.OrderDate, CustomerName = o.Customer.Name })
        .ToListAsync(ct);
}

What this does: EF Core translates Skip and Take into OFFSET ... FETCH (or equivalent) so the database returns only one page. Never load the full set and then skip/take in memory.

Streaming for very large result sets:

await foreach (var dto in _context.Orders
    .AsNoTracking()
    .Select(o => new OrderListDto { Id = o.Id, CustomerName = o.Customer.Name })
    .AsAsyncEnumerable())
{
    await ProcessAsync(dto, ct);
}

What this does: AsAsyncEnumerable() streams rows instead of loading all into memory. Use when you need to process a large set without holding it all in memory.

Raw SQL when LINQ is not enough

When LINQ cannot express the query efficiently (e.g. complex CTEs, vendor-specific features) or you need full control, use raw SQL with parameters so that plans are reused and injection is avoided.

// Query with raw SQL – use parameters!
var customerId = 42;
var orders = await _context.Orders
    .FromSqlRaw(
        "SELECT * FROM Orders WHERE CustomerId = {0} AND Status = {1}",
        customerId,
        (int)OrderStatus.Active)
    .AsNoTracking()
    .ToListAsync(ct);

What this does: FromSqlRaw runs the given SQL; {0} and {1} are replaced by parameters so the plan is reused and the query is safe. Prefer LINQ when it produces acceptable SQL; use raw SQL when profiling shows a need.

Execute raw (non-query):

await _context.Database.ExecuteSqlRawAsync(
    "UPDATE Orders SET Status = {0} WHERE CreatedAt < {1}",
    (int)OrderStatus.Expired,
    cutoff,
    ct);

Indexing and query shape

Indexes should match your query predicates and sort columns. Use EF Core logging to capture the SQL, then run it in SSMS or Azure Data Studio and look at the execution plan. Add indexes for columns used in WHERE, JOIN, and ORDER BY. Avoid indexing every column; focus on slow or frequent queries.

Index in EF Core model (fluent):

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .HasIndex(o => o.CustomerId);
    modelBuilder.Entity<Order>()
        .HasIndex(o => new { o.Status, o.CreatedAt });
}

What this does: HasIndex creates an index when you generate a migration. For covering indexes (INCLUDE columns), use raw SQL in a migration or provider-specific APIs. Align indexes with the actual SQL EF Core produces after you apply the patterns above.

Split queries for large graphs

When you Include many or large collections, EF Core may generate a single query with many JOINs that returns duplicate rows (cartesian explosion). Use AsSplitQuery() so that EF Core runs multiple queries (one for the main entity, one per collection) and composes the result in memory.

var order = await _context.Orders
    .Include(o => o.Customer)
    .Include(o => o.LineItems)
        .ThenInclude(l => l.Product)
    .AsSplitQuery()
    .FirstOrDefaultAsync(o => o.Id == orderId, ct);

What this does: Instead of one big JOIN, EF Core runs e.g. one query for Order+Customer and one for LineItems+Product, then merges the results. Use when a single query would return too many duplicated rows. Default in EF Core 5+ for multiple Include of collections can be configured globally; use AsSplitQuery() when needed.

Common issues and challenges

N+1 in production: The most common EF Core performance pitfall. Always measure (logging, profiler) before and after; use eager loading and projection so that related data is loaded in one query or not loaded at all when not needed.

Over-including: .Include() loads entire related entities. If you only need a few columns, use projection (.Select()) instead; it reduces data transfer and often produces a single, efficient query.

Compiled query misuse: Compiled queries are cached per context type; avoid dynamic filters that would require many variants. Use them for hot paths (e.g. get by ID) and keep them simple.

Index bloat: Adding too many indexes slows writes and increases storage. Add indexes based on actual query plans and usage; remove unused indexes.

Ignoring execution plans: Without profiling, you cannot know if the engine is doing index seeks or table scans. Use EF Core logging or SQL Profiler to capture SQL and analyse plans; fix the worst offenders first.

Loading huge result sets: .ToListAsync() on a query that returns millions of rows can exhaust memory. Use pagination (.Skip().Take()), streaming (AsAsyncEnumerable()), or projection to limit data.

Best practices and pitfalls

Do:

Measure first: enable EF Core logging or APM; capture SQL and execution plans.
Use AsNoTracking() for read-only queries.
Prefer projection (.Select() to DTO) when you do not need full entities.
Use Include / ThenInclude only when you need full related entities; otherwise project.
Use compiled queries for hot paths with fixed shape.
Use Skip().Take() for pagination; never load all and slice in memory.
Use ExecuteUpdate / ExecuteDelete for bulk updates/deletes (EF Core 7+).
Use parameters in raw SQL (FromSqlRaw with {0}, {1}, or FromSqlInterpolated carefully).
Align indexes with the SQL your queries produce.

Don’t:

Don’t loop and access navigation properties without having included or projected them (N+1).
Don’t over-include; load only what you use.
Don’t ignore change tracking cost for read-only paths; use AsNoTracking().
Don’t load millions of rows with ToListAsync() without pagination or streaming.
Don’t use raw SQL with string concatenation (injection risk); use parameters.
Don’t add indexes without checking execution plans; too many indexes hurt writes.

Summary

EF Core optimisation starts with measuring: use logging and profiling to see the SQL, then fix N+1 with Include or projection, use AsNoTracking for read-only paths, and add compiled queries and batching where it matters. Skipping measurement leads to wrong fixes and wasted effort; applying these patterns keeps the database layer scalable. Next, enable EF Core logging or SQL profiling on a high-traffic path, identify the top slow or N+1 queries, then apply projection or AsNoTracking and re-measure.

EF Core is a powerful ORM; use logging and profiling to see the SQL and then optimise.
N+1: fix with eager loading (.Include() / .ThenInclude()) or projection (.Select() to DTO).
AsNoTracking() for read-only queries to reduce memory and CPU.
Projection (.Select()) often gives a single, efficient query and less data transfer than loading full entities.
Compiled queries for hot paths (e.g. get by ID) with fixed shape.
Batching: ExecuteUpdate / ExecuteDelete / AddRange for bulk operations.
Pagination (.Skip().Take()) and streaming (AsAsyncEnumerable()) for large result sets.
Raw SQL when LINQ is not enough; always use parameters.
Indexes should match WHERE, JOIN, ORDER BY; add based on execution plans. Use the FAQs below as a quick reference when tuning EF Core.

Position & Rationale

I log and profile EF Core SQL first—I don’t add indexes or rewrite queries without seeing the actual statements and plans. I fix N+1 with eager loading (.Include() / .ThenInclude()) or projection (.Select() to DTO); I prefer projection when the client needs a subset of fields so we get one efficient query and less data. I use AsNoTracking() for read-only queries to reduce memory and CPU. I use compiled queries for hot paths (e.g. get by ID) with fixed shape. I use ExecuteUpdate / ExecuteDelete / AddRange for bulk operations instead of loading and saving one by one. I paginate (.Skip().Take()) or stream (AsAsyncEnumerable()) for large result sets. I add indexes only after checking execution plans; I avoid over-indexing so writes don’t suffer. I reject optimising without measuring; I reject loading full entities when a projection would do.

Trade-Offs & Failure Modes

What this sacrifices: Eager loading can over-fetch if we Include too much; compiled queries are less flexible. Raw SQL bypasses LINQ and needs manual maintenance.
Where it degrades: When we don’t profile—we guess and add indexes that don’t help or hurt writes. When we use tracking for read-only—wasted memory. When we load full entities for lists—N+1 or huge payloads.
How it fails when misapplied: Adding indexes without execution plans. Using tracking for every query. Fixing N+1 with Include when projection would be simpler and faster. Skipping pagination on large result sets so the app runs out of memory.
Early warning signs: “The query is slow but we’re not sure which”; “we have 50 Includes”; “the API returns 10MB for a list”; “writes got slow after we added indexes.”

What Most Guides Miss

Guides often show one technique (e.g. Include) and skip when to use projection vs Include—projection (.Select() to DTO) often gives one query and less data than loading full entities and mapping. AsNoTracking() for read-only is underplayed; tracking adds overhead we don’t need when we’re not updating. Compiled queries for hot paths (e.g. get by ID) are rarely shown; they avoid repeated plan compilation. Batching (ExecuteUpdate, ExecuteDelete, AddRange) vs one-by-one is a big win that many tutorials don’t stress. The discipline of measure first (log SQL, check plans) before adding indexes or rewriting is also underplayed—we should never guess.

Decision Framework

If a query is slow → Enable EF logging or use a profiler; check for N+1, missing indexes, or over-fetching; fix with Include, projection, or index as appropriate.
If the query is read-only → Use AsNoTracking(); use projection if the client needs a subset of fields.
If you have a hot path with fixed shape → Use compiled queries.
If you’re doing bulk updates or deletes → Use ExecuteUpdate / ExecuteDelete; for bulk insert use AddRange and SaveChanges in batches.
If the result set is large → Paginate (Skip/Take) or stream (AsAsyncEnumerable); don’t load everything into memory.
If you need an index → Check the execution plan first; add only what the plan suggests; avoid over-indexing.

You can also explore more patterns in the Database Design & Optimization resource page.

Key Takeaways

Measure first (log SQL, execution plans); fix N+1 with Include or projection; use AsNoTracking for read-only; use projection when the client needs a subset.
Use compiled queries for hot paths; use ExecuteUpdate/ExecuteDelete/AddRange for bulk; paginate or stream large result sets.
Add indexes based on plans; don’t over-index. Revisit when query shapes or data volume change.

For enterprise applications, I offer consulting on database architecture, performance tuning, and EF Core optimization.

When I Would Use This Again — and When I Wouldn’t

I would use these optimisations again when I’m tuning EF Core apps that hit SQL Server (or compatible) and see slow queries, N+1, or high memory. I wouldn’t optimise without profiling first—assumptions (e.g. “Include is always bad”) can be wrong. I also wouldn’t skip indexes and then add raw SQL everywhere; fix the data model and indexes first. Alternative: for small datasets or low traffic, default EF behaviour may be fine; add AsNoTracking and projection when you scale or profile shows need.

Frequently Asked Questions

How do I find N+1 queries in my EF Core application?

Enable EF Core logging (e.g. LogLevel.Microsoft.EntityFrameworkCore.Database.Command) or use Application Insights with EF Core telemetry to see every SQL statement. Look for repeated queries with the same pattern (e.g. one per order for customer).

When should I use compiled queries?

Use compiled queries for hot paths that run frequently (e.g. get order by ID) and have a fixed shape. Avoid for ad-hoc or highly dynamic queries; the cache is per context type and query shape.

What is the difference between Include and projection?

.Include() loads full related entities (e.g. Order + Customer + LineItems). Projection (.Select() to a DTO) loads only the columns you need and often produces a single, efficient query. Prefer projection when you do not need full entities.

How do I choose which indexes to add?

Capture the actual SQL from EF Core logging; run it in SSMS or Azure Data Studio and look at the execution plan. Add indexes for columns used in WHERE, JOIN, and ORDER BY; use covering indexes (INCLUDE) when the query only needs a few columns.

Can EF Core cause memory issues with large result sets?

Yes. Loading millions of rows with .ToListAsync() can exhaust memory. Use pagination (.Skip().Take()), streaming (AsAsyncEnumerable()), or projection to limit data; avoid loading full entity graphs for large sets.

How do I measure EF Core performance in production?

Use Application Insights (or your APM) with EF Core telemetry; add custom metrics for slow queries. Use distributed tracing so that you can correlate slow requests with the SQL they execute.

What is AsNoTracking and when should I use it?

AsNoTracking() tells EF Core not to track the entities it loads. For read-only queries (e.g. reports, APIs that only return data), tracking adds overhead and memory; use AsNoTracking() so that EF Core only materialises the data. Use tracking only when you need to update and save the same entities.

Use .Include() and .ThenInclude() to eager load the related data in one query, or use projection (.Select() to a DTO) so that the related columns are loaded in a single query. Never loop and access a navigation property for each item without having included or projected it.

What is the difference between FirstOrDefault and SingleOrDefault?

FirstOrDefault returns the first match (or null); SingleOrDefault returns the single match (or null) and throws if there is more than one. Use FirstOrDefault when you expect at most one but do not want to pay for a uniqueness check; use SingleOrDefault when you want to enforce “exactly one” and catch data errors.

When should I use raw SQL with EF Core?

Use raw SQL when EF Core cannot express the query efficiently (e.g. complex CTEs, bulk operations, vendor-specific features) or when you need full control. Use FromSqlRaw or ExecuteSqlRaw with parameters so that plans are reused and injection is avoided. Prefer LINQ when it produces acceptable SQL; use raw SQL when profiling shows a need.

How do I paginate with EF Core without loading everything?

Use .Skip((page - 1) * pageSize).Take(pageSize) before .ToListAsync(). EF Core translates that into OFFSET ... FETCH (or equivalent) so that only one page of rows is read from the database. Never load the full set and then skip/take in memory.

What is change tracking and when does it hurt performance?

Change tracking is EF Core’s mechanism for detecting updates and deletes. It adds memory and CPU for every entity you load. For read-only scenarios, use AsNoTracking() so that EF Core does not track. Use tracking only when you need to modify and save entities in the same context.

How do I use EF Core with Azure SQL and connection resilience?

Use connection resilience (e.g. EnableRetryOnFailure) in your DbContext configuration so that transient failures (e.g. throttling) are retried. Use Managed Identity or a connection string from Key Vault so that credentials are not in config. Align connection timeout and command timeout with your SLA.

What is the difference between Add and Update in EF Core?

Add marks an entity as new (insert); Update marks an entity as modified (update). For existing entities loaded from the database, change the properties and save; EF Core detects changes if tracking is enabled. For new entities, use Add. Avoid Update on entities that were not loaded from the same context unless you are doing a disconnected update with the correct state.

How do I run migrations in production safely?

Run migrations as part of your deployment pipeline (e.g. dotnet ef database update or a script step). Prefer backward-compatible migrations (e.g. add column as nullable first, backfill, then add constraint) so that old and new app versions can run during deployment. Test migrations on a copy of production data before applying to production.

What is ExecuteUpdate and ExecuteDelete?

ExecuteUpdateAsync and ExecuteDeleteAsync (EF Core 7+) execute a single UPDATE or DELETE statement with a WHERE clause without loading entities into memory. Use them for bulk updates or deletes by condition instead of loading, modifying, and saving each entity.

When should I use AsSplitQuery?

Use AsSplitQuery() when you Include multiple collections or large collections and a single query would produce too many duplicate rows (cartesian explosion). EF Core then runs multiple queries (e.g. one for main entity, one per collection) and composes the result. Use when the default single-query behaviour is slow or returns too much data.

How do I project to a DTO with nested collections in LINQ?

Use a nested .Select() inside the main .Select(): e.g. Select(o => new OrderDto { LineItems = o.LineItems.Select(l => new LineItemDto { ... }).ToList() }). EF Core translates it into a single query (or split query) that loads only the needed columns and shapes the DTO in memory.

Related Guides & Resources

Part of cluster

Full-Stack & Data — explore related topics:

Full Stack Development Database Design & Optimization

Waqas Ahmad — Software Architect & Technical Consultant

Distributed Systems

Article

Database Optimization with Entity Framework Core

Read the article

Introduction

Topics covered

Decision Context

What is Entity Framework Core and why does tuning matter?

What is the N+1 problem?

What is a compiled query?

EF Core optimization at a glance

DbContext and logging: see the SQL

N+1: bad vs eager loading vs projection

AsNoTracking for read-only queries

Include and ThenInclude: eager loading in depth

Projection with Select: LINQ to DTOs

Compiled queries for hot paths

Batching: ExecuteUpdate, ExecuteDelete, AddRange

Pagination and streaming

Raw SQL when LINQ is not enough

Indexing and query shape

Split queries for large graphs

Common issues and challenges

Best practices and pitfalls

Summary

Position & Rationale

Trade-Offs & Failure Modes

What Most Guides Miss

Decision Framework

Key Takeaways

When I Would Use This Again — and When I Wouldn’t

Frequently Asked Questions

Frequently Asked Questions

How do I find N+1 queries in my EF Core application?

When should I use compiled queries?

What is the difference between Include and projection?

How do I choose which indexes to add?

Can EF Core cause memory issues with large result sets?

How do I measure EF Core performance in production?

What is AsNoTracking and when should I use it?

How do I avoid N+1 when I need related data in a list?

What is the difference between FirstOrDefault and SingleOrDefault?

When should I use raw SQL with EF Core?

How do I paginate with EF Core without loading everything?

What is change tracking and when does it hurt performance?

How do I use EF Core with Azure SQL and connection resilience?

What is the difference between Add and Update in EF Core?

How do I run migrations in production safely?

What is ExecuteUpdate and ExecuteDelete?

When should I use AsSplitQuery?

How do I project to a DTO with nested collections in LINQ?

Related Guides & Resources

Related articles

Part of cluster

Related services