Entity Framework Training: Diagnose and Fix EF Core Query Costs

Entity Framework Training Starts with a Query-Cost Baseline

A useful entity framework training exercise is to measure a request before changing its LINQ. This is relevant across .net core training, csharp training, a production-focused c# course, asp.net core training, microsoft technologies training, and blazor training: record SQL command count, database duration, returned row count, and allocated bytes for one representative endpoint. Run dotnet-counters monitor --process-id <pid> System.Runtime during a repeatable load test, then compare allocation rate and GC counts after each query change; lower database time with a sharp allocation increase is not automatically a win.

Enable EF Core command logging only in a development or controlled staging environment, then tag the LINQ query so that a trace can be tied to an endpoint. TagWith becomes an SQL comment, which is particularly useful in SQL Server Query Store, PostgreSQL pg_stat_statements, and application logs. Do not enable EnableSensitiveDataLogging() in production: parameter values can include email addresses, tokens, or tenant identifiers.

builder.Services.AddDbContext<StoreDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("Store"))
           .EnableDetailedErrors()
           .LogTo(Console.WriteLine,
               new[] { DbLoggerCategory.Database.Command.Name },
               LogLevel.Information));

var page = await db.Orders
    .TagWith("GET /api/orders dashboard")
    .Where(o => o.TenantId == tenantId)
    .OrderByDescending(o => o.CreatedAt)
    .Take(50)
    .Select(o => new { o.Id, o.CreatedAt, o.Total })
    .ToListAsync(ct);

Use the database plan, not only generated SQL, to identify the expensive operator. For SQL Server, capture the actual execution plan and compare estimated versus actual row counts; a 100x mismatch commonly points to stale statistics, parameter-sensitive plans, or correlated predicates. For PostgreSQL, execute EXPLAIN (ANALYZE, BUFFERS) ... with representative parameter values. A missing index is only one possibility: an index on (TenantId, CreatedAt DESC) is useful for the query above because it supports both the equality filter and ordered TOP operation, while an index on CreatedAt alone can still force broad filtering or sorting.

ASP.NET Core Training: Remove N+1 Queries with Explicit Projections

In an ASP.NET Core endpoint, the N+1 pattern often arrives through navigation-property access after the root query has completed. Turn lazy loading off for request DTO paths and project everything needed by the response in one LINQ expression. The projection below produces one SQL command without materializing tracked Order, Customer, and OrderLine entity graphs; AsNoTracking() also avoids snapshots that EF needs for change detection.

var orders = await db.Orders
    .AsNoTracking()
    .Where(o => o.TenantId == tenantId && o.Status == OrderStatus.Open)
    .OrderByDescending(o => o.CreatedAt)
    .Take(100)
    .Select(o => new OrderListItem(
        o.Id,
        o.Customer.Name,
        o.Lines.Sum(l => l.Quantity),
        o.Lines.Sum(l => l.Quantity * l.UnitPrice)))
    .ToListAsync(ct);

Add a command-count regression test around critical read paths by registering a DbCommandInterceptor that increments an AsyncLocal counter per request. Assert one command for the list endpoint above, or an intentionally documented number for split queries. This catches a later refactor such as orders.Select(o => mapper.Map(o)), where a mapper accesses an unloaded navigation and silently causes dozens of round trips when lazy-loading proxies are enabled.

public sealed class CommandCounter : DbCommandInterceptor
{
    public int Count { get; private set; }

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData data,
        InterceptionResult<DbDataReader> result)
    {
        Count++;
        return result;
    }
}

// In an integration test: await client.GetAsync("/api/orders");
// Assert.Equal(1, counter.Count);

Do not reflexively replace every Include with a DTO projection. For an edit screen that must modify a loaded aggregate, tracked entities are appropriate; use AsNoTrackingWithIdentityResolution() only when a read result repeats the same entity through multiple joins and duplicate object instances become costly. It performs identity resolution but still maintains an internal lookup, so benchmark it against plain AsNoTracking() rather than assuming it is free.

C# Course Technique: Compile Hot Queries Without Hiding Bad SQL

EF already caches much of its query translation by expression shape, so compiled queries help mainly on very hot, repeated query paths where expression binding and lookup overhead show up in CPU profiles. Verify that first with dotnet-trace collect --process-id <pid> --providers Microsoft-DotNETCore-SampleProfiler or PerfView; if the database dominates elapsed time, fix the plan, index, or payload before introducing EF.CompileAsyncQuery.

private static readonly Func<StoreDbContext, Guid, IAsyncEnumerable<ProductCard>>
    ProductsByCategory = EF.CompileAsyncQuery(
        (StoreDbContext db, Guid categoryId) =>
            db.Products.AsNoTracking()
              .Where(p => p.CategoryId == categoryId && p.IsActive)
              .OrderBy(p => p.Name)
              .Select(p => new ProductCard(p.Id, p.Name, p.Price)));

public async Task<List<ProductCard>> GetProducts(Guid categoryId, CancellationToken ct)
{
    var result = new List<ProductCard>();
    await foreach (var item in ProductsByCategory(_db, categoryId).WithCancellation(ct))
        result.Add(item);
    return result;
}

Keep the compiled delegate static and pass scalar parameters explicitly. Capturing a scoped service, a mutable tenant object, or the current user inside the compiled expression is a common lifetime bug and can also prevent one reusable query shape. Global query filters still apply, but if the filter reads a context property such as CurrentTenantId, test that each context instance sets it before execution; a compiled delegate does not make tenant state immutable.

For list endpoints, combine compilation only with an index-compatible keyset predicate. Offset paging forces the database to walk and discard earlier rows as the offset grows, while WHERE (Name > @lastName) OR (Name = @lastName AND Id > @lastId) can seek on a composite (Name, Id) index. Measure p50 and p99 latency at page 1 and page 500; a benchmark that tests only the first page misses the main benefit.

Microsoft Technologies Training: Control Cartesian Explosion and Split Reads

Loading two collection navigations in one query can multiply rows: 10 order lines and 5 payments for one order can yield roughly 50 joined rows before considering other joins. Use AsSplitQuery() when duplicated parent columns or materialization overhead are visible in the actual result set. EF then issues separate commands for collections, reducing row multiplication at the cost of extra database round trips.

var orders = await db.Orders
    .AsNoTracking()
    .Where(o => o.TenantId == tenantId)
    .Include(o => o.Lines.Where(l => !l.IsCancelled))
    .Include(o => o.Payments)
    .AsSplitQuery()
    .ToListAsync(ct);

Split queries have a consistency nuance: without an explicit transaction, another transaction can modify a child collection between the root command and a later collection command. For a report that requires a single consistent snapshot, use a database isolation level that your provider can actually honor, such as SQL Server snapshot isolation after it has been enabled by the DBA, or PostgreSQL repeatable read. Do not add a serializable transaction to ordinary dashboard reads without measuring lock waits and serialization retries.

await using var tx = await db.Database.BeginTransactionAsync(
    IsolationLevel.RepeatableRead, ct);

var report = await db.Orders.AsNoTracking()
    .Where(o => o.CreatedAt >= from && o.CreatedAt < to)
    .Include(o => o.Lines)
    .AsSplitQuery()
    .ToListAsync(ct);

await tx.CommitAsync(ct);

Set query splitting deliberately rather than relying on a project-wide default. A single query can be faster for small, bounded collections because it avoids extra network latency; split queries win when row multiplication is substantial. Capture both SQL shapes with ToQueryString(), run them against production-like cardinality, and compare transferred rows, logical reads, and endpoint p95 latency.

Blazor Training: Cancel Stale EF Core Reads and Preserve Pagination

A practical blazor training issue is overlapping UI requests: a user types three characters quickly, and an older, slower query can overwrite newer results. Create a fresh CancellationTokenSource for each search, cancel the prior one, and pass its token to ToListAsync. Cancellation is cooperative: the provider sends cancellation to the database where supported, but code after the await must still avoid applying results from a stale request.

private CancellationTokenSource? _searchCts;
private List<ProductCard> _items = [];

private async Task SearchAsync(string term)
{
    _searchCts?.Cancel();
    _searchCts?.Dispose();
    _searchCts = new CancellationTokenSource();
    var token = _searchCts.Token;

    try
    {
        var items = await Db.Products.AsNoTracking()
            .Where(p => p.IsActive && p.Name.StartsWith(term))
            .OrderBy(p => p.Name).ThenBy(p => p.Id)
            .Take(30)
            .Select(p => new ProductCard(p.Id, p.Name, p.Price))
            .ToListAsync(token);
        if (!token.IsCancellationRequested) _items = items;
    }
    catch (OperationCanceledException) when (token.IsCancellationRequested) { }
}

Use a short-lived context factory in server-side interactive UI code instead of keeping one scoped DbContext for the lifetime of a circuit. Register AddDbContextFactory<StoreDbContext>() and create a context per operation; DbContext is not thread-safe, and a long-lived tracker can retain thousands of entities after repeated navigation. For searchable text, also verify the generated predicate and collation: StartsWith is commonly index-seekable, whereas ToLower().Contains(...) often makes a normal index unusable.

Frequently Asked Questions

What should an entity framework training project measure before optimizing a query?

Measure command count, database duration, actual-plan logical reads, returned rows, and managed allocation per request. Tag the query with TagWith, inspect the actual plan in SQL Server Query Store or EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL, then change one variable at a time. A useful acceptance criterion is explicit, for example: one SQL command, fewer than 5,000 logical reads, and no tracked entities for a list endpoint.

Does an ASP.NET Core training project need AsNoTracking on every query?

Use AsNoTracking for read-only DTO, report, and API-list queries. Keep tracking when the same context will modify the loaded aggregate and call SaveChanges; attaching a DTO-shaped result later can require manually marking properties and risks overwriting concurrent changes. For a read graph with repeated references, benchmark AsNoTrackingWithIdentityResolution because its identity map has CPU and memory cost.

When should a C# course teach EF.CompileAsyncQuery?

Teach it after profiling proves repeated EF query binding is material CPU work. Make the delegate static, parameterize values such as tenant ID and category ID, and retain a stable query shape. It does not fix N+1 commands, table scans, missing indexes, or an oversized projection; validate SQL and actual database plans first.

How does blazor training handle EF Core searches while users type?

Cancel the previous CancellationTokenSource, create a DbContext from IDbContextFactory for the new operation, and pass the new token to ToListAsync. Order results by a stable tie-breaker such as Name then Id so that paging does not skip or duplicate rows when names are equal. Ignore OperationCanceledException only when it belongs to the request you intentionally cancelled.