The save that affects zero rows
This is my favourite bug in the entire stack, because it is invisible. The code is correct. The test passes. Nothing throws. The row simply does not change.
var order = await _repo.GetAll(o => o.Id == id)
.FirstOrDefaultAsync();
order.Status = OrderStatus.Shipped;
await _uow.SaveChangesAsync(); // returns 0. no exception.
The cause is a default that disagrees with itself. An interface declares a default value for an optional parameter; the implementation declares a different one; and C# binds optional parameter defaults at the call site, from the compile-time type of the reference. Call through the interface and you get the interface's default. Call through the concrete class and you get the other one.
public interface IRepository<T>
{
IQueryable<T> GetAll(
Expression<Func<T, bool>> filter = null,
bool tracked = false); // <- the one you get
}
public class Repository<T> : IRepository<T>
{
public IQueryable<T> GetAll(
Expression<Func<T, bool>> filter = null,
bool tracked = true) // <- the one you read
{ ... }
}
So the entity comes back from a AsNoTracking() query, the change tracker has never heard
of it, and SaveChanges correctly does nothing at all. You read the implementation, you
see true, and you spend an hour wondering why the database disagrees with you.
SaveChangesAsync() returning 0 when you expected 1 is almost always this. Check it before
you check anything else — it costs five seconds and it is right most of the time.
Two fixes, and you want both. Make the defaults agree, and be explicit at every call site that intends to write:
var order = await _repo
.GetAll(o => o.Id == id, tracked: true)
.FirstOrDefaultAsync();
The deeper lesson is that optional parameters are part of the signature in a way that feels like it should be part of the implementation, and is not. If a default matters for correctness, do not make it optional.
Count and Count(), which are not the same
Two characters, and one of them silently returns zero.
// Property: the in-memory count of a loaded collection.
CommentCount = thread.Comments.Count
// Method: translated to a correlated subquery in SQL.
CommentCount = thread.Comments.Count()
Inside a Select projection, the method is translated by the provider into
(SELECT COUNT(*) FROM Comments WHERE ...) and the database answers it. The property is a
plain CLR member on ICollection<T>. If the navigation was never
Included, the collection is empty, and the property cheerfully reports 0
— no error, no warning, no query.
The result is a dashboard where every thread has zero comments, which gets reported as "comments are broken" long after the deploy that caused it.
.Count(), .Any(), .Sum() —
the methods translate. The properties lie quietly.
N+1, and the fix that is worse
Everyone knows the N+1: one query for the list, then one more per row as a lazy navigation is touched in a loop. Fifty rows, fifty-one round trips, each one carrying network latency that your local machine — where the database is on localhost — does not have.
The textbook fix is Include. The textbook fix, applied to a page that needs four
collections, is how you get a different and more interesting outage.
// Looks thorough. Produces a cartesian product.
var cases = await _db.Cases
.Include(c => c.Parties)
.Include(c => c.Payments)
.Include(c => c.Documents)
.Include(c => c.Notes)
.Where(c => c.BusinessId == businessId)
.Skip(skip).Take(25)
.ToListAsync();
Each Include of a collection is a join, and joins multiply. A case with 4
parties, 10 payments, 6 documents and 5 notes does not return 25 rows. It returns 4 × 10 × 6 × 5 =
1,200 rows for that one case, with the case's own columns repeated in every one of
them. EF then de-duplicates it all in memory, which it is good at, and which does not help the eight
seconds you just spent moving the data across the wire.
AsSplitQuery is not the answer either
AsSplitQuery() turns one cartesian query into one query per collection, which removes the
multiplication. It also removes the single-statement consistency, and on a paginated list it still
drags every column of every related row into memory to build objects you are about to throw away.
What actually worked, on a page that was timing out: run the paginated query for the parent rows only, then issue one batch aggregate query per collection keyed by the ids you just got, and stitch them together in a dictionary.
// 1. the page itself — no collection Includes at all
var cases = await query
.OrderByDescending(c => c.CreatedOn)
.Skip(skip).Take(take)
.ToListAsync();
var ids = cases.Select(c => c.Id).ToList();
// 2. one query per aggregate, scoped to those ids
var paidByCase = await _db.Payments
.Where(p => ids.Contains(p.CaseId) && p.IsPaid)
.GroupBy(p => p.CaseId)
.Select(g => new { g.Key, Total = g.Sum(x => x.Amount) })
.ToDictionaryAsync(x => x.Key, x => x.Total);
var fileCount = await _db.Files
.Where(f => ids.Contains(f.CaseId))
.GroupBy(f => f.CaseId)
.Select(g => new { g.Key, Count = g.Count() })
.ToDictionaryAsync(x => x.Key, x => x.Count);
// 3. map, with the dictionaries passed in
return cases.Select(c => MapSummary(c, paidByCase, fileCount));
Four small round trips instead of one enormous one, a bounded result set regardless of how many payments a case has accumulated, and a page that renders in under a second on data that used to time out.
Project, do not load
The entity has 40 columns. The grid shows 6 of them. ToListAsync() on the entity reads
all 40, materialises objects, and registers every one with the change tracker so it can watch for
edits you are never going to make.
// Reads 40 columns, tracks 500 objects, uses 6 fields.
var rows = await _db.Cases.Where(filter).ToListAsync();
// Reads 6 columns, tracks nothing, allocates almost nothing.
var rows = await _db.Cases.Where(filter)
.Select(c => new CaseRow {
Id = c.Id,
Name = c.Name,
Status = c.Status,
Parties = c.Parties.Count(),
LastNote = c.Notes
.OrderByDescending(n => n.CreatedOn)
.Select(n => n.Text)
.FirstOrDefault()
})
.ToListAsync();
A projection is implicitly no-tracking, so you get that fix for free. And note the last field: a
correlated subquery for "the most recent note", resolved by the database, with no
Include and no extra round trip.
One trap if your project has nullable reference types enabled. A projection like
.FirstOrDefault() over a string is string?, so a dictionary built from it
is Dictionary<int, string?> and will not assign to
Dictionary<int, string>. Declare it as nullable and cast in the selector rather
than fighting it:
.ToDictionaryAsync(g => g.Key, g => (string?)g.FileName)
Paging that gets slower as you page
Skip(n).Take(25) compiles to OFFSET n ROWS FETCH NEXT 25, and
OFFSET does exactly what it says: the database produces the first n rows in
order and throws them away. Page 2 is fast. Page 400 reads ten thousand rows to return twenty-five.
For a UI with numbered pages this is usually fine, because nobody clicks page 400. For an export, a sync job, or an API that someone will iterate to the end, it is quadratic work, and it gets worse as the table grows.
Use a keyset instead — remember the last row you saw and ask for what comes after it:
// Page 1
var page = await _db.Cases
.OrderByDescending(c => c.CreatedOn).ThenByDescending(c => c.Id)
.Take(25).ToListAsync();
// Page n+1: seek, do not count
var page = await _db.Cases
.Where(c => c.CreatedOn < lastOn
|| (c.CreatedOn == lastOn && c.Id < lastId))
.OrderByDescending(c => c.CreatedOn).ThenByDescending(c => c.Id)
.Take(25).ToListAsync();
With an index on (CreatedOn DESC, Id DESC) every page costs the same, whether it is the
first or the ten-thousandth. The tiebreaker on Id is not optional: without it, two rows
sharing a timestamp can be skipped or repeated across the boundary.
Reading a plan without becoming a DBA
You do not need to understand every operator. You need to find four things, and they account for most of what goes wrong.
| What you see | What it means | Usually fixed by |
|---|---|---|
| Clustered Index Scan on a big table | Reading the whole table to find a few rows. | An index on the columns you filter by. |
| Key Lookup next to an Index Seek | The index found the row but not all the columns, so it goes back to the table for each one. | INCLUDE the missing columns in the index. |
| Estimated rows 1, actual rows 400,000 | Stale statistics, or a plan built for different parameters. | Update statistics; see parameter sniffing below. |
| A fat arrow into a Sort or Hash Match | Sorting or hashing more rows than fit in memory, spilling to tempdb. | Filter earlier, or index in the order you sort. |
Two practical notes. Get the actual plan, not the estimated one — the gap between estimate and actual is the single most useful number on the screen. And read the arrows, not the operators: the thickness is row count, and the fattest arrow is where your time went.
Parameter sniffing
A stored procedure or parameterised query is compiled once, using whatever parameter values happened to be supplied on the first execution, and that plan is cached and reused.
This is almost always a good thing. It is a disaster when your data is skewed. The first caller asks
for BusinessId = 4071, which has 12 rows, so SQL Server picks a plan with nested loops
and a key lookup — perfect for 12 rows. The next caller asks for the business with 4 million rows, and
gets the same plan, executed four million times.
The symptom is distinctive and confusing: the same query is fast for some users and catastrophic for others, and it swaps over after a restart or a statistics update.
OPTIMIZE FOR UNKNOWN, which asks for an average
plan instead of a plan tailored to one outlier. Then RECOMPILE on the specific query if
the skew is genuinely extreme and the query is not run thousands of times a second. Rewriting the
query comes before all of these if the skew is a design problem.
The checklist
What I actually check, in the order I check it, when something is slow or wrong.
- Did the save affect zero rows? Tracking. Almost always tracking.
- Is there a
.Countwithout parentheses in a projection? It is returning 0 and nobody has noticed. - How many collection
Includes are on the list query? More than one is a multiplication; replace them with batch aggregates. - Is the query selecting entities where a projection would do? Forty columns for a six-column grid is forty columns of bandwidth and a change tracker full of nothing.
- Is anything iterating to the end with
Skip? Keyset it. - Turn on query logging in development. Most of these are obvious the moment you can see the SQL, and invisible for months when you cannot.
// Worth its weight. Development only.
options.UseSqlServer(cs)
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging();