Loading related data without an N+1 loop

Recognize N+1 as an operation pattern, not a proxy requirement

N+1 means one initial query followed by additional work per returned parent. Lazy loading can create that pattern invisibly, but an explicit CountAsync inside a loop creates it just as clearly. This lesson deliberately uses no lazy-loading package so the extra requests are visible in source. A report can contain correct totals while performing avoidable repeated commands. Conversely, a smaller command count is not automatically the fastest plan for every result size. First observe which commands actually run, then choose a result shape appropriate to the caller rather than assuming a navigation access is always free.

Count reader commands only after controlled setup

ReadCounter is an actual DbCommandInterceptor implementing synchronous and asynchronous reader interception. The fixture resets its count after schema creation and seeding, so setup commands do not contaminate the observation. Reading two parents takes one command, and two separate post counts make three. The second experiment resets again and projects each blog’s name with Posts.Count, allowing one translated relational query to return A:2 and B:1. Assertions compare both output values and reader counts. This scope counts the commands the test observes; it is not a packet counter, an execution-time benchmark or a promise that every query shape uses one command.

using System;
using System.Linq;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

await using var connection = new SqliteConnection("Data Source=:memory:;Foreign Keys=True");
await connection.OpenAsync();
var counter = new ReadCounter();
var options = new DbContextOptionsBuilder<LabDb>().UseSqlite(connection).AddInterceptors(counter).Options;
await using var db = new LabDb(options); await db.Database.EnsureCreatedAsync();
db.Blogs.AddRange(new Blog { Name = "A", Posts = { new Post { Title = "One" }, new Post { Title = "Two" } } }, new Blog { Name = "B", Posts = { new Post { Title = "Three" } } }); await db.SaveChangesAsync(); db.ChangeTracker.Clear();
counter.Reset(); var parents = await db.Blogs.AsNoTracking().OrderBy(b => b.Name).ToListAsync(); var repeated = new List<string>();
foreach (var blog in parents) repeated.Add(blog.Name + ":" + await db.Posts.CountAsync(p => p.BlogId == blog.Id));
Check(counter.Reads == 3 && string.Join(",", repeated) == "A:2,B:1", "N+1 observations"); Console.WriteLine("loop-commands:" + counter.Reads);
counter.Reset(); var rows = await db.Blogs.OrderBy(b => b.Name).Select(b => new { b.Name, Count = b.Posts.Count }).ToListAsync();
Check(counter.Reads == 1 && string.Join(",", rows.Select(x => x.Name + ":" + x.Count)) == "A:2,B:1", "projection command and values"); Console.WriteLine("projection-commands:" + counter.Reads);

static void Check(bool condition, string name) { if (!condition) throw new InvalidOperationException("CHECK: " + name); }

The arithmetic model describes growth but cannot measure EF

The .NET9 practice receives a parent count of two and prints one plus two for a per-parent plan, versus one for a supplied projected-plan assumption. Those values are arithmetic on the declared plan, not intercepted SQL. The model can help a learner predict how repeated work grows at a thousand parents, but it cannot discover query batching, a provider rewrite or a lazy-loading event. The real external project supplies separate command evidence. Keep that division when writing tests: independently specified expected report values and actual provider diagnostics can reveal problems that either a formula or a value assertion alone would miss.

Include and split queries need their own cost model

Eager loading is not synonymous with one cheap query. A large joined graph can duplicate principal columns, while split queries use several commands and introduce consistency considerations. Filtered Include and tracking fixup can also make a result differ from a simplistic expectation if the context already contains related entities. The classroom fixture clears seeding state and projects only the needed scalar counts. A production choice should account for row volume, columns, indexes, latency and transaction requirements. Avoid optimizing only the number of commands while ignoring a much larger transferred or materialized result.

Practice: compare a graph load with the scalar report

Add a third blog with no posts and verify that both the loop and projected report preserve its zero count. Then create a separate Include-based query returning actual posts and inspect its command count and result shape. Compare that with a split-query variant where supported, without calling either universally faster from this tiny fixture. Reset the interceptor before each measured operation and keep seeding outside the interval. If the application needs only counts, justify why it would materialize all post entities. The experiment should answer a concrete caller requirement rather than reward an arbitrary smallest transcript.

• Hint: reset diagnostics after setup and before the operation under observation.
• Hint: compare row shape and volume as well as command count.

Practice: specify the report independently of its loading strategy

Build a portable expected report from supplied parent labels and supplied counts, including an empty parent set and a zero-count parent. Keep the expected values separate from the implementation that loads them. Add a proposed command budget as another assertion rather than deriving it from the same loop that produces the report. The arithmetic model should remain ordinary BCL data and avoid inventing DbSet or SQL classes. When the real adapter changes its loading strategy, it should still satisfy the same report contract while its separately observed command behavior can be reviewed on the actual provider.

• Hint: correct output and efficient data access are distinct assertions.
• Hint: a model of a proposed command plan is not a measurement of execution.

Record which behavior the diagnostic actually observed

The external fixture verifies three reader commands for its explicit per-parent loop and one for its scalar projection, with equal materialized totals. That concrete evidence is valuable without becoming a claim about production latency or another provider. The .NET9 practice remains a growth calculation. When handing off a query change, describe the required result, tracking context, materialization point, diagnostic scope and remaining performance uncertainty. Then measure representative data in the intended environment. That is stronger than simply replacing a loop with a compact expression and assuming that familiar LINQ syntax guarantees a particular relational execution plan.