Pops Racer built the Mach 5 so that Speed never has to think about it. There's a button on the steering wheel for everything — A drops the jacks, B swaps to grip tires, C puts rotary blades through whatever is in the way. Speed pushes a button and the car handles it.
What that steering wheel doesn't have is a gauge for what the engine is actually doing. On an empty practice track, that never once matters.
A list endpoint that returns in 40 milliseconds.
You wrote it, you ran it, you watched it come back instantly. You wrote a test. The test passed. Code review had no notes, because there is nothing to have a note about — it's twelve lines of clean LINQ that reads exactly like what it does.
In production it takes nine seconds and fires 2,001 queries at the database to do it.
Nobody wrote 2,001 queries. EF Core wrote them. And EF Core wrote them on your machine too — it fired six, they came back in 40 milliseconds combined, and you never had a reason to look.
That's the whole problem. Your local database has five rows in it.
Every failure in this post is invisible at five rows and fatal at two million. Not "slower" — invisible. Same code path, same SQL, same test suite going green, producing a number so small you'd never think to question it. Your development environment isn't a smaller version of production. It's an empty track. It agrees with you about everything.
🎯 The Shift
From: "It's fast on my machine"
To: "My machine has five rows and no opinion"
I've spent a lot of time on systems pushing millions of healthcare claims, and EF Core performance work almost never starts with a clever query. It starts with someone finally turning on logging and going quiet for a minute.
The pattern is always the same. Nothing is slow in development, ever. Nothing is slow in staging either, because staging has a copy of dev's data. Production is slow in a way nobody can reproduce. Then someone watches the SQL and finds the application asking for the same row four hundred times.
🔍 1. Make Development Stop Lying
Most EF Core advice starts with query patterns. That's backwards. You cannot fix what you cannot see, and by default you cannot see anything — hiding the database is EF Core's entire value proposition, and it is very good at its job.
So before any of the patterns below: fit the gauge Pops left off the dashboard.
ToQueryString() returns a query's SQL without executing it. No configuration required:
var query = _context.Claims.Where(c => c.Status == ClaimStatus.Submitted);
Console.WriteLine(query.ToQueryString());
Breakpoint on any IQueryable you're suspicious about, read it in the watch window. That answers most questions before you've configured anything.
For everything else, turn on logging where it can't follow you to production — appsettings.Development.json, not appsettings.json:
{
"Logging": {
"LogLevel": {
"Microsoft.EntityFrameworkCore.Database.Command": "Information"
}
}
}
Every tutorial also reaches for EnableSensitiveDataLogging(). It swaps @__p_0 for the real value, which is genuinely useful — and it is precisely a switch that writes your parameter values into your logs. If those parameters are patient identifiers or password reset tokens, that's plaintext on disk, wherever your logs go, forever. Development only, behind an environment check.
🧮 Count the queries, don't eyeball them
A wall of scrolling SQL tells you something is wrong. It doesn't tell you what changed, and you can't put it in a test. Count instead:
public sealed class QueryCountingInterceptor : DbCommandInterceptor
{
private int _count;
public int Count => Volatile.Read(ref _count);
public void Reset() => Interlocked.Exchange(ref _count, 0);
public override InterceptionResult<DbDataReader> ReaderExecuting(
DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
{
Interlocked.Increment(ref _count);
return base.ReaderExecuting(command, eventData, result);
}
public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
DbCommand command, CommandEventData eventData,
InterceptionResult<DbDataReader> result, CancellationToken ct = default)
{
Interlocked.Increment(ref _count);
return base.ReaderExecutingAsync(command, eventData, result, ct);
}
}
That counts reads — and, on a path that saves, a little more than you might expect. An insert with an identity key or a computed column has to read the generated values back, so it executes through ExecuteReader and lands on the override above. Batches that need nothing back go through NonQueryExecuting instead. Override that one too for a complete tally, and assert your counts on read paths, where the distinction never comes up.
Register the instance you intend to assert on — scoped, so the interceptor the test resolves is the one the context just used:
services.AddScoped<QueryCountingInterceptor>();
services.AddDbContext<ClaimsContext>((sp, options) => options
.UseSqlServer(connectionString)
.AddInterceptors(sp.GetRequiredService<QueryCountingInterceptor>()));
Resolve it in the test alongside the context, and Reset() and Count are talking about the same tally the query went through:
[Fact]
public async Task GetRecentClaims_RunsExactlyOneQuery()
{
_queryCounter.Reset();
await _service.GetRecentClaimsAsync(CancellationToken.None);
Assert.Equal(1, _queryCounter.Count);
}
That test is the point of this post. It fails the moment somebody reintroduces an N+1 — in CI, on a five-row fixture — because query count doesn't depend on row count. The bug that was invisible at five rows is now the only thing that isn't.
Finally, turn the warning you'll ignore into an exception you can't:
optionsBuilder.ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning));
That's §3's cartesian explosion, caught at the keyboard instead of logged where nobody reads it.
🔁 2. The N+1 You Were Taught Doesn't Compile
Everyone learns N+1 from the same example:
var claims = _context.Claims.ToList();
foreach (var claim in claims)
Console.WriteLine($"{claim.Id} — {claim.Patient.Name}"); // "one query per claim"
Here's what trips people up: on a default EF Core setup, that doesn't happen. It throws a NullReferenceException, because Patient is null and nothing went to fetch it.
Lazy loading is off by default. Getting that behavior takes installing Microsoft.EntityFrameworkCore.Proxies, calling UseLazyLoadingProxies(), and marking navigations virtual. It's a carry-over from EF6, where lazy loading was the default — the tutorials were written then and copied forward.
That matters more than a pedantic correction, because a lot of developers are watching for a failure mode their framework doesn't have, and missing the ones it does:
- Somebody turned proxies on, usually to fix a
NullReferenceExceptionexactly like that one. The crash went away, which is the worst outcome — the bug converted from loud to silent. Includeinside a loop — each iteration a fresh query with a fresh join.awaitinside aforeach— sequential round trips wearing async clothing.- A repository returning
IEnumerable<T>. The signature quietly ends the query, so every caller that filters afterwards does it in memory, after the whole table has arrived. It looks like good encapsulation:
// ❌ IQueryable becomes IEnumerable at the boundary — filtering now happens in C#
public IEnumerable<Claim> GetAll() => _context.Claims;
var submitted = _repo.GetAll().Where(c => c.Status == ClaimStatus.Submitted);
The fix for all of them is the same — decide what you need before you enumerate, and get it in one trip:
// ✅ one query
var claims = await _context.Claims
.Include(c => c.Patient)
.Where(c => c.Status == ClaimStatus.Submitted)
.ToListAsync(ct);
Query count: 2,001 → 1. The same number on your five-row database as in production, which is exactly why it's worth asserting on.
That
IQueryable→IEnumerableboundary deserves more room than it gets here. The next post — LINQ Pitfalls: The Query That Ran Twice — takes it apart properly.
💥 3. Include Fixes N+1 and Then Creates a New Problem
So you fixed it with Include, and naturally you kept going:
// ❌ two collection includes
var claims = await _context.Claims
.Include(c => c.LineItems)
.Include(c => c.Attachments)
.ToListAsync(ct);
One query — and a cartesian product. A claim with 10 line items and 10 attachments returns 100 rows, the same claim repeated across every combination. EF materializes 10 and 10 correctly, but the database read, sent and paid for 100. Free at five rows. The whole problem at two million.
AsSplitQuery() trades one bloated round trip for several lean ones. Which wins depends on your data — split queries mean more round trips, and no consistency guarantee across them: a concurrent write between the two can give you a parent and children that never existed together. If that matters for your domain, wrap them in a serializable or snapshot transaction; a plain one at the default isolation level won't help. The tell is row multiplication: one collection include is fine as a join, two start multiplying, and it's the product of the sizes, not the sum.
One correctness note before you reach for it, and this one returns wrong data rather than slow data. A split query combined with Skip/Take needs fully unique ordering. Order by SubmittedUtc alone and two claims sharing a timestamp can sort differently in each of the split queries, so the children you get back belong to a parent you didn't fetch. Add a tiebreaker — .OrderByDescending(c => c.SubmittedUtc).ThenBy(c => c.Id). Relational databases apply no ordering by default, not even on the primary key.
The cartesian half you don't have to remember — you turned that warning into an exception in §1.
✂️ 4. Ask For The Columns You Use
Include pulls entire entities — every column, materialized into tracked objects, including the six nvarchar(max) fields nobody is looking at. Project instead:
// ✅ SQL selects four columns, materializes no entities
var summaries = await _context.Claims
.Where(c => c.Status == ClaimStatus.Submitted)
.Select(c => new ClaimSummaryDto
{
Id = c.Id,
PatientName = c.Patient.Name,
Amount = c.Amount,
SubmittedUtc = c.SubmittedUtc
})
.ToListAsync(ct);
No Include at all — c.Patient.Name inside a projection tells EF to join and take one column.
🗺️ The mapper that undoes all of it
Projection only helps if it's the last thing that touches the query. This version looks identical in review and isn't:
// ❌ the DTO is correct and the SQL is still SELECT *
var claims = await _context.Claims
.Where(c => c.Status == ClaimStatus.Submitted)
.ToListAsync(ct);
return claims.Adapt<List<ClaimSummaryDto>>();
ToListAsync ran first. Every column of every row came back, materialized into tracked entities, and then got mapped down to four fields. Mapping after materialization is a formatting step — the database already paid for everything you threw away.
Hand the mapper the IQueryable instead, and it builds the projection into the query:
// ✅ Mapster writes the Select, EF translates it, the database sends four columns
return await _context.Claims
.Where(c => c.Status == ClaimStatus.Submitted)
.ProjectToType<ClaimSummaryDto>()
.ToListAsync(ct);
AutoMapper's equivalent is ProjectTo<T>(_mapper.ConfigurationProvider). Same rule either way: project on the IQueryable, not on the List.
One thing to check before that package lands in a commercial project: AutoMapper moved to a commercial licence in 2025, alongside MediatR from the same maintainer. The Apache-2.0 versions you remember are still out there, but anything current is governed by the new terms — read them and the revenue threshold before you take the dependency, rather than after. Mapster is MIT and does the same job in this post.
And the tell is in the log, not the signature — a method returning List<ClaimSummaryDto> tells you nothing about what the database was asked for.
One caveat: queryable projection only covers what your provider can translate, so keep the configs you project with simple — arbitrary C# in a mapping profile won't survive the trip to SQL.
📖 5. Read Paths Don't Need Change Tracking
By default EF snapshots every entity it materializes so SaveChanges can work out what moved. On a read path that produces nothing and costs memory proportional to what you loaded.
var claims = await _context.Claims
.AsNoTracking()
.Where(c => c.Status == ClaimStatus.Submitted)
.ToListAsync(ct);
Projections to a DTO aren't tracked anyway, so AsNoTracking() there is redundant. If you need repeated entities deduplicated into one object, AsNoTrackingWithIdentityResolution() does that without the tracker.
The failure mode is loading no-tracking and then trying to update, which silently does nothing. If you meant to write, don't say no-tracking.
📏 6. Never Return An Unbounded Set
return await _context.Claims.ToListAsync(ct); // ❌ fine on your machine
That's every claim your organization has ever processed, materialized into memory and serialized to JSON. Five rows in dev; two million in production, and the instant endpoint is now an outage.
Skip/Take bounds it, and degrades on deep pages — the database still walks everything it skipped. For deep paging, remember the last key you saw:
// ✅ keyset paging — the database seeks instead of counting past
var page = await _context.Claims
.AsNoTracking()
.Where(c => c.SubmittedUtc < lastSeenUtc)
.OrderByDescending(c => c.SubmittedUtc)
.Take(pageSize)
.ToListAsync(ct);
Same caveat as §3 applies here, and the predicate is the half people get wrong. If SubmittedUtc isn't unique, the tiebreaker has to appear in both the ordering and the comparison, or rows sharing a timestamp straddle the page boundary and get skipped or repeated:
// ✅ compound keyset predicate — everything strictly past (lastSeenUtc, lastSeenId)
.Where(c => c.SubmittedUtc < lastSeenUtc
|| (c.SubmittedUtc == lastSeenUtc && c.Id < lastSeenId))
.OrderByDescending(c => c.SubmittedUtc).ThenByDescending(c => c.Id)
And watch for the second query you didn't write: a total count for a page control is another full aggregate on every request. Cache it, approximate it, or design a UI that doesn't need it.
📇 7. The Index Nobody Added
Every fix so far lives in your C#. This one doesn't, which is exactly why it survives code review — it isn't a problem with the car, it's a problem with the track.
var claims = await _context.Claims
.AsNoTracking()
.Where(c => c.ProviderId == providerId && c.Status == ClaimStatus.Submitted)
.OrderByDescending(c => c.SubmittedUtc)
.Take(50)
.Select(c => new ClaimSummaryDto { /* ... */ })
.ToListAsync(ct);
Projected, untracked, bounded, one round trip. It is also a full table scan, because nothing told the database that ProviderId and Status are how this table gets searched.
The C# has no defect. The reviewer has nothing to point at. And the query counter from §1 reads 1, which is the correct answer to the question it asks.
This is the failure only production can teach you, because a table scan across five rows and a table scan across two million rows are the same code, and one of them takes 40 milliseconds.
modelBuilder.Entity<Claim>()
.HasIndex(c => new { c.ProviderId, c.Status, c.SubmittedUtc });
Column order follows how you filter: equality predicates first, then the range or sort column. An index on SubmittedUtc alone will not help that query.
Order matters among the equality columns too, though not for this query — supply both predicates and either order seeks fine. It matters because the index also serves queries supplying only a prefix: (ProviderId, Status) helps a search on ProviderId alone and cannot seek on Status alone. Lead with the column you also query by itself.
🧱 8. The Predicate That Can't Use It
You added the index. The query is still scanning.
An index is an ordered structure the database seeks into, and it can only do that if the column appears in your predicate bare. Wrap it in a function and the database must compute that function for every row before it knows whether the row matches — an ordering of the raw values is no longer any help.
// ❌ LOWER() applied per row — the index on ProviderCode is unusable
.Where(c => c.ProviderCode.ToLower() == code.ToLower())
On SQL Server's default collation that comparison was already case-insensitive. The ToLower() was never doing anything except disabling the index.
.Where(c => c.ClaimNumber.Contains(term)) // ❌ '%term%' — no anchor, so: index scan
.Where(c => c.ClaimNumber.StartsWith(term)) // ✅ 'term%' — anchored, so: index seek
.Where(c => c.SubmittedUtc.AddDays(30) < now) // ❌ computed per row
.Where(c => c.SubmittedUtc < now.AddDays(-30)) // ✅ computed once, compared to a bare column
Same result set, same row count, same query count. One of them can seek.
The rule fits on one line: keep the column bare on one side of the comparison, and do the arithmetic to the parameter.
Note where this leaves your test suite. The query counter reads 1. The projection is right, the tracking is right, the paging is right. Query count is a good proxy for a whole class of bugs and completely blind to this one.
So fix them in order: round trips, then columns, then the plan. Compiled queries and compiled models are real, they're small, and they will not save you from an N+1.
🎓 Why These Patterns Matter
Notice what every section had in common: not one of them is a subtle bug. Every one is obvious the moment you can see it, and undetectable until then.
Team A has EF Core doing its job perfectly. The database is invisible. Nobody has seen the SQL their application generates, because there was never a reason to look. Every performance bug they will ever have is already written, already merged, already passing its tests, waiting for a row count.
Team B printed the SQL on day one. Their tests assert query counts, so an N+1 fails in CI on a five-row fixture. MultipleCollectionIncludeWarning throws in development. When something is slow they read the execution plan instead of guessing.
Same framework, same LINQ. One team finds these at the keyboard; the other finds them from a dashboard at 2am.
Your ORM isn't the problem. Your ORM doing its job — hiding the database — is the problem, and the fix is to stop letting it.
🧭 Key Takeaways
- Instrument first —
ToQueryString()for one query,Microsoft.EntityFrameworkCore.Database.CommandatInformationfor all of them - Assert on query counts, not timings — counts don't depend on row count, so an N+1 fails in CI on a five-row fixture
- Lazy loading is off by default — the textbook N+1 throws instead. Real sources: proxies someone enabled,
Includein a loop,awaitin aforeach, repositories returningIEnumerable<T> Includefixes N+1 and can cause cartesian explosion — two collection includes multiply rows;AsSplitQuery()trades round trips for row count- Project on the
IQueryable, not theList—ProjectToType/ProjectTo, neverAdaptafterToListAsync AsNoTracking()on read paths, never on a path that intends to write- Bound every result set, and remember the count query you didn't write
- Keep the column bare — a function on the column disables the index
- Never leave
EnableSensitiveDataLogging()on outside development
🚀 Next Steps
- Turn on SQL logging in
appsettings.Development.jsontoday. Load your busiest page and count the statements. That number is the post. - Add the query-counting interceptor and write one test asserting a count on your most-used endpoint.
- Turn
MultipleCollectionIncludeWarninginto an exception in development. - Find every repository method returning
IEnumerable<T>where the caller filters afterwards, and everyAdaptthat follows aToListAsync. - Take your slowest production query and read its execution plan. If the C# was already correct, the answer is an index — or a predicate that can't use one.
Start with the endpoint you'd be most embarrassed to have profiled. That's where the round trips are.
The Mach 5 was never the problem. Speed just never ran it anywhere the engine had to answer for itself. Fit the gauge.
Related Posts:
- Building Professional WebAPI Controllers — DTOs and pagination at the boundary
- Async/Await — Don't Block Your Threads — the post that promised this one
- Unit Testing with NSubstitute — why the query-count test asserts on outcomes, not internals
In the next post: LINQ Pitfalls — the query that ran twice. Deferred execution, multiple enumeration, and the IQueryable → IEnumerable boundary that quietly drags an entire table into memory. Every trap in it reproduces with a List<T> and no database at all.