Entity Framework Core makes data access look like in-memory LINQ, which is why it's easy to write slow code with it. The translation from LINQ to SQL is mechanical, but the choices made in C# (which navigation to include, whether to track entities, when to call .ToList()) decide whether one request fires one query or two hundred. This lesson covers how to see what EF is actually doing, the patterns that turn into performance problems, and the tools EF Core 8 provides to fix them.

A query that can't be seen can't be optimized. EF Core can log every SQL statement it sends to the database, and the first move on any slow code path is to turn that logging on and read the output. Without it, tuning is guesswork.

The simplest way to see SQL during development is LogTo on the DbContextOptionsBuilder. Combined with EnableSensitiveDataLogging, it prints the parameter values too, which matters when a query is slow only for certain inputs.

In production you'd route the logs through ILogger instead of Console.WriteLine and you'd leave EnableSensitiveDataLogging off, since it writes parameter values (including PII) to the log. The category that matters for SQL is Microsoft.EntityFrameworkCore.Database.Command. Filter to that and you get the rendered SQL plus execution time on each call.

A single line in the log looks like this:

The 12ms is the database execution time, not including network or materialization. Hundreds of these for one HTTP request indicate an N+1. One query for 12 seconds points to a missing index or a bad plan. Identifying which problem applies determines what to fix.

Leaving EnableSensitiveDataLogging on in production roughly doubles the log volume per query and exposes parameter values. Use it locally and in non-prod environments only.

A common performance bug in EF code is N+1. The pattern is innocent looking: load a list of entities, iterate it, touch a navigation property, and each access fires a fresh query. One outer query plus N inner queries equals N+1.

A typical version: a reporting endpoint loads orders for a customer and prints the customer name for each one.

The first call fires one SQL statement. The body of the loop, however, touches order.Customer.Name, and Customer is a navigation property that wasn't loaded. With lazy loading enabled, EF fires a SELECT * FROM Customers WHERE Id = @id for each order. With lazy loading off, the call throws NullReferenceException, which at least surfaces the bug. Lazy loading hides it behind a slow response.

The diagram shows what an N+1 looks like on the wire. One outer query returns the orders, and then the loop body causes a separate round trip per order. If the outer query returns 200 rows, that's 201 round trips. Each one is small, but each one has network latency, query parsing, and connection pool overhead.

The detection trick is to count the SQL statements per HTTP request in the logs. Loading N items and seeing N+1 queries with the same shape and different parameter values is the bug. The fix is to tell EF up front what's needed.

Include adds a join to the same SQL statement, so one round trip returns both the orders and the customers. For most N+1 cases this is the fix. Sometimes a projection is even better.

An N+1 with 500 outer rows takes around 500 times the network latency of the fixed version. On a 5ms-per-round-trip database, that's 2.5 seconds of pure waiting. Fixing it usually drops the endpoint from seconds to tens of milliseconds.

EF Core offers three ways to bring related data into memory. They look similar from the C# side, but the SQL each one emits is different, and which one fits depends on whether the requirement is known up front.

Eager loading is the default tool. Ask for the related data up front and EF fetches it in one round trip.

Explicit loading fits when the decision to load related data happens after the parent is already in memory. A common case is a screen that shows order details and only fetches items when the user expands a row.

Lazy loading exists for completeness. Install the Microsoft.EntityFrameworkCore.Proxies package, call UseLazyLoadingProxies() on the options, and mark every navigation property virtual. EF generates a runtime subclass of the entity that overrides the navigation getters to fire a query the first time they're touched.

Lazy loading is the easiest way to write an N+1 by accident. The same loop that caused the problem in the previous section runs fine, never throws, and fires a query per iteration without warning. Most production codebases keep lazy loading off and force the developer to be explicit.

Lazy loading turns every navigation access into a hidden database round trip. A foreach over 1,000 entities that touches one navigation each is 1,000 extra queries that weren't written explicitly.

Include solves N+1, but it has a failure mode of its own. With two Included collections, EF generates a single SELECT that joins all of them, and the row count explodes. This is called a cartesian explosion.

Consider an order with 5 items and 3 status history events. A single query that joins both collections returns 5 times 3 equals 15 rows for that one order. The actual data is 8 items (5 + 3), but the wire payload is 15 rows because each item row repeats the order data and each history event row repeats the order data, with the cross product duplicating everything.

For a single order with small collections, the duplication is annoying but harmless. Run the same query for 1,000 orders, with averages of 10 items and 20 status events each, and the result transfers 1,000 times 10 times 20 = 200,000 rows for what should be 30,000. EF logs a warning when it detects this pattern.

The fix is AsSplitQuery. Instead of one statement with a wide cross-join, EF issues one query per included collection.

Three round trips, no duplication. The trade-off is extra round-trip latency, and the three queries aren't guaranteed to see a consistent snapshot of the database unless wrapped in a transaction. For most workloads the savings on payload size dwarf the extra latency, but it's a knob, not a default.

The diagram contrasts the two strategies. AsSingleQuery emits one wide SQL statement with joins; AsSplitQuery emits one root statement and one follow-up per included collection. Pick single queries when payload size is small and transactional consistency matters. Pick split queries when including multiple collections.

Flip the default at the model level if the codebase mostly uses split queries:

After that, AsSingleQuery overrides per call when the join shape is wanted.

A cartesian explosion of 2 collections with 10 rows each multiplies the result set by 100. With 3 collections of 10 rows each, it's 1,000x. The query plan looks fine; the wire payload is what hurts.

By default, every entity EF materializes joins the change tracker. The context keeps a snapshot of each entity so it can detect modifications when SaveChanges is called. For write paths this is what's needed. For read-only paths, it's pure overhead, often 20 to 40 percent of the query cost.

AsNoTracking tells EF to skip the snapshot and the identity map. The query returns plain objects; the context doesn't know they exist.

The middle row needs unpacking. Without tracking, EF returns a fresh object for every row, even if the same Customer is referenced by 50 different orders. That produces 50 distinct Customer instances in memory pointing at the same row in the database. Usually that's fine. When it isn't (for example, modifying the in-memory graph with shared references), AsNoTrackingWithIdentityResolution does identity resolution within the scope of the query without paying for the full change tracker.

Flip the default for an entire context when most of the code is read-only:

After this change, the rare write path opts back in with AsTracking().

Change tracking allocates a snapshot per entity and runs property comparisons on SaveChanges. On a 10,000-row read-only query, AsNoTracking typically shaves 30 percent off both time and allocations.

Include brings every column of every row across the wire. Most of the time, those extra columns aren't needed. Projection (Select into an anonymous type or DTO) tells EF exactly which columns to fetch.

The generated SQL pulls four scalar values per order, joining Customer and aggregating Items server-side. There's no Order entity in memory, no change tracker entry, no navigation to lazy-load. For pure read APIs this is the appropriate shape.

Projection wins on payload (only the requested columns), on memory (no entity allocation), and on time (no change tracker). The trade-off: a DTO can't be sent through SaveChanges; to update, fetch the entity instead.

A common variation is projecting into a record type, which provides immutability:

Loading whole entities for a list view that needs three fields wastes the unused columns. On wide tables (50+ columns), projection often cuts the result-set size by 90 percent.

Every time EF executes a LINQ query, it walks the expression tree, generates SQL, and caches the plan. The cache works on the expression-tree shape, so a query called once per request gets cached after the first call. For the hottest paths, though, the cache lookup can be skipped by compiling the query explicitly.

EF.CompileAsyncQuery returns a delegate. The delegate captures the expression at compile time and goes straight to execution on each call, skipping the cache lookup and tree traversal. For a query called millions of times per second, the savings show up; for a query called once per request, the cache is fast enough that the difference is negligible.

Bulk operations save much more time. Before EF Core 7, updating or deleting many rows meant loading each entity, mutating it, and calling SaveChanges, which fired one statement per row. EF Core 7 added ExecuteUpdate and ExecuteDelete, which translate directly to a single SQL statement.

One statement, no entity materialization, no change tracking, no SaveChanges round trip. The return value is the row count.

The catch is that ExecuteUpdate and ExecuteDelete bypass the change tracker entirely. If an entity is tracked when a bulk update runs, the tracker doesn't know its row changed. The pattern is fine when the bulk operation is the only thing the context does in that operation; mix it with tracked entities and stale state results.

For regular SaveChanges, EF Core already batches inserts, updates, and deletes up to the provider's limit (around 42 commands per round trip on SQL Server, depending on parameter count). No special call is needed; calling SaveChanges once at the end of a unit of work is faster than calling it after each entity. The opposite is the bug: calling SaveChanges in a loop turns one round trip into N.

ExecuteUpdate on 100,000 rows is one round trip and one transaction. Loading them, mutating, and calling SaveChanges is at least 100,000 reads plus several thousand batched writes. The bulk version is often 100x faster end-to-end.

A query plan can be fine and still be slow because the database has no way to find the requested rows. Indexes are how the database avoids scanning the whole table. EF allows declaring indexes in the Fluent API, and the migration command translates them into CREATE INDEX statements.

The four declarations cover four common shapes. A single-column index supports lookups and joins by CustomerId. A composite index supports queries that filter on Status and order by PlacedAt; the column order matters and follows the leftmost-prefix rule. A unique index enforces uniqueness on Email at the database level, so two customers can't share an email even under concurrent inserts. A filtered index narrows the index to a subset of rows; the example above only indexes pending orders, which is much smaller than indexing every order ever placed.

Index design is its own topic, but two rules apply almost everywhere. Add indexes on the columns in the WHERE clause. Add composite indexes when filtering on column A and sorting by column B in the same query.

For predicate logic that's hard to express in LINQ, EF Core exposes server-side functions through the EF.Functions extension. Like translates to SQL LIKE with wildcard support that string.Contains doesn't offer:

Date arithmetic is another case. SQL Server's DATEDIFF runs server-side and uses an index if one exists; calling it through EF.Functions.DateDiffDay is the safe way to access it.

The opposite mistake is computing date math in C# and stuffing the result into the predicate. That works for a constant comparison, but anything more complex won't translate, and EF either falls back to client evaluation (slow, unexpected) or throws.

A missing index on a WHERE column forces a full table scan. On a million-row table, that's a million reads per query; with the index, it's typically tens to hundreds depending on selectivity.

DbContext is cheap to construct but not free. Each instance allocates change-tracking state, model metadata references, and an internal service provider. For a web app handling thousands of requests per second, recreating the context per request adds up. AddDbContextPool keeps a pool of cleaned, reusable instances, and each request rents one for the lifetime of the scope.

Pooling is a drop-in upgrade for ASP.NET Core apps; the DI lifetime is still scoped, but the underlying instance comes from a pool. The one caveat is that the context can't store per-request state in fields, because that state will leak into the next request that rents the same instance. A context constructor that does anything beyond accepting DbContextOptions needs careful review before pooling.

A handful of anti-patterns come up often enough to call out explicitly:

Sharing a DbContext across threads. DbContext is not thread-safe. Running two queries in parallel on the same instance with Task.WhenAll throws InvalidOperationException. For parallel queries, use two contexts.

Calling `.ToList()` mid-query. This switches the rest of the LINQ chain from server evaluation to client evaluation without warning. The whole table loads into memory and filters in C#.

`.Count()` after `.ToList()`. Same shape, different cost. Materializing a list to count it allocates the entire list. Use CountAsync directly.

Using `Find` when tracking isn't needed. Find always tracks. For a read-only lookup, AsNoTracking().FirstOrDefaultAsync is the right pattern.

Forgetting `await` on async query methods. ToListAsync without await returns a Task<List<T>>, which compiles but behaves nothing like the synchronous list. The compiler warns; pay attention.

A .ToList() mid-query on a 100,000-row table can transfer 50+ MB across the wire for a result the database could have returned in 5 KB. The bug looks small in code and is enormous at runtime.