Data Access Study Portal

Additional notes

Performance characteristics

Where EF Core spends time

1. Query compilation — first time a LINQ query runs, EF Core builds an expression tree and translates to SQL. Cached afterwards, but first hit is ~10× slower.
2. Change tracker — every loaded entity is added to the tracker. Tracker is a dictionary keyed by primary key with snapshot of original values. Memory: ~500–1000 bytes per tracked entity.
3. Materialization — SQL result → entity instance via cached delegates. Fast after warmup.
4. SaveChanges — tracker diffs against snapshots, builds UPDATE / INSERT / DELETE statements, batches them.

Typical numbers (single Postgres, local network)

Bulk operations are EF Core's weakest area. SaveChanges issues one INSERT per row by default (batched, but still per-row). For high-volume inserts, use EFCore.BulkExtensions (or raw COPY for the absolute floor).

Compiled queries

For hot paths called millions of times:

private static readonly Func<IdentityDbContext, string, Task<Tenant?>> _getBySlug =
    EF.CompileAsyncQuery((IdentityDbContext db, string slug) =>
        db.Tenants.AsNoTracking().FirstOrDefault(t => t.Slug == slug));

// Usage
var tenant = await _getBySlug(db, slug);

Skips the query-tree-cache lookup. ~10–20% faster than the equivalent inline LINQ. Worth it on the hottest queries only.

Connection pool tuning (Npgsql)

Npgsql has its own connection pool (separate from EF Core's DbContextPool).

Set via connection string:

Host=postgres;Database=identity;Username=app;Password=...;Maximum Pool Size=25;Connection Lifetime=3600;Multiplexing=true

Sizing rule of thumb: Max Pool Size × replica count ≤ Postgres max_connections × 0.8. Same logic as Go's pgxpool.

AsNoTracking — the silent default you should set

The default db.Tenants.ToListAsync() adds every returned entity to the change tracker. For read-only queries this is pure waste.

// Per-query
var tenants = await db.Tenants.AsNoTracking().ToListAsync();

// Per-DbContext (recommended for read-heavy services)
optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);

// Per-query opt back IN when you do need tracking
var tenant = await db.Tenants.AsTracking().FirstAsync(t => t.Id == id);

Sets change tracker to NoTracking by default; opt in explicitly when you actually need to modify.

Bulk operations

EF Core's built-in INSERT path is fine for ≤100 rows. For more:

EFCore.BulkExtensions

await db.BulkInsertAsync(tenants);
await db.BulkUpdateAsync(tenants);
await db.BulkInsertOrUpdateAsync(tenants);

10–100× faster than SaveChanges for ≥1000 rows. Uses provider-specific bulk paths (Postgres COPY, SQL Server SqlBulkCopy).

Raw Npgsql COPY (fastest)

using var conn = (NpgsqlConnection)db.Database.GetDbConnection();
await conn.OpenAsync();

await using var writer = await conn.BeginBinaryImportAsync(
    "COPY tenants (tenant_id, slug, name) FROM STDIN (FORMAT BINARY)");

foreach (var t in tenants)
{
    await writer.StartRowAsync();
    await writer.WriteAsync(t.TenantId, NpgsqlDbType.Uuid);
    await writer.WriteAsync(t.Slug, NpgsqlDbType.Text);
    await writer.WriteAsync(t.Name, NpgsqlDbType.Text);
}

await writer.CompleteAsync();

Lowest-latency path available. Directly equivalent to Go's pgx.CopyFrom.

Observability

EF Core logging

builder.Services.AddDbContext<IdentityDbContext>(options =>
    options
        .UseNpgsql(connStr)
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging(builder.Environment.IsDevelopment())); // SQL params, dev only

Interceptors (per-query custom logic)

public class SlowQueryInterceptor : DbCommandInterceptor
{
    public override async ValueTask<DbDataReader> ReaderExecutedAsync(
        DbCommand command, CommandExecutedEventData eventData, DbDataReader result,
        CancellationToken ct = default)
    {
        if (eventData.Duration.TotalMilliseconds > 100)
        {
            Log.Warning("Slow query ({Duration}ms): {Sql}",
                eventData.Duration.TotalMilliseconds, command.CommandText);
        }
        return await base.ReaderExecutedAsync(command, eventData, result, ct);
    }
}

// Registration
options.AddInterceptors(new SlowQueryInterceptor());

OpenTelemetry instrumentation

builder.Services.AddOpenTelemetry().WithTracing(tracing => tracing
    .AddNpgsql()                  // Npgsql-level spans
    .AddEntityFrameworkCoreInstrumentation(opts => opts.SetDbStatementForText = true));

Each query becomes a span with the SQL as db.statement. Direct analog of Go's otelpgx setup.

Common pitfalls

1. N+1 from lazy loading — tenant.Users triggers a query inside a loop. Fix: .Include(t => t.Users) or project to a DTO.
2. Change tracker holding thousands of entities — long-lived DbContext or load-everything-then-iterate patterns balloon memory. Fix: AsNoTracking, AsAsyncEnumerable, or scope the DbContext properly.
3. Forgetting AsNoTracking on read-only queries — silently slower and heavier than necessary. Set query tracking behavior at the DbContext level.
4. SaveChanges for bulk operations — see Bulk operations.
5. Include chains creating cartesian explosion — Tenants.Include(t => t.Users).Include(t => t.ApiKeys) returns tenants × users × apiKeys rows. EF Core 5+ uses split queries by default for collections, but check the generated SQL.
6. Catching DbUpdateException without inspecting the inner exception — the real Postgres error is in ex.InnerException (typically PostgresException).
7. Migration race condition with multiple API replicas — all replicas call MigrateAsync on startup; one wins, others see "already applied". Usually fine due to idempotency, but for safety run a separate migrator Job in K8s.
8. MigrateAsync failing on startup brings the service down — the current codebase wraps it in try/catch + degraded-mode logging. Worth keeping.

When to drop to Dapper / raw SQL

Dapper is a thin layer over IDbConnection:

using var conn = new NpgsqlConnection(connStr);
var tenants = await conn.QueryAsync<Tenant>(
    "SELECT tenant_id, slug, name FROM tenants WHERE slug = @Slug",
    new { Slug = slug });

No change tracker, no entity model, manual mapping — closer to Go's pgx-direct in spirit.

Comparison to Go (sqlc + pgx + goose)

The deeper difference: EF Core is opinionated optimization — it's making decisions for you (change tracking, lazy loading, query translation). When the decisions are right, it's faster to write. When they're wrong, you spend hours figuring out why a simple query is slow. sqlc+pgx is zero opinion — every byte of SQL is yours. Less convenience, fewer surprises.

Interview talking points

• "Explain AsNoTracking." — Disables the change tracker for the query. The returned entities aren't added to the tracker's internal dictionary, no snapshot is taken, SaveChanges won't touch them. Use it for read-only queries; it's a measurable perf win (~30–40% on large result sets) and a memory win.
• "What does the change tracker actually do?" — Maintains a dictionary of entity → (state, original-values-snapshot). On SaveChanges, it diffs current values against the snapshot to generate UPDATE statements only for changed properties. Costs memory; saves you from writing UPDATE statements.
• "Why is DbContext scoped, not singleton?" — DbContext holds the change tracker, the open connection, and query caches. It's not thread-safe. One per request keeps tracker scope reasonable and avoids cross-request data leakage.
• "What's ExecuteUpdateAsync?" — EF Core 7+ bulk-update path that bypasses the change tracker. db.Tenants.Where(...).ExecuteUpdateAsync(s => s.SetProperty(...)) issues a single UPDATE without loading entities. Massive perf win for bulk modifications.
• "How do EF Core migrations differ from goose / golang-migrate?" — EF Core generates the migration SQL from your entity-model diff. goose makes you write the SQL by hand. EF Core is faster for rapid iteration; goose makes SQL the source of truth (DBAs can read it directly).
• "How would you safely apply migrations across multiple API replicas?" — Either (a) idempotency-of-MigrateAsync covers it (all replicas race, one wins, rest see "already applied") — generally fine, or (b) run migrations as a separate K8s Job before the rolling update of API pods.
• "When would you NOT use EF Core?" — Bulk operations (use BulkExtensions or COPY), reporting queries with complex joins (Dapper is faster and clearer), hot paths with >1M req/day (every microsecond counts).
• "How do you profile EF Core queries in production?" — LogTo + interceptors for slow queries; OpenTelemetry for distributed tracing; MiniProfiler for dev; check __EFMigrationsHistory for drift; EXPLAIN ANALYZE the generated SQL in psql.
• "What's DbContext pooling and when do you use it?" — AddDbContextPool reuses DbContext instances across requests instead of allocating fresh ones. Cuts allocations on high-throughput services. Doesn't affect connection pooling (Npgsql does that separately).
• "What's the equivalent of tenant.Users.Add(...) in Go?" — There isn't one. In Go you'd issue INSERT INTO users (..., tenant_id) VALUES (..., $1) explicitly. The lack of object-graph navigation is the trade for explicitness.

Further reading

• EF Core documentation
• Npgsql documentation
• EFCore.BulkExtensions
• Dapper
• Use the index, Luke — DB indexing reference (language-agnostic)