Entity Framework Core Performance: Optimizing Queries Without Compromise

EF Core makes database access simple. At the same time, it's easy to accidentally write slow queries. What runs fine locally with a handful of records crashes in production with a million rows. Change tracking eating unnecessary memory. Queries hitting the database again for each item in a list.

This is about optimizations that matter — not micro-optimizations that are measurable but negligible, but adjustments that take an API from 800ms to 40ms.

The N+1 Problem: The Classic Pitfall

A query fetches a list. For each item in that list, a separate query is executed to fetch related data. With 100 items: 101 database roundtrips.

// ❌ N+1 query: 1 query for orders + N queries for customers
var orders = await _db.Orders.Take(100).ToListAsync();
foreach (var order in orders)
{
    // This triggers a separate query per order
    Console.WriteLine($"{order.Id}: {order.Customer.Name}");
}

EF Core just logs this without warning. The database sees hundreds of identical queries pass by, each with a different parameter. Connection pooling helps, but it's still overhead.

The fix: eager loading with Include:

// ✅ 1 query via JOIN
var orders = await _db.Orders
    .Include(o => o.Customer)
    .Take(100)
    .ToListAsync();

foreach (var order in orders)
{
    Console.WriteLine($"{order.Id}: {order.Customer.Name}");
}

A single query with a JOIN fetches everything in one go. The difference is dramatic with datasets of any size.

Projection: Only Fetch What's Needed

Change tracking is useful for updates, but in read-only scenarios it's pure overhead. EF Core tracks which properties have changed, even if there's no .SaveChanges() following.

A typical example: an API endpoint returning a list.

// ❌ Full entities with tracking
var products = await _db.Products
    .Include(p => p.Category)
    .ToListAsync();

return products.Select(p => new ProductDto
{
    Id = p.Id,
    Name = p.Name,
    CategoryName = p.Category.Name
});

This fetches all columns, creates full entity objects, enables change tracking, and then manually maps to a DTO anyway. Three unnecessary steps.

Better: project to the desired type in the query itself.

// ✅ Only the needed columns, no tracking
var products = await _db.Products
    .Select(p => new ProductDto
    {
        Id = p.Id,
        Name = p.Name,
        CategoryName = p.Category.Name
    })
    .ToListAsync();

return products;

EF Core translates this to a SQL query fetching exactly those three columns. No overhead, no unnecessary data, no tracking.

With large datasets this saves hundreds of megabytes of memory and seconds of query time.

AsNoTracking: Explicitly Disable Tracking

If projection isn't possible — for example with more complex object graphs that still need to return as entities — AsNoTracking() helps:

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

Change tracking stays off. Entities are read-only, but the object structure remains intact. Saves significant memory with large result sets.

From EF Core 6 onward, tracking can also be disabled globally:

builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString);
    options.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
});

Then tracking is off by default and must be explicitly enabled where needed. Useful for read-heavy applications.

Compiled Queries: One-Time Query Parsing

With frequently-used queries, EF Core pays parsing and expression tree compilation every time. Compiled queries cache that:

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

public async Task<Product?> GetProductAsync(int id)
{
    return await _getProductById(_db, id);
}

It saves a few microseconds per call. At thousands of requests per second that adds up. For queries that rarely run it's overkill.

SplitQuery: Split Large Joins

With multiple Include statements, EF Core generates one large JOIN query by default. That works excellently for simple relationships, but with one-to-many collections a cartesian product explosion occurs.

Example: an order with 10 items and 5 shipments. The JOIN results in 50 rows that EF Core must merge into one object.

// ❌ Cartesian explosion with large collections
var order = await _db.Orders
    .Include(o => o.Items)
    .Include(o => o.Shipments)
    .FirstAsync(o => o.Id == orderId);

With AsSplitQuery() separate queries are executed:

// ✅ 3 separate queries: orders, items, shipments
var order = await _db.Orders
    .Include(o => o.Items)
    .Include(o => o.Shipments)
    .AsSplitQuery()
    .FirstAsync(o => o.Id == orderId);

More roundtrips, but each query is much smaller. With large collections this is net faster.

Batch Updates and Deletes

Until recently, deleting 1000 rows required first querying to fetch them, then a loop with Remove(), and then SaveChanges() executing 1000 DELETE statements.

EF Core 7 introduces bulk operations:

// ❌ Old: fetch everything and delete one by one
var oldOrders = await _db.Orders
    .Where(o => o.CreatedAt < DateTime.UtcNow.AddYears(-1))
    .ToListAsync();
_db.Orders.RemoveRange(oldOrders);
await _db.SaveChangesAsync();

// ✅ New: single DELETE statement
await _db.Orders
    .Where(o => o.CreatedAt < DateTime.UtcNow.AddYears(-1))
    .ExecuteDeleteAsync();

Same goes for updates:

await _db.Products
    .Where(p => p.Stock == 0)
    .ExecuteUpdateAsync(p => p.SetProperty(x => x.IsAvailable, false));

Translates to one UPDATE statement. No loading entities, no change tracking, straight to the database.

Index Hints and Raw SQL When Needed

EF Core generates good SQL, but sometimes the query planner knows better. Index hints or specific query constructs that EF Core doesn't support can be manual:

var products = await _db.Products
    .FromSqlRaw(@"
        SELECT * FROM Products WITH (INDEX(IX_Products_Category))
        WHERE CategoryId = {0}
    ", categoryId)
    .ToListAsync();

Or call a stored procedure:

var stats = await _db.OrderStats
    .FromSqlRaw("EXEC GetOrderStatistics @StartDate, @EndDate",
        new SqlParameter("@StartDate", startDate),
        new SqlParameter("@EndDate", endDate))
    .ToListAsync();

Remains type-safe and still benefits from EF Core's mapping, but with full control over the SQL.

Query Filters for Soft Deletes

Applications with soft deletes — an IsDeleted flag instead of actual deletions — must add Where(x => !x.IsDeleted) everywhere. Forget it once, and deleted records suddenly appear in production.

Global query filters prevent that:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>()
        .HasQueryFilter(p => !p.IsDeleted);
}

Every query on Products automatically gets that filter. Want to see deleted items anyway:

var allProducts = await _db.Products
    .IgnoreQueryFilters()
    .ToListAsync();

Also works for multi-tenancy: filter on TenantId and every query is automatically scoped.

Optimization	Impact	When to use
Include/Eager Loading	High	Any N+1 situation
Projection (Select)	High	Read-only queries, API endpoints
AsNoTracking	Medium	Large result sets, no updates
Compiled Queries	Low	Hot paths, very frequent queries
SplitQuery	Situational	Multiple large collections
ExecuteUpdate/Delete	High	Bulk operations

Monitoring and Diagnostics

Without measuring, optimizing is guessing. EF Core has built-in logging:

builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString);
    options.LogTo(Console.WriteLine, LogLevel.Information);
    options.EnableSensitiveDataLogging(); // Only in development
    options.EnableDetailedErrors();
});

This shows every executed query. In production that's too much, but for troubleshooting it's gold.

For continuous monitoring: MiniProfiler integrates seamlessly with EF Core and shows query timings per request.

dotnet add package MiniProfiler.AspNetCore
dotnet add package MiniProfiler.EntityFrameworkCore

Registration in Program.cs:

builder.Services.AddMiniProfiler(options =>
{
    options.RouteBasePath = "/profiler";
}).AddEntityFramework();

Navigate to /profiler/results and see exactly which queries are slow, including call stacks.

Conclusion

EF Core performance is about being aware of what's happening. Every .Include() is a JOIN. Every entity object coming out of a query has tracking overhead unless stated otherwise. And lazy loading — off by default since EF Core 3.0 — can still be turned on, but then the N+1 party starts again.

The tools exist. Projection, compiled queries, bulk updates, query filters. It's a matter of applying them where needed. Not everything needs to be perfect — just the hot paths where thousands of requests flow through.