This EF Core query is slow. How do you work out why, and what does change tracking have to do with it?

Look at the SQL first

The only defensible first move is to see what was actually sent to the database. ToQueryString() on an IQueryable gives you the SQL for a single query without executing it, and configuring LogTo with EnableSensitiveDataLogging in a development environment gives you the commands with parameter values as they run. Until you have that text you are guessing, and EF Core query performance questions are lost far more often by guessing than by not knowing the API surface.

What the SQL tells you is which of three quite different problems you have. One statement that is slow on the server is a database problem: missing index, bad plan, a predicate that is not sargable. One statement that returns far more data than the screen needs is a projection problem. Many near-identical statements differing only by a parameter is an N+1, and that is a loading-strategy problem. Change tracking is a fourth, separate axis, and it is the one candidates most often reach for first even though it is rarely the largest cost.

What tracking costs you

A tracking query does more than hand you objects. For every entity it materialises, the ChangeTracker creates an entry and takes a snapshot of the original values of every mapped scalar property, so that SaveChanges can later work out what changed. That snapshot roughly doubles the memory footprint of the loaded data. It also performs navigation fix-up: as each entity enters the tracker, EF Core wires up references between it and any already-tracked related entities by matching foreign keys, which is work proportional to what is already in the context. And when you call SaveChanges, DetectChanges walks every tracked entry comparing every property against its snapshot.

None of that is wasted if you intend to modify the entities. All of it is wasted on a read that feeds a JSON response or a report. For a query returning ten rows the cost is unmeasurable; for one returning fifty thousand it is the difference between a request that allocates a few megabytes and one that allocates tens of megabytes and spends real time in GC. The second failure mode matters more in long-lived contexts: a context that has already loaded a large graph makes every subsequent fix-up more expensive, which is one reason a DbContext should be scoped to a unit of work rather than kept alive.

// Tracked: full entities, original-value snapshots, fix-up, DetectChanges later.
var orders = await db.Orders.Where(o => o.CustomerId == id).ToListAsync();

// No tracking: no entry, no snapshot, no fix-up. Still fetches every column.
var readOnly = await db.Orders.AsNoTracking()
    .Where(o => o.CustomerId == id).ToListAsync();

// Projection: no tracking (the DTO is not an entity type) AND narrower SQL.
var dto = await db.Orders
    .Where(o => o.CustomerId == id)
    .Select(o => new OrderSummary(o.Id, o.PlacedOn, o.Customer.Name))
    .ToListAsync();

AsNoTracking() is the cheap win, and QueryTrackingBehavior.NoTracking set on the context makes it the default if the context is read-only by design. But it only removes bookkeeping. It does not stop EF Core selecting every column of every entity, and it does not stop an Include fanning rows out. A Select into a DTO or record does both: results projected to a non-entity type are not tracked at all, and the SQL narrows to the columns you named, so less data crosses the wire and less is materialised. That is why the projection, not AsNoTracking(), is the answer that earns credit.

One caveat worth volunteering. No-tracking queries do not perform identity resolution, so if the same row appears more than once in the result set you get separate object instances rather than one shared instance. AsNoTrackingWithIdentityResolution() restores that behaviour by keeping a lookup for the duration of the query, at some cost, without tracking for updates.

The N+1 that lazy loading hands you

With lazy-loading proxies enabled and navigations declared virtual, reading a navigation property that has not been loaded triggers a database round trip at the moment of access. Inside a loop, that is one query per iteration:

var orders = await db.Orders.ToListAsync();     // 1 query
foreach (var o in orders)
{
    // Each access to an unloaded navigation issues its own SELECT.
    Console.WriteLine(o.Customer.Name);
}

The logs make it unmistakable: the same SELECT against Customers repeated with a different parameter, hundreds of times, each one fast and the total catastrophic. The fix is to state the shape of the data you want up front, either with Include when you need the entities or with a projection that navigates in the expression so the join happens in SQL. Note also that lazy loading depends on the entity being attached to a live context, so it does not work on entities returned from a no-tracking query and it throws once the context is disposed, which is where "why is this collection empty" bugs come from.

Where the EF Core 2.x folklore misleads you

There is a widely repeated claim that if EF Core cannot translate part of your query it will quietly fetch the whole table and finish the work in memory. That was true of EF Core 1.x and 2.x, where client evaluation was permitted anywhere in the query and only produced a warning, and it caused exactly the disasters people remember. It has not been true since EF Core 3.0. Client evaluation is now allowed only in the top-level projection; anything else that cannot be translated, including a Where predicate calling a method EF Core does not understand, throws an InvalidOperationException telling you the expression could not be translated.

That changes the diagnosis in a way that matters. On any currently supported version a call the provider cannot translate is a loud failure at development time, not a silent performance cliff, so it is not on the list of explanations for a query that runs but runs slowly. Saying otherwise signals that your knowledge stopped at EF Core 2.x. The real remaining version of the problem is subtler: you force evaluation into memory yourself, by calling ToList() or AsEnumerable() earlier in the chain than you meant to and then filtering the materialised results with LINQ to Objects. That compiles, runs, and does pull the table, and it is visible in the SQL as a query with no WHERE clause where you expected one.

Order of attack

Get the SQL. If there is one statement and the database is slow, it is an indexing or plan problem and no amount of LINQ rewriting helps. If the statement returns columns or rows nobody uses, project. If there are many statements, kill the lazy loading. Then, and only then, remove tracking on the read paths, because it is a real but usually secondary cost. Measure after each step against a realistic data volume, since every one of these problems is invisible on a developer's hundred-row database.