7 EF Core Mistakes That Wreck Performance and How to Fix Them

Why EF Core Performance Matters More Than You Think

Entity Framework Core is a beloved ORM in the .NET ecosystem, but its convenience hides a serious trap: many common usage patterns silently degrade performance as your application scales. I have seen projects where a simple page load triggers dozens of database round-trips, all because of a single misused navigation property. This guide exposes seven mistakes that repeatedly cause performance issues and provides actionable solutions grounded in real-world experience.

How Small Mistakes Compound

Imagine a typical e-commerce backend that displays orders with their line items. A developer new to EF Core might write a loop that accesses order.Items inside a view, triggering a separate SQL query for each order. This is the classic N+1 problem, and it can turn a single-page request into dozens or even hundreds of database calls. Over a busy period, these small inefficiencies stack up, increasing latency and database load. In one anonymized project, such a pattern caused response times to spike from 200ms to over 5 seconds, leading to user complaints and a costly emergency refactor. The fix was straightforward—using Include to eager-load related data—but the damage had already been done.

The Cost of Ignoring the Data Layer

Performance issues in EF Core are not always obvious. A query that runs fine on a local development database with a few hundred rows can become unacceptably slow on a production database with millions of rows. I have often seen teams blame the database server or network when the real culprit is an inefficient LINQ query that translates to a poorly optimized SQL statement. The key is to understand how EF Core translates LINQ expressions into SQL and to use tools like the ToQueryString method or a profiler to inspect generated queries. This awareness is the first step toward building high-performance data access layers.

This article focuses on seven specific mistakes that I have encountered across multiple projects. Each mistake is presented with a problem description, a concrete example, and a proven fix. The goal is to give you a mental checklist that you can apply in your daily work, helping you avoid the most common performance pitfalls before they become expensive problems. Remember, the best time to fix a performance issue is before it reaches production.

Mistake 1: The N+1 Query Problem and How to Slay It

The N+1 query problem is arguably the most common performance mistake in EF Core. It occurs when you load a collection of entities and then iterate over them, accessing a navigation property that triggers a separate SQL query for each parent entity. The result is 1 query for the parent entities plus N queries for the related data—hence the name. This pattern can devastate performance, especially when N is large.

Identifying the Problem in Your Code

Consider a blog application where you want to display a list of posts along with their authors. A naive approach might look like this:

var posts = context.Posts.ToList(); foreach (var post in posts) { Console.WriteLine(post.Author.Name); }

This code executes one query to load all posts, but then for each post, it executes another query to load the Author navigation property. If you have 100 posts, that is 101 database round-trips. In a real-world scenario, the impact is immediate: page load times increase dramatically, and the database server becomes overwhelmed with small queries. The fix is simple: use eager loading with the Include method to load related data in a single query.

Fix: Replace the lazy loop with eager loading:

var posts = context.Posts.Include(p => p.Author).ToList();

This produces a single SQL query with a JOIN, reducing the round-trips to just one. The improvement is dramatic—often reducing load times by an order of magnitude. I have seen a similar fix in a project where the response time dropped from 12 seconds to under 200 milliseconds.

When Eager Loading Is Not Enough

Eager loading works well for simple hierarchies, but complex scenarios with multiple levels or conditional loading require more thought. For instance, if you only need a subset of related data, consider using ThenInclude for nested properties or filtered includes (available in EF Core 5+). Filtered includes allow you to load only related entities that meet a condition, reducing the amount of data transferred. For example:

var posts = context.Posts .Include(p => p.Comments .Where(c => c.IsApproved)) .ToList();

This loads only approved comments, preventing unnecessary data from being fetched. Another alternative is explicit loading for scenarios where you need to load related data conditionally after the parent entities are already in memory. However, explicit loading still requires a separate round-trip, so use it sparingly. The golden rule is to always be aware of how many queries your code generates. Use a profiler or enable logging of SQL statements during development to catch N+1 patterns early. This simple habit can save hours of debugging later.

Mistake 2: Lazy Loading Abuse and the Hidden Cost of Convenience

Lazy loading is a convenient feature in EF Core that automatically loads related data when you access a navigation property. While it can make initial development faster, it often leads to the N+1 problem and unpredictable performance. The convenience comes at a high cost: you lose control over when and how many database queries are executed.

Why Lazy Loading Is Dangerous in Production

In a typical project, lazy loading might be enabled early on because it simplifies the code. Developers do not need to write Include statements, and the application works fine with small datasets. However, as the application grows, the number of queries can explode. I recall a case where a reporting page that displayed aggregated data with lazy loading caused over 2000 queries to be executed for a single page view. The page timed out frequently, and the database server was under constant load. The fix was to disable lazy loading globally and rewrite the queries using eager loading or explicit projections. After the change, the page loaded in under a second.

Best Practice: Disable lazy loading by default in production environments. You can enable it temporarily for specific scenarios, but avoid relying on it as a general pattern. Instead, explicitly control when related data is loaded. Use eager loading when you know you will need the data, and use projections with Select when you only need a few fields from related entities. Projections are often the most efficient way to retrieve data because they send a tailored query to the database.

Alternatives to Lazy Loading

If you need to load related data on demand, consider using explicit loading with the Load method. This still requires a separate query but gives you explicit control over when it happens. For example:

context.Entry(post).Collection(p => p.Comments).Load();

This loads comments only when you decide, not automatically. Another approach is to use a repository pattern that centralizes data loading logic, making it easier to reason about query counts. Some teams also use GraphQL or OData to let the client specify exactly what data they need, reducing over-fetching. Whichever approach you choose, the key is to be intentional about data loading and avoid the seductive convenience of lazy loading in production. Your database—and your users—will thank you.

Mistake 3: Selecting All Columns When You Only Need a Few

One of the most common performance mistakes in EF Core is using Select * queries when only a subset of columns is needed. This is often done inadvertently by calling .ToList() on an entity set without filtering the properties. The result is that the database returns all columns, consuming more bandwidth and memory, and slowing down query execution.

The Impact of Over-Fetching

Consider a user profile page that needs only the user's name and email address. A typical mistake is to load the entire User entity, which might include fields like password hash, security questions, and internal audit timestamps. This extra data is never used on the page, but it is still fetched from the database and serialized into objects. For a single user, the overhead is negligible, but for a list of 1000 users, it can add significant latency and memory pressure. In one project, I found that switching from full entity loading to projections reduced the payload size by 70%, cutting response times in half.

Fix: Use the Select method to project only the fields you need. For example:

var users = context.Users .Where(u => u.IsActive) .Select(u => new { u.Name, u.Email }) .ToList();

This generates a SQL query that selects only Name and Email columns, resulting in a smaller result set and faster execution. The same principle applies to related data: use Select with nested projections to retrieve exactly the shape you need.

When to Use Projections vs. Full Entities

Projections are ideal for read-only scenarios where you do not need to track changes. However, if you need to update the data later, you might need the full entity. In that case, consider using a lightweight DTO or view model for reads and only load full entities when you intend to modify them. This hybrid approach balances performance with functionality. Another technique is to use auto-mapping libraries like AutoMapper to simplify projection code, but be careful about the overhead of mapping configurations. For high-performance scenarios, manual projections with Select are often the most efficient. The bottom line: never fetch columns you do not need. This simple rule can dramatically improve query performance across your application.

Mistake 4: Ignoring the Database-Side Execution: The Pitfall of Client Evaluation

EF Core translates LINQ queries into SQL, but not all LINQ methods can be converted. When a query cannot be translated, EF Core falls back to client evaluation: it fetches all the data from the database and then applies the remaining filters in memory. This can be a huge performance drain, especially when working with large datasets.

How Client Evaluation Sneaks In

A common scenario is using a custom method inside a LINQ Where clause. For example:

var activeUsers = context.Users .Where(u => IsEligible(u)) .ToList();

If IsEligible is a C# method that cannot be translated to SQL, EF Core will fetch all users from the database and then apply the filter in memory. If the Users table has millions of rows, this means transferring all that data over the network and consuming server memory for filtering. The result is a query that runs orders of magnitude slower than expected.

Fix: Ensure that all filtering logic is expressed in terms that EF Core can translate. Use only expressions that map directly to SQL operators. If you need complex logic, consider rewriting it as a database function or a stored procedure. You can also use EF.Functions for database-specific operations like Like or date comparisons. Always check the generated SQL by logging or using a profiler. EF Core 6+ introduced an exception that is thrown when a query cannot be translated, making it easier to catch these issues during development.

Detecting Client Evaluation in Your Code

Enable logging of query execution to see if any queries are evaluated on the client. In your DbContext configuration, you can add:

optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);

This will show you the generated SQL. If you see a query that fetches all rows from a large table, it is a red flag. Another sign is a sudden spike in memory usage on the application server. By being vigilant, you can catch client evaluation early and refactor the query to run entirely on the database side. This vigilance is a key habit for maintaining high performance in EF Core applications.

Mistake 5: Inefficient Paging: The Problem with Large Offsets

Paging is a common requirement for any application that displays lists of data. Many developers use the Skip and Take methods to implement paging, but this approach becomes increasingly inefficient as the page number grows. The reason is that the database still has to read all the rows up to the offset, which can be extremely costly for large datasets.

How Skip/Take Hurts Performance

Imagine a query that returns 10 results per page, and the user requests page 1000. With Skip(9990).Take(10), the database must scan and discard 9990 rows before returning the final 10. This is wasteful. In one anonymized system, a user reporting module that supported paging over millions of records became unusable for high page numbers, with queries taking over 30 seconds. The root cause was the naive Skip/Take implementation.

Fix: Use keyset pagination (also known as the seek method). Instead of skipping a number of rows, you filter based on the last seen key. For example, if you are paging by Id in ascending order, the query for the next page would be:

var nextPage = context.Users .Where(u => u.Id > lastSeenId) .OrderBy(u => u.Id) .Take(10) .ToList();

This query uses an index seek, which is extremely fast regardless of the page depth. The key is to have a stable sort order and a unique key to paginate on. For multi-column sorting, you can use a compound key. Keyset pagination is more complex to implement, but the performance gains are worth the effort for large datasets.

Trade-offs and When to Still Use Skip/Take

Keyset pagination does not support arbitrary page jumps (e.g., going directly to page 1000), because it relies on the last seen key. If your application requires random access to pages, you may need to combine both approaches: use Skip/Take for early pages and keyset for deeper pages. Another option is to use a count query to estimate position, but this adds overhead. For most real-world scenarios, keyset pagination is the better choice because users rarely jump to page 1000; they typically browse sequentially. Implementing keyset pagination early in your data access layer can save you from a painful refactor later.

Mistake 6: Misusing Transactions and Connection Management

Transactions are essential for data integrity, but they can also be a source of performance issues when misused. Common mistakes include holding transactions open for too long, using unnecessary transactions, or failing to manage connection pooling effectively. These issues can lead to database locks, deadlocks, and connection pool exhaustion.

The Danger of Long-Running Transactions

In one instance, a batch processing job wrapped all its operations in a single transaction. The job processed thousands of records, and the transaction remained open for minutes. This caused other parts of the application to block on the same tables, leading to timeouts and poor user experience. The fix was to break the batch into smaller transactions, each processing a reasonable number of records (e.g., 100 records per transaction). This reduced lock contention and allowed other queries to proceed.

Best Practice: Keep transactions as short as possible. Use the TransactionScope for implicit transactions, but be aware of its default isolation level (Serializable), which can be overly restrictive. Often, a lower isolation level like Read Committed suffices. Additionally, avoid performing long-running operations (like web service calls) inside a transaction. If you must, consider using a compensating transaction pattern instead.

Connection Pooling and Transaction Pitfalls

EF Core uses connection pooling by default, but if you open and close connections frequently or fail to dispose of DbContext instances, you can exhaust the pool. Another common mistake is using multiple DbContext instances within the same transaction, each opening its own connection. This can lead to escalation to a distributed transaction, which is slower and may not be supported by all databases. The solution is to share a single DbContext instance within a unit of work, or use the same connection string to allow pooling to reuse connections. Also, always use using blocks to ensure DbContext is disposed promptly. These practices will keep your connection pool healthy and your transactions efficient.

Mistake 7: Overlooking Query Compilation and Caching

EF Core compiles LINQ queries into SQL before execution. By default, it caches the compiled query plan so that subsequent executions of the same LINQ pattern are faster. However, many developers are unaware of this mechanism and inadvertently break it by using non-constant expressions, leading to repeated compilation overhead.

How Dynamic Queries Hurt Performance

Consider a filter method that builds a query based on user input:

var query = context.Users.Where(u => u.Name.Contains(filter));

If the filter variable changes with each request, EF Core treats each variation as a different query and compiles it separately. Over time, the cache can become polluted with many similar but distinct query plans, causing memory pressure and compilation overhead. In a high-traffic application, this can degrade performance significantly.

Fix: Use EF.CompileQuery or EF.CompileAsyncQuery for queries that are executed frequently with different parameters. These methods compile the query once and reuse the compiled plan for all executions. For example:

private static Func s_compiledQuery = EF.CompileQuery((MyContext ctx, string filter) => ctx.Users.Where(u => u.Name.Contains(filter)));

Call this compiled query instead of building a LINQ query each time. This can yield significant performance improvements, especially for queries that are executed many times per second. Note that compiled queries have some limitations: they cannot be combined with other LINQ operators after compilation, so they are best suited for self-contained queries.

When to Use Compiled Queries

Compiled queries are ideal for high-traffic endpoints like API calls that perform the same query shape repeatedly. They are less useful for ad-hoc reporting queries that change frequently. In those cases, consider using raw SQL or stored procedures for maximum control. Also, be aware that EF Core's automatic caching already handles many scenarios; compiled queries are an optimization for when you observe high CPU usage from query compilation. Use profiling tools to identify compilation hotspots before applying this optimization. The key is to understand the trade-off: compilation speed vs. flexibility.

Frequently Asked Questions About EF Core Performance

This section addresses common questions that arise when developers try to optimize EF Core performance. The answers are based on practical experience and official documentation.

Should I use raw SQL instead of LINQ for performance?

Raw SQL can be faster for complex queries that are difficult to express in LINQ, but it bypasses EF Core's mapping and change tracking. Use raw SQL for read-only, complex reports, but stick with LINQ for CRUD operations to maintain maintainability. A balanced approach is to use LINQ for most queries and fall back to raw SQL for specific bottlenecks after profiling.

How do I detect N+1 queries in production?

Enable logging of all SQL statements in your development environment. In production, use application performance monitoring (APM) tools like Application Insights or MiniProfiler. Look for repeated identical queries in a short time frame. You can also use the context.ChangeTracker.LazyLoadingEnabled property to disable lazy loading globally and force explicit loading.

Is it worth using compiled queries for all queries?

No. Compiled queries add complexity and are only beneficial for queries executed many times. Profile your application to identify the top 10 most frequently executed queries and compile only those. The default caching in EF Core is sufficient for most scenarios. Over-optimizing can lead to code that is harder to maintain.

What about using AsNoTracking for read-only queries?

AsNoTracking is a great optimization for read-only scenarios because it prevents the change tracker from storing entity snapshots, reducing memory usage and speeding up query execution. Always use it when you do not plan to update the entities. However, remember that you cannot call SaveChanges on un-tracked entities without re-attaching them.

How can I reduce the number of database round-trips?

Use eager loading with Include and ThenInclude, batch multiple operations with ExecuteSqlRaw or bulk insert libraries, and consider using projection to return only needed data. Also, avoid repeating the same query by caching results in memory or using a distributed cache like Redis.

Synthesis and Next Actions: Building a Performance-First Culture

Performance optimization in EF Core is not a one-time activity; it is a mindset that should be embedded in your development process. The seven mistakes covered in this guide are common, but they are also preventable with awareness and discipline.

Your Action Plan

Start by auditing your current codebase for these patterns. Enable SQL logging and review the generated queries for each major feature. Use a profiler to measure response times and identify slow queries. Create a checklist for code reviews that includes checking for N+1 queries, lazy loading, client evaluation, and unnecessary columns. Educate your team about these issues through pair programming or lunch-and-learn sessions. Over time, these practices will become second nature.

Remember that optimization is a trade-off: always measure before and after a change to ensure it actually improves performance. Not all optimizations apply to every scenario. For example, compiled queries add complexity and may not be worth it for low-traffic endpoints. Use profiling data to guide your decisions.

Finally, stay updated with EF Core releases. Each new version brings performance improvements and new features like filtered includes, compiled query enhancements, and better translation support. The .NET ecosystem is evolving rapidly, and keeping up with best practices will help you maintain a high-performance data access layer.

This guide provides a starting point for your performance journey. The real value comes from applying these principles consistently and measuring the results. As you refine your approach, you will find that EF Core can be both productive and performant when used correctly.