Listen, I’ve been there. You’re cranking out endpoints, shipping features, feeling productive. Your API works. Tests pass. Code review? Green checkmarks all around.
Then production hits, and suddenly your beautiful API that hummed along with test data is crawling. Users are complaining. Your monitoring dashboard looks like a Christmas tree of alerts. And you’re sitting there, staring at your perfectly clean code, wondering what the hell went wrong.
Nine times out of ten? It’s Entity Framework Core. Or rather, it’s how you’re using EF Core.
Let me show you the patterns that are silently murdering your API performance — and what to do instead.
1. The “Load Everything, Filter Later” Disaster
What you’re probably doing:
public async Task<List<OrderDto>> GetRecentOrders(int customerId)
{
var orders = await _context.Orders
.Include(o => o.OrderItems)
.ThenInclude(i => i.Product)
.Include(o => o.Customer)
.ToListAsync();
return orders
.Where(o => o.CustomerId == customerId)
.Where(o => o.OrderDate > DateTime.Now.AddDays(-30))
.Select(o => MapToDto(o))
.ToList();
}
Do you see it? You just pulled every single order from your database into memory, along with all their items, products, and customer data. Then you filtered it in C#.
Your database is screaming. Your memory is crying. And your users? They’re rage-clicking the refresh button.
What you should be doing:
public async Task<List<OrderDto>> GetRecentOrders(int customerId)
{
return await _context.Orders
.Where(o => o.CustomerId == customerId)
.Where(o => o.OrderDate > DateTime.Now.AddDays(-30))
.Select(o => new OrderDto
{
OrderId = o.OrderId,
OrderDate = o.OrderDate,
TotalAmount = o.TotalAmount,
ItemCount = o.OrderItems.Count,
CustomerName = o.Customer.Name
})
.ToListAsync();
}
Why this matters: The first version might load 10,000 orders with 50,000 related items. The second? Only the 15 orders that actually match your criteria, and only the specific columns you need. That’s the difference between a 2-second response and a 50ms response.
2. The N+1 Query Nightmare (And You Don’t Even Know It’s Happening)
This one is sneaky. Your code looks innocent. But behind the scenes, EF Core is making hundreds of database calls.
The silent killer:
public async Task<List<ProductViewModel>> GetProductsWithCategories()
{
var products = await _context.Products.ToListAsync();
var viewModels = products.Select(p => new ProductViewModel
{
Name = p.Name,
Price = p.Price,
CategoryName = p.Category.Name // 💀 OH NO
}).ToList();
return viewModels;
}
Looks harmless, right? Wrong.
Here’s what actually happens:
- One query to get all products
- One query for each product to get its category
Got 100 products? That’s 101 database queries. Got 1,000 products? You’re making 1,001 round trips to your database.
The fix:
public async Task<List<ProductViewModel>> GetProductsWithCategories()
{
return await _context.Products
.Select(p => new ProductViewModel
{
Name = p.Name,
Price = p.Price,
CategoryName = p.Category.Name
})
.ToListAsync();
}
One query. One trip. Done.
Pro tip: Turn on sensitive data logging in development to see what queries EF Core is actually generating:
optionsBuilder.EnableSensitiveDataLogging()
.LogTo(Console.WriteLine, LogLevel.Information);
You’ll be shocked at what you find.
3. Tracking Everything Like a Paranoid Spy
By default, EF Core tracks every entity you query. It’s watching for changes, maintaining state, building change detection graphs. All of that costs memory and CPU.
For read-only operations? It’s pure waste.
The wasteful way:
public async Task<DashboardStats> GetDashboardStats()
{
var orders = await _context.Orders
.Where(o => o.OrderDate > DateTime.Now.AddMonths(-1))
.ToListAsync();
return new DashboardStats
{
TotalOrders = orders.Count,
TotalRevenue = orders.Sum(o => o.TotalAmount),
AverageOrderValue = orders.Average(o => o.TotalAmount)
};
}
The smart way:
public async Task<DashboardStats> GetDashboardStats()
{
var stats = await _context.Orders
.Where(o => o.OrderDate > DateTime.Now.AddMonths(-1))
.AsNoTracking() // 🎯 The magic line
.GroupBy(o => 1)
.Select(g => new DashboardStats
{
TotalOrders = g.Count(),
TotalRevenue = g.Sum(o => o.TotalAmount),
AverageOrderValue = g.Average(o => o.TotalAmount)
})
.FirstOrDefaultAsync() ?? new DashboardStats();
return stats;
}
Better yet? Do the aggregation in the database:
public async Task<DashboardStats> GetDashboardStats()
{
return await _context.Orders
.Where(o => o.OrderDate > DateTime.Now.AddMonths(-1))
.GroupBy(o => 1)
.Select(g => new DashboardStats
{
TotalOrders = g.Count(),
TotalRevenue = g.Sum(o => o.TotalAmount),
AverageOrderValue = g.Average(o => o.TotalAmount)
})
.AsNoTracking()
.FirstOrDefaultAsync() ?? new DashboardStats();
}
Your database is really good at math. Let it do what it does best.
4. The “Update Everything” Approach
Updating a single field? Don’t load the entire entity, change it, and save it back.
The inefficient way:
public async Task UpdateOrderStatus(int orderId, string newStatus)
{
var order = await _context.Orders
.Include(o => o.OrderItems)
.ThenInclude(i => i.Product)
.Include(o => o.Customer)
.FirstOrDefaultAsync(o => o.OrderId == orderId);
if (order != null)
{
order.Status = newStatus;
await _context.SaveChangesAsync();
}
}
You just loaded an entire object graph to update one string field.
The efficient way:
public async Task UpdateOrderStatus(int orderId, string newStatus)
{
await _context.Orders
.Where(o => o.OrderId == orderId)
.ExecuteUpdateAsync(s => s.SetProperty(o => o.Status, newStatus));
}
One targeted SQL UPDATE statement. No tracking. No unnecessary loading. Just pure efficiency.
5. Forgetting That async/await Isn’t Magical
I see this everywhere:
public async Task<List<ProductDto>> GetProducts()
{
var products = await _context.Products.ToListAsync();
// Then doing a ton of synchronous processing
foreach (var product in products)
{
product.ProcessSomething(); // Synchronous!
product.CalculateDiscount(); // Synchronous!
product.FormatPrice(); // Synchronous!
}
return products;
}
You made the database call async, which is great. But then you’re blocking the thread doing synchronous work anyway.
If you’re doing heavy processing, either:
- Push it to the database (aggregations, calculations)
- Use true async operations (I/O-bound work)
- Consider background processing (Hangfire, message queues)
6. Ignoring Connection Pooling Limits
Your DbContext isn’t thread-safe. Creating and disposing it constantly isn’t free. But the real killer? Holding onto contexts too long.
Don’t do this:
public class MyService
{
private readonly ApplicationDbContext _context;
public MyService(ApplicationDbContext context)
{
_context = context; // Singleton service with scoped DbContext? 💀
}
}
Do this:
public class MyService
{
private readonly IDbContextFactory<ApplicationDbContext> _contextFactory;
public MyService(IDbContextFactory<ApplicationDbContext> contextFactory)
{
_contextFactory = contextFactory;
}
public async Task DoWork()
{
using var context = await _contextFactory.CreateDbContextAsync();
// Use context
} // Disposed immediately
}
Or stick with the standard scoped pattern in ASP.NET Core — just don’t inject it into singletons.
7. Not Projecting to DTOs Early Enough
This is huge. The longer you work with full entities, the more memory you use, and the more EF Core has to track.
Late projection (bad):
var orders = await _context.Orders
.Include(o => o.OrderItems)
.ThenInclude(i => i.Product)
.Include(o => o.Customer)
.ThenInclude(c => c.Address)
.Where(o => o.OrderDate > startDate)
.ToListAsync(); // Everything loaded into memory here
return orders.Select(o => MapToDto(o)).ToList(); // Then projected
Early projection (good):
return await _context.Orders
.Where(o => o.OrderDate > startDate)
.Select(o => new OrderDto // Project immediately
{
OrderId = o.OrderId,
CustomerName = o.Customer.Name,
CustomerCity = o.Customer.Address.City,
ItemCount = o.OrderItems.Count,
Total = o.OrderItems.Sum(i => i.Quantity * i.UnitPrice)
})
.ToListAsync();
The database does the joining and filtering. You only get the exact data you need.
The Bottom Line
EF Core is a fantastic tool. It saves you from writing SQL by hand. It handles migrations. It makes your code cleaner.
But it’s not magic. It’s a translator between C# and SQL, and if you don’t understand what it’s translating your code into, you’re going to have a bad time.
Here’s your cheat sheet:
✅ Filter in the database, not in memory
✅ Use .Select() to project early
✅ Use .AsNoTracking() for read-only queries
✅ Use .Include() wisely (or better yet, use projections)
✅ Use ExecuteUpdateAsync and ExecuteDeleteAsync for bulk operations
✅ Log and review your actual SQL queries
✅ Profile your queries in production
❌ Don’t load everything then filter
❌ Don’t forget about N+1 queries
❌ Don’t track entities you’re never updating
❌ Don’t do aggregations in C# that the database can do
❌ Don’t hold onto DbContext instances
Your API will thank you. Your database will thank you. And most importantly, your users will thank you.
Now go check your query logs. I’ll wait here while you realize how many queries you’re actually running.
Don’t worry — we’ve all been there. The important thing is fixing it before your CEO asks why the server costs tripled this month.
If you really enjoyed this blog, don’t forget to support me by buying me a coffee here ☕
https://www.patreon.com/cw/CodeWithPushpa25