EF Core performance beyond N+1 and indexes: DbContext pooling, compiled queries, ExecuteUpdate

// ======================================================================
// Q: Beyond N+1 and indexes: how do you make EF Core faster?
//
// - Reads: AsNoTracking() and projection to a DTO with only the columns you need
//   (no SELECT *).
// - Writes: many changes in one SaveChangesAsync (EF batches them), not
//   SaveChanges inside a loop. For bulk imports: COPY (PostgreSQL) or
//   SqlBulkCopy.
// - Keyset pagination (Q9), and always with a limit: no endpoint without Take.
// - Reports: materialized views or a read replica, so heavy queries don't slow
//   down order processing.
// - Connection pool: right-sized, and connections are released quickly (short
//   scopes, no transactions waiting on HTTP calls).
// - HTTP level: response compression (Brotli / Gzip) for large JSON, HTTP/2,
//   smaller payloads.
// ======================================================================

// DbContext pooling: reuses instances, fewer allocations per request
builder.Services.AddDbContextPool<AppDbContext>(o => o.UseNpgsql(connectionString));

// Compiled query for very frequent queries: the LINQ is not re-translated every time
private static readonly Func<AppDbContext, string, Task<ProductDto?>> GetBySku =
    EF.CompileAsyncQuery((AppDbContext db, string sku) =>
        db.Products.AsNoTracking()
          .Where(p => p.Sku == sku)
          .Select(p => new ProductDto(p.Sku, p.Name, p.ListPrice))
          .FirstOrDefault());

// Bulk changes without loading entities (EF Core 7+)
await db.Products
    .Where(p => p.SupplierId == supplierId)
    .ExecuteUpdateAsync(s => s.SetProperty(p => p.ListPrice, p => p.ListPrice * 1.05m), ct);

await db.CartItems
    .Where(c => c.CreatedAt < DateTime.UtcNow.AddDays(-30))
    .ExecuteDeleteAsync(ct);