Entity Framework Core anti-patterns
Entity Framework Core is arguably one of the most popular frameworks used in .NET development. It is an Object Relational Mapper (ORM) that supports both relational and NoSQL databases.
In this post, I am going to recap and explain with code the 8 most common mistakes developers make with EF Core when working with relational schemas. These anti-patterns negatively affect application performance and often become the bottleneck as an application grows.
This post is a written summary of the live stream .NET Data Community Standup: 8 Real-World Query Anti-Patterns (and How to Fix Them) from the .NET YouTube channel, with extra code snippets and side notes. If you have time, I encourage you to watch the full video.
1. N + 1 problem
The list starts with one of the worst situations that can happen — the N + 1 issue. Imagine you query a list of manufacturers, and each manufacturer has one or many products. An N + 1 query might look like the following:
var manufacturers = context.Manufacturers
.Where(m => m.Country == Countries.USA)
.ToList();
foreach (var manufacturer in manufacturers)
{
Console.WriteLine("Manufacturer: " + manufacturer.Name);
// loading all products for each manufacturer explicitly
context.Entry(manufacturer).Collection(m => m.Products).Load();
foreach (var product in manufacturer.Products)
{
Console.WriteLine(product.Title);
}
}
Or, if you have enabled lazy loading, the query you compose may look even more innocent:
var manufacturers = context.Manufacturers
.Where(m => m.Country == Countries.USA)
.ToList();
foreach (var manufacturer in manufacturers)
{
Console.WriteLine("Manufacturer: " + manufacturer.Name);
foreach (var product in manufacturer.Products)
{
Console.WriteLine("\t" + product.Title);
}
}
In my sample database, I have "Apple" and "Tesla" as the only manufacturers in the US, so since we get 2 parent entities, an ORM will generate 3 queries in total. Both the explicit and lazy loading queries produce the same queries below, combined with the WriteLine output:
SELECT [m].[Id], [m].[Country], [m].[Name]
FROM [Manufacturers] AS [m]
WHERE [m].[Country] = N'USA'
Manufacturer: Apple
SELECT [p].[Id], [p].[Description], [p].[ManufacturerId], [p].[Price], [p].[Title]
FROM [Products] AS [p]
WHERE [p].[ManufacturerId] = @p
iPhone 17
iPad mini 7
Manufacturer: Tesla
SELECT [p].[Id], [p].[Description], [p].[ManufacturerId], [p].[Price], [p].[Title]
FROM [Products] AS [p]
WHERE [p].[ManufacturerId] = @p
Model 3
Cybertruck
I only had 2 parent entities in my example, but you can already imagine how easily this could scale to more manufacturers, generating even more queries. A solution proposed was to use Eager Loading.
var manufacturers = context.Manufacturers
.Where(m => m.Country == Countries.USA)
// eagerly load all products
.Include(m => m.Products)
.ToList();
foreach (var manufacturer in manufacturers)
{
Console.WriteLine("Manufacturer: " + manufacturer.Name);
foreach (var product in manufacturer.Products)
{
Console.WriteLine("\t" + product.Title);
}
}
SELECT [m].[Id], [m].[Country], [m].[Name], [p].[Id], [p].[Description], [p].[ManufacturerId], [p].[Price], [p].[Title]
FROM [Manufacturers] AS [m]
LEFT JOIN [Products] AS [p] ON [m].[Id] = [p].[ManufacturerId]
WHERE [m].[Country] = N'USA'
ORDER BY [m].[Id]
Manufacturer: Apple
iPhone 17
iPad mini 7
Manufacturer: Tesla
Model 3
Cybertruck
As we can see, this time, only one query was generated and all the related entities, which are products in this case, were loaded immediately. The Include() method generates a JOIN statement, which needs to be used cautiously, otherwise it can lead to anti-pattern number two.
We will also come back to this query later when we discuss Projection.
2. Cartesian explosion
As mentioned previously, when we load related entities with eager loading, EF Core uses a JOIN statement. Logically, this means that two or more Include calls that load collection navigations of the same entity cause a Cartesian product: every row of one collection gets combined with every row of the other. (Nested includes with ThenInclude don't have this problem, because those collections are not siblings.)
var manufacturers = context.Manufacturers
.Where(m => m.Country == Countries.USA)
.Include(m => m.Products)
.Include(m => m.Suppliers)
.ToList();
SELECT [m].[Id], [m].[Country], [m].[Name], [p].[Id], [p].[Description], [p].[ManufacturerId], [p].[Price], [p].[Title], [s0].[ManufacturersId], [s0].[SuppliersId], [s0].[Id], [s0].[Name]
FROM [Manufacturers] AS [m]
LEFT JOIN [Products] AS [p] ON [m].[Id] = [p].[ManufacturerId]
LEFT JOIN (
SELECT [m0].[ManufacturersId], [m0].[SuppliersId], [s].[Id], [s].[Name]
FROM [ManufacturerSupplier] AS [m0]
INNER JOIN [Suppliers] AS [s] ON [m0].[SuppliersId] = [s].[Id]
) AS [s0] ON [m].[Id] = [s0].[ManufacturersId]
WHERE [m].[Country] = N'USA'
ORDER BY [m].[Id], [p].[Id], [s0].[ManufacturersId], [s0].[SuppliersId]
The generated SQL query is a clear sign of a Cartesian explosion. For example, if a manufacturer has 6 products and 3 suppliers, 6 × 3 = 18 rows will be sent over the network for that manufacturer, even though there are only 9 related records (6 + 3). If we used three joins, we would multiply the row counts of all three collections, so the total number of rows would explode — and that's where the issue gets its name.
EF Core offers another approach: the AsSplitQuery() method, which generates a separate SQL query for each included collection navigation.
var manufacturers = context.Manufacturers
.Where(m => m.Country == Countries.USA)
.Include(m => m.Products)
.Include(m => m.Suppliers)
.AsSplitQuery()
.ToList();
SELECT [m].[Id], [m].[Country], [m].[Name]
FROM [Manufacturers] AS [m]
WHERE [m].[Country] = N'USA'
ORDER BY [m].[Id]
SELECT [p].[Id], [p].[Description], [p].[ManufacturerId], [p].[Price], [p].[Title], [m].[Id]
FROM [Manufacturers] AS [m]
INNER JOIN [Products] AS [p] ON [m].[Id] = [p].[ManufacturerId]
WHERE [m].[Country] = N'USA'
ORDER BY [m].[Id]
SELECT [s0].[ManufacturersId], [s0].[SuppliersId], [s0].[Id], [s0].[Name], [m].[Id]
FROM [Manufacturers] AS [m]
INNER JOIN (
SELECT [m0].[ManufacturersId], [m0].[SuppliersId], [s].[Id], [s].[Name]
FROM [ManufacturerSupplier] AS [m0]
INNER JOIN [Suppliers] AS [s] ON [m0].[SuppliersId] = [s].[Id]
) AS [s0] ON [m].[Id] = [s0].[ManufacturersId]
WHERE [m].[Country] = N'USA'
ORDER BY [m].[Id]
As we can see here, first a query loads all the filtered manufacturers, and then two more queries are sent: one to get the related products and another one to get the related suppliers. The suppliers query uses two joins because it is a many-to-many relationship, but the overall concept is that we got rid of the Cartesian product problem.
However, this approach has its own flaws, and that's why it is not the default behavior.
You can set AsSplitQuery to be your default query splitting behavior:
optionsBuilder.UseSqlServer(
connectionStringBuilder.ConnectionString,
options => options.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
And if later in the application you need to send a single (Cartesian) query, you can use AsSingleQuery():
var manufacturers = context.Manufacturers
.Where(m => m.Country == Countries.USA)
.Include(m => m.Products)
.Include(m => m.Suppliers)
.AsSingleQuery()
.ToList();
Split queries are not the default behavior because they come with their own downsides:
-
Results are not guaranteed to be consistent. Within a single query, database engines guarantee consistency; however, if the underlying data changes between your split queries, the final result may be inconsistent. You can mitigate this by wrapping the queries in a serializable or snapshot transaction, although that may create performance issues of its own.
-
Multiple roundtrips are sent to the database. Each split query costs an additional network call, and network calls are often the underlying reason for slow responses, especially when the latency to the database is high (for example, with cloud services).
You can see the full list at the following link.
So in the end, both approaches have downsides. For that reason, it is sometimes worth thinking about your queries before you start designing your schema. If you frequently need to load an entity together with its related collections, a document database (such as Azure Cosmos DB or MongoDB) may be a better fit. There, the related data is usually embedded directly inside the parent document, typically stored as JSON, so a single read returns everything at once and neither a Cartesian explosion nor extra roundtrips come into play.
3. Requesting more rows than needed
The first two anti-patterns were not particularly specific to EF Core; they are simply how relational databases work. This anti-pattern, on the other hand, is specific to EF Core and C# expression trees.
Not everything we put into a query can be translated into SQL. For example, imagine we extract our filter into a regular C# method and use it inside Where:
static bool IsAmericanManufacturer(Manufacturer manufacturer)
=> manufacturer.Country == Countries.USA;
var manufacturers = context.Manufacturers
.Where(m => IsAmericanManufacturer(m))
.ToList();
Unhandled exception. System.InvalidOperationException: The LINQ expression 'DbSet<Manufacturer>()
.Where(m => Program.IsAmericanManufacturer(m))' could not be translated.
Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly...
It's not really "the database" that has a problem with IsAmericanManufacturer — it's EF Core's LINQ-to-SQL translation layer, which can't turn an arbitrary C# method into a SQL fragment because all it sees in the expression tree is a method call, not the method's body. It's also worth noting when this actually throws: writing .Where(m => IsAmericanManufacturer(m)) alone doesn't throw anything; it just builds an expression tree. The exception only fires once the query is executed, for example when you call .ToList() or .FirstOrDefault(), or otherwise enumerate it.
What is interesting is that this was not always the default design the EF Core team chose. Before EF Core 3.0, a query like this would still run: EF Core fell back to client evaluation and only logged a warning. All the rows would be returned from the database, and the filtering would then happen in your application's memory. EF Core 3.0 removed that automatic fallback and made the query throw an exception instead, precisely to stop this anti-pattern from happening unnoticed. The only place where EF Core still evaluates on the client automatically is the top-level projection (the last Select of the query).
That said, you can still reintroduce the exact same problem today — EF Core just requires you to opt in. If you "fix" the exception above by inserting AsEnumerable() before the Where(), you're telling EF Core to stop translating the query at that point and fall back to plain LINQ-to-Objects for everything after it:
var manufacturers = context.Manufacturers
.AsEnumerable() // switches to LINQ-to-Objects from here on
.Where(m => IsAmericanManufacturer(m))
.ToList();
AsEnumerable() itself doesn't trigger a database call — it only changes the compile-time type from IQueryable to IEnumerable, so every operator after it runs in memory using LINQ-to-Objects. The actual roundtrip still only happens once .ToList() runs. But because the filter now lives after that switch, EF Core has nothing left to put in a WHERE clause, so the entire Manufacturers table gets pulled over the network before any filtering happens:
SELECT [m].[Id], [m].[Country], [m].[Name]
FROM [Manufacturers] AS [m]
This is the anti-pattern this section is named after: requesting, and transferring, more rows than the application actually needs, then throwing most of them away in memory.
The takeaway from this anti-pattern is to always try to maximize filtering on the database level and keep network calls as light as possible. In this example, the fix is simply to write the condition in a form EF Core can translate: .Where(m => m.Country == Countries.USA). The exception is when your application has an intended need for client-side filtering or pagination, for example a grid component that works with an already loaded dataset.
4. Not using projection
It's fairly common for an entity to contain more data (or columns, in this context) than a particular method or API client actually needs. For this reason, it's more efficient to avoid requesting all of an entity's data and use projection instead, which means selecting only the values we need with Select or SelectMany. Going back to the first code snippet, we can use a projection to get back only the product titles.
var productTitles = context.Manufacturers
.Where(m => m.Country == Countries.USA)
.SelectMany(m => m.Products.Select(p => p.Title))
.ToList();
foreach (var productTitle in productTitles)
{
Console.WriteLine(productTitle);
}
SELECT [p].[Title]
FROM [Manufacturers] AS [m]
INNER JOIN [Products] AS [p] ON [m].[Id] = [p].[ManufacturerId]
WHERE [m].[Country] = N'USA'
iPhone 17
iPad mini 7
Model 3
Cybertruck
With this projection, only one column is returned in the result set, making the query more efficient. It's also worth noting that with projection, we don't need to explicitly load related entities with Include() — EF Core adds the joins it needs to the same query.
An important point to note here, though, is that EF Core's change tracker only tracks entity instances. Our projection returns plain strings, not Manufacturer or Product instances, so there's nothing for EF Core to track. That means we can't just modify productTitles in memory and call SaveChanges() to persist the change — there's no tracked entity for EF Core to compare against. For this situation, we can use the ExecuteUpdate method (added in EF Core 7), which is called on a query over the entity itself (such as context.Products.Where(...)) — not on a projection of plain values like ours — and translates straight into a SQL UPDATE statement without loading or tracking any entities. It runs immediately and skips SaveChanges() entirely, giving us the best of both worlds: an efficient read and an efficient update.
context.Products
.Where(p => p.Title == "iPhone 17")
.ExecuteUpdate(setters => setters.SetProperty(p => p.Description, "Updated description"));
Keep in mind that ExecuteUpdate bypasses the change tracker, so entities that are already tracked by the same context will not reflect the change.
You can read more about EF Core tracking with custom projections in the documentation. For example, entity instances nested inside an anonymous type are still tracked.
5. Missing pagination or not using keyset pagination
Normally, we do not want to return all the data at once; instead, we return it in the form of "pages". For instance, say the page size is 20 items and we need page 3. A query of this type would look like this:
var page = 3;
var pageSize = 20;
var toSkip = (page - 1) * pageSize;
var products = context.Products.OrderBy(p => p.Id)
.Skip(toSkip)
.Take(pageSize)
.ToList();
SELECT [p].[Id], [p].[Description], [p].[ManufacturerId], [p].[Price], [p].[Title]
FROM [Products] AS [p]
ORDER BY [p].[Id]
OFFSET @p ROWS FETCH NEXT @p1 ROWS ONLY
This type of pagination is called Offset pagination, because it relies on the OFFSET keyword to skip rows. This approach, however, has two main drawbacks:
-
Inefficiency — the database engine still has to scan through and discard all the skipped records, making this pagination more and more inefficient as the skip amount grows.
-
Inconsistency — if you showed a user page 2, and in the meantime a record from page 1 or page 2 was deleted, all the following records shift up by one position. When the user then requests page 3,
OFFSETskips the record that should have been the first one on that page, so the user never sees it. Similarly, a newly inserted record can make the user see the same record twice.
For this reason, a better approach is to use Keyset pagination.
This approach substitutes the Skip method with the Where method, which continues right after the last record the user has seen. A keyset pagination may look like this:
var lastIdShown = 72;
var pageSize = 20;
var products = context.Products.OrderBy(p => p.Id)
.Where(p => p.Id > lastIdShown)
.Take(pageSize)
.ToList();
SELECT TOP(@p) [p].[Id], [p].[Description], [p].[ManufacturerId], [p].[Price], [p].[Title]
FROM [Products] AS [p]
WHERE [p].[Id] > @lastIdShown
ORDER BY [p].[Id]
This implementation solves both issues. The query is not affected by concurrent changes to records that come before the last one the user saw, and, as long as the ordering column is indexed (which a primary key like Id always is), the database engine no longer has to scan through and discard rows to reach the requested page.
The only change in implementation is that now you need to know the last record the user saw, instead of the page number they are requesting.
Whichever pagination approach you use, make sure your ordering is fully unique. If you order by a column that can contain duplicates (for example, a date), add a unique column such as Id as a tiebreaker, otherwise records may be skipped or shown twice.
One trade-off worth knowing, though: because there's no offset to compute from, keyset pagination can't jump straight to an arbitrary page — you can only move to the next (or previous) batch relative to a known cursor.
If you do need random access, the online documentation shared in the live stream suggests combining both approaches to support jumping to an arbitrary page as well as efficient cursor-like advancement:
If random access pagination is necessary, a robust implementation could use keyset pagination when navigating to the next/previous page, and offset navigation when jumping to any other page.
6. Not using async methods
This is a very straightforward anti-pattern: using synchronous EF Core methods when an asynchronous version is available. For example, prefer ToListAsync() over ToList(), and SaveChangesAsync() over SaveChanges(), etc.
The reason is that database calls are I/O-bound. A synchronous call blocks the calling thread while it waits for the database to respond, while an asynchronous call frees that thread to do other work in the meantime. In an application that handles many concurrent requests, such as a web API, blocked threads add up quickly and limit how many requests the app can serve. Async doesn't make an individual query faster; it lets your application do more with the same number of threads.
Some SDKs, like the one for Azure Cosmos DB, don't even provide synchronous APIs for database network calls. Before EF Core 9, calling a synchronous EF Core method against Cosmos blocked on the asynchronous SDK call (sync-over-async), which can result in deadlocks. Starting from EF Core 9, this throws an exception by default:
cosmosContext.SaveChanges();
System.InvalidOperationException: An error was generated for warning 'Microsoft.EntityFrameworkCore.Database.SyncNotSupported':
Azure Cosmos DB does not support synchronous I/O. Make sure to use and correctly await only
async methods when using Entity Framework Core to access Azure Cosmos DB.
Today you can still suppress the exception and fall back to sync-over-async via ConfigureWarnings, but EF Core 11 (scheduled for November 2026) removes synchronous I/O from the Cosmos provider entirely, so async will be the only option. See the GitHub issue for details.
7. Tracking read-only results
When we query a database, by default EF Core tracks the returned entities. Queries that return no entity instances, like the projection in anti-pattern 4, aren't tracked; see the rules around custom projections.
Tracking means that for every entity returned, EF Core takes a snapshot of its original state and adds it to an internal identity map, so that later, when SaveChanges() is called, it can compare the current values against that snapshot and figure out what actually changed.
That bookkeeping isn't free — it costs extra memory and CPU time. If we're only reading data and have no intention of ever modifying it, for example when returning it straight into an API response, we're paying for a feature we'll never use.
var manufacturers = context.Manufacturers
.Where(m => m.Country == Countries.USA)
.ToList();
To avoid this, EF Core lets us opt out of tracking with AsNoTracking():
var manufacturers = context.Manufacturers
.Where(m => m.Country == Countries.USA)
.AsNoTracking()
.ToList();
The SQL generated is exactly the same in both cases — AsNoTracking() doesn't change what's requested from the database, only what EF Core does with the results once they arrive. On read-heavy paths returning a lot of entities, skipping the snapshotting and identity map can meaningfully cut down both memory usage and query time.
If an entire DbContext is only ever used for reads — a dedicated reporting or query context, for instance — it's also possible to make no-tracking the default behavior for every query issued through it, instead of adding AsNoTracking() everywhere:
context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
You can also set this once when configuring the context, with UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking), and opt back in for individual queries using AsTracking().
If the same entity appears more than once in a result set — for example when it's reached through multiple join paths — AsNoTracking() will materialize a separate object instance for each occurrence, since it doesn't do identity resolution. When you still want a single shared instance per entity but don't need change tracking, use AsNoTrackingWithIdentityResolution() instead. It uses a stand-alone change tracker in the background to make sure each entity is materialized only once, and the results are still not tracked by your context.
8. Not using indexes
The final anti-pattern that was brought up touches one of the most popular relational database optimization concepts — using indexes. If you expect to query by certain columns (or a single column) often, then consider adding indexes on those columns. The word "often" might be too vague, but think of it as something that is on the hot path or executed regularly. "Query by" here isn't only the WHERE clause — columns you JOIN on or ORDER BY benefit from an index too.
It's worth knowing that EF Core already creates some indexes for you: by convention, it adds an index for every foreign key (and for the columns behind an alternate key). It does not, however, index anything else automatically — regular query columns are still up to you. Adding an index of your own is straightforward:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Product>()
.HasIndex(p => p.Title);
}
You can also use the [Index] attribute on the entity class. Either way, remember to add a migration so the index gets created in the database.
EF Core has good support for different kinds of indexes, such as composite, unique, and filtered ones. You can read more about them in the documentation.
On the other hand, data that is only used for a report that runs once a week or once a month generally shouldn't be a candidate for a new index. This is because indexes come with their own cost. They take up extra disk space, since the indexed column values are stored separately, and they slow down writes, because every insert or delete — and every update to an indexed column — now also has to keep the index in sync.
Frequency isn't the only thing to weigh, though. Even on a hot path, an index can be a bad trade if it doesn't help the query planner narrow things down much. A column with very few distinct values — a boolean flag, a status with three options — is a classic example: a query filtering on it still matches a large share of the table, so the database often decides a full scan is cheaper than bouncing between the index and the table, and the index just sits there adding write overhead. There is an exception, though: if the values are heavily skewed and you only ever look for the rare ones (for example, the few unprocessed rows among millions of processed ones), an index, or better yet a filtered index, can still pay off. Tiny tables are another example where an index doesn't help: if the whole table fits in a page or two, scanning it is already fast.
Summary and recommendations
This was a written summary — with a few additions of my own — of the .NET Data Community Standup: 8 Real-World Query Anti-Patterns (and How to Fix Them), led by Chris Woodruff. To recap:
-
N + 1 problem — don't load related data inside a loop; use eager loading (
Include()) or a projection instead. -
Cartesian explosion — avoid loading multiple sibling collections with joins in a single query; consider
AsSplitQuery(), keeping its trade-offs in mind. -
Requesting more rows than needed — keep filters translatable so they run in the database, not in memory.
-
Not using projection — select only the data you need.
-
Offset pagination — prefer keyset pagination, especially for large or frequently changing data.
-
Not using async methods — use the async APIs so you don't block threads while waiting for the database.
-
Tracking read-only results — use
AsNoTracking()when you are only reading data. -
Not using indexes — index the columns on your hot paths, but not blindly, since indexes come with a cost.
I found it genuinely useful, and that is why I decided to turn it into a blog post. I still encourage you to watch the original video as well — it has no slides, so it works just as well as a podcast if you would rather listen than watch.
I also always enjoy Shay Rojansky's participation and comments in these standups. He is one of my favorite contributors to .NET, and he has a real talent for explaining things clearly. So if you have time and want to stay sharp (yes, we all live in the C-sharp world xD), consider watching other standups where he participates.