EF Core and SQL Server: the queries that kill you in production

None of these are exotic. They pass code review, they work on your machine, and they fail on real data — usually at the worst possible moment, and usually without throwing anything you can search for.

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.

The tell 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.

Rule Inside a projection, always call 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.

ONE QUERY, FOUR COLLECTION JOINS case 1 row parties 4 payments 10 docs 6 notes 5 1,200 rows 4 × 10 × 6 × 5, per case × 25 cases on the page = 30,000 rows across the wire FOUR QUERIES, ONE PER COLLECTION case page 25 rows payment sums by case id counts by case id 25 + 25 + 25 rows joined in a dictionary a sum, not a product
The same page, the same data. The difference is whether the collections multiply or add, and that is decided by how many round trips you are willing to make.

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.

The shape to remember For a list view you almost never want the related rows. You want a number about them. Ask the database for the number.

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.

What to reach for, in order Make sure statistics are current. Then 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 .Count without 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();
The habit that catches most of it Look at the generated SQL for every list endpoint you write, once, before it ships. Not the plan, not a profiler — just the statement. Nearly every bug above is visible in it immediately, and none of them is visible in the C#.

← All articles