Most ASP.NET Core teams hit a performance wall around the same place: the database layer. Your API responds fine in development. Then latency creeps up in production. Requests that took 50ms now take 500ms. Throughput tanks. The instinct is to blame Entity Framework Core, but the real culprit is usually how it’s being used.
The good news: strategic optimization at the query, caching, and indexing layers can cut API latency by 60% and triple throughput without abandoning the ORM or rewriting your codebase. This guide walks through the patterns that actually work at scale.
The N+1 Query Problem: Your First Bottleneck
N+1 queries are the most common performance leak in EF Core applications. You run one query to fetch a list of orders, then inside a loop you fetch the customer for each order. One query becomes 101 queries.
// Bad: N+1 queries
var orders = dbContext.Orders.ToList();
var orderDtos = orders.Select(o => new OrderDto
{
OrderId = o.OrderId,
OrderDate = o.OrderDate,
CustomerName = o.Customer.Name // Triggers a query per order
}).ToList();
The fix is eager loading with Include. Load related data in a single roundtrip:
// Good: Single query with joins
var orders = dbContext.Orders
.Include(o => o.Customer)
.ToList();
var orderDtos = orders.Select(o => new OrderDto
{
OrderId = o.OrderId,
OrderDate = o.OrderDate,
CustomerName = o.Customer.Name
}).ToList();
For nested relationships, chain Include calls or use ThenInclude:
// Nested eager loading
var orders = dbContext.Orders
.Include(o => o.Customer)
.Include(o => o.OrderItems)
.ThenInclude(oi => oi.Product)
.ToList();
This single query returns all related data. In production, this change alone often cuts API latency by 40% to 50%.
Projection: Fetch Only What You Need
Eager loading loads entire entities. If your API only needs a few fields, you’re transferring unnecessary data from the database to memory, then serializing it over HTTP. Projection solves this by selecting only the columns you need at the query level:
// Bad: Load full entities, then map
var orders = dbContext.Orders
.Include(o => o.Customer)
.ToList()
.Select(o => new OrderSummaryDto
{
OrderId = o.OrderId,
OrderDate = o.OrderDate,
CustomerName = o.Customer.Name
})
.ToList();
// Good: Project in the query
var orders = dbContext.Orders
.Select(o => new OrderSummaryDto
{
OrderId = o.OrderId,
OrderDate = o.OrderDate,
CustomerName = o.Customer.Name
})
.ToList();
The second version generates a query that selects only the three columns needed. The database returns less data, the network transfer is smaller, and no unmapped entity data clutters memory. For APIs serving thousands of requests per second, this compounds into significant savings.
Compiled Queries: Reuse Query Plans
Every time EF Core builds a query, it parses the LINQ expression, generates SQL, and the database compiles a query plan. If you run similar queries thousands of times per second, you’re re-compiling the same plan repeatedly.
Compiled queries cache the query plan. EF Core builds the plan once, then reuses it:
// Define the compiled query once
private static readonly Func<ApplicationDbContext, int, Task<Order>> GetOrderById =
EF.CompileAsyncQuery((ApplicationDbContext ctx, int orderId) =>
ctx.Orders
.Include(o => o.Customer)
.Include(o => o.OrderItems)
.FirstOrDefault(o => o.OrderId == orderId)
);
// Use it repeatedly
public async Task<OrderDto> GetOrder(int orderId)
{
var order = await GetOrderById(dbContext, orderId);
return MapToDto(order);
}
Compiled queries are especially valuable for frequently-used queries in high-throughput APIs. Benchmarks show 10% to 20% latency reduction on repeated queries, with higher gains if the query is complex or run thousands of times per second.
Distributed Caching: Reduce Database Load
Even optimized queries have latency. If the same data is requested repeatedly, cache it. Distributed caching (like Azure Cache for Redis) keeps hot data in memory outside the database, accessible across multiple API instances.
A simple caching pattern:
public async Task<CustomerDto> GetCustomer(int customerId)
{
var cacheKey = $"customer_{customerId}";
// Try cache first
var cached = await distributedCache.GetStringAsync(cacheKey);
if (cached != null)
{
return JsonSerializer.Deserialize<CustomerDto>(cached);
}
// Cache miss: query database
var customer = await dbContext.Customers.FindAsync(customerId);
var dto = MapToDto(customer);
// Store in cache for 5 minutes
await distributedCache.SetStringAsync(
cacheKey,
JsonSerializer.Serialize(dto),
new DistributedCacheEntryOptions
{
AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(5)
}
);
return dto;
}
For frequently accessed data with a reasonable TTL (time-to-live), this cuts database load dramatically. In production, a 5-minute cache on customer data can reduce database queries by 95% or more, depending on access patterns.
Cache invalidation is the hard part. When a customer record is updated, invalidate the cache:
public async Task UpdateCustomer(int customerId, UpdateCustomerRequest request)
{
var customer = await dbContext.Customers.FindAsync(customerId);
customer.Name = request.Name;
customer.Email = request.Email;
await dbContext.SaveChangesAsync();
// Invalidate cache
var cacheKey = $"customer_{customerId}";
await distributedCache.RemoveAsync(cacheKey);
}
Strategic Indexing: Let the Database Work Smart
Indexes speed up queries, but poorly chosen indexes waste storage and slow down writes. Strategy matters.
Start with the most-queried columns. If your API frequently filters by CustomerId, index it:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Order>()
.HasIndex(o => o.CustomerId);
}
For queries that filter on multiple columns, use composite indexes:
modelBuilder.Entity<Order>()
.HasIndex(o => new { o.CustomerId, o.OrderDate })
.IsDescending(false, true); // Descending on OrderDate for recent-first queries
For queries that need only index columns (no table lookup), use included columns to make the index covering:
modelBuilder.Entity<Order>()
.HasIndex(o => o.CustomerId)
.IncludeProperties(o => o.OrderDate, o => o.TotalAmount);
A covering index means the database can answer the query from the index alone, without reading the table. For high-throughput read-heavy APIs, this is a major win.
Monitor index usage in production. SQL Server and PostgreSQL provide query execution plans and index statistics. Remove unused indexes; they only slow down writes.
Putting It Together: A Real-World Example
A typical e-commerce API endpoint needs to list recent orders for a customer with order items and product details. Without optimization:
// Unoptimized: N+1 queries, full entities, no caching
public async Task<List<OrderDto>> GetCustomerOrders(int customerId)
{
var orders = await dbContext.Orders
.Where(o => o.CustomerId == customerId)
.OrderByDescending(o => o.OrderDate)
.Take(50)
.ToListAsync();
var orderDtos = new List<OrderDto>();
foreach (var order in orders)
{
var items = await dbContext.OrderItems
.Where(oi => oi.OrderId == order.OrderId)
.ToListAsync();
var itemDtos = items.Select(oi => new OrderItemDto
{
ProductName = oi.Product.Name, // Another query per item
Quantity = oi.Quantity,
Price = oi.Price
}).ToList();
orderDtos.Add(new OrderDto
{
OrderId = order.OrderId,
OrderDate = order.OrderDate,
Items = itemDtos
});
}
return orderDtos;
}
For 50 orders with an average of 5 items each, this runs roughly 251 queries. Response time: 5-10 seconds.
Optimized version:
// Optimized: Eager load, projection, caching, compiled query
private static readonly Func<ApplicationDbContext, int, Task<List<OrderDto>>> GetOrdersCompiled =
EF.CompileAsyncQuery((ApplicationDbContext ctx, int customerId) =>
ctx.Orders
.Where(o => o.CustomerId == customerId)
.OrderByDescending(o => o.OrderDate)
.Take(50)
.Select(o => new OrderDto
{
OrderId = o.OrderId,
OrderDate = o.OrderDate,
Items = o.OrderItems.Select(oi => new OrderItemDto
{
ProductName = oi.Product.Name,
Quantity = oi.Quantity,
Price = oi.Price
}).ToList()
})
.ToList()
);
public async Task<List<OrderDto>> GetCustomerOrders(int customerId)
{
var cacheKey = $"customer_orders_{customerId}";
var cached = await distributedCache.GetStringAsync(cacheKey);
if (cached != null)
{
return JsonSerializer.Deserialize<List<OrderDto>>(cached);
}
var orders = await GetOrdersCompiled(dbContext, customerId);
await distributedCache.SetStringAsync(
cacheKey,
JsonSerializer.Serialize(orders),
new DistributedCacheEntryOptions
{
AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(2)
}
);
return orders;
}
// Indexes
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Order>()
.HasIndex(o => new { o.CustomerId, o.OrderDate })
.IsDescending(false, true);
}
This version runs one query, projects only needed columns, caches the result, and uses a composite index. Response time: 20-50ms on cache hit, 100-200ms on cache miss. For 1,000 concurrent users, throughput can reach 300+ requests per second, compared to 10 requests per second without optimization.
Monitoring and Benchmarking
Optimization is data-driven. Use Application Insights or similar tools to track query count and latency in production. Set up alerts for N+1 query patterns. Use EF Core’s logging to see generated SQL:
services.AddDbContext<ApplicationDbContext>(options =>
options.UseSqlServer(connectionString)
.LogTo(Console.WriteLine, LogLevel.Information)
);
Before and after benchmarks validate improvements. A simple stopwatch in development isn’t enough; measure under production-like load using tools like k6 or Apache JMeter.
When to Reach for Raw SQL
Optimized EF Core handles most workloads. But some scenarios benefit from raw SQL: complex aggregations, bulk operations, or queries that EF Core translates inefficiently. Use FromSql for these, keeping the rest of your codebase in EF Core:
var topCustomers = await dbContext.Orders
.FromSql($"SELECT * FROM Orders WHERE CustomerId IN (SELECT TOP 10 CustomerId FROM Orders GROUP BY CustomerId ORDER BY COUNT(*) DESC)")
.ToListAsync();
This is a tactical choice, not a wholesale replacement.
Key Takeaways
Entity Framework Core isn’t slow. It’s often used without considering its performance implications. The optimization stack is straightforward:
- Eliminate N+1 queries with eager loading and projections.
- Compile frequently-used queries to reuse query plans.
- Cache hot data in distributed memory to reduce database load.
- Index strategically based on actual query patterns, not guesswork.
- Monitor and measure in production, not just development.
Apply these patterns consistently, and you’ll build APIs that scale to thousands of requests per second without abandoning the productivity and safety of Entity Framework Core. The database layer stops being a bottleneck and becomes a strength.
What is the difference between Include and projection in EF Core?
Include loads full related entities into memory. Projection (Select) retrieves only specific columns from the database. For APIs, projection is usually better: it reduces data transfer, memory use, and serialization time. Use Include when you need the full entity; use projection when you only need a few fields.
How do I know if I have an N+1 query problem?
Enable EF Core logging or use a profiler like MiniProfiler to see SQL queries. If one logical operation triggers many queries (for example, fetching 50 orders triggers 50+ additional queries for related data), you have N+1. The fix is eager loading with Include or projection at the query level.
Is distributed caching always worth it?
It depends on access patterns. If data is read frequently and writes are infrequent, caching is a huge win. If data changes constantly or is rarely re-read, caching adds complexity without benefit. Profile your API to identify hot data first, then cache selectively.
How many indexes should I create?
Start with indexes on columns used in WHERE clauses and joins. For each index, measure its impact: does it speed up queries noticeably? Does it slow down writes? Remove unused indexes. Too many indexes waste storage and hurt write performance. Aim for 3 to 5 well-chosen indexes per high-traffic table.
When should I use raw SQL instead of EF Core?
Use raw SQL when EF Core translates a query inefficiently or when you need database-specific features (window functions, CTEs, bulk operations). For most business logic, EF Core is cleaner and safer. Use raw SQL tactically for specific queries, not as a wholesale replacement.
