Data Access Study Portal

How it works

The stack

[your code]
      ↓
[query layer: EF Core LINQ  OR  EF Core raw SQL  OR  Dapper  OR  NpgsqlCommand]
      ↓
[provider: Npgsql.EntityFrameworkCore.PostgreSQL]
      ↓
[driver: Npgsql — binary protocol, allocation-aware]
      ↓
[Postgres]

DbContext setup

Per service convention used across this codebase (TenantService, QuestionerService, etc.):

// Registration
builder.Services.AddDbContext<IdentityDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("IdentityDb"))
);

// For high-traffic services, DbContext pooling cuts allocations
builder.Services.AddDbContextPool<IdentityDbContext>(options =>
    options.UseNpgsql(connectionString), poolSize: 128);

AddDbContextPool reuses DbContext instances across requests — meaningful win on hot paths because change-tracker / state-manager allocation is the bulk of per-request overhead.

Query patterns — LINQ

// SELECT one
var tenant = await db.Tenants
    .Where(t => t.Slug == slug)
    .FirstOrDefaultAsync();

// SELECT many with projection (avoids loading full entity)
var summaries = await db.Tenants
    .Where(t => t.Active)
    .Select(t => new TenantSummary(t.TenantId, t.Slug, t.Name))
    .ToListAsync();

// Read-only query — disable change tracking
var tenants = await db.Tenants
    .AsNoTracking()
    .Where(t => t.Active)
    .ToListAsync();

// INSERT
db.Tenants.Add(new Tenant { Slug = "kucy", Name = "Kizomba Union CY" });
await db.SaveChangesAsync();

// UPDATE — change tracker generates the SQL
var tenant = await db.Tenants.FirstAsync(t => t.Slug == slug);
tenant.ThemeConfigJson = newJson;
await db.SaveChangesAsync();

// UPDATE — bulk, no entity load (EF Core 7+)
await db.Tenants
    .Where(t => t.Slug == slug)
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(t => t.ThemeConfigJson, newJson)
        .SetProperty(t => t.UpdatedAt, DateTime.UtcNow));

// DELETE — bulk, no entity load (EF Core 7+)
await db.Tenants
    .Where(t => t.TenantId == id)
    .ExecuteDeleteAsync();

ExecuteUpdateAsync / ExecuteDeleteAsync (EF Core 7+) are the modern way to do bulk modifications without loading entities into the tracker. Use them whenever you don't need the entity in memory.

Query patterns — raw SQL

// FromSqlRaw — returns entities, still uses change tracker
var tenants = await db.Tenants
    .FromSqlRaw("SELECT * FROM tenants WHERE slug LIKE {0}", $"{prefix}%")
    .AsNoTracking()
    .ToListAsync();

// FromSqlInterpolated — safer, parameterized via string interpolation
var tenants = await db.Tenants
    .FromSqlInterpolated($"SELECT * FROM tenants WHERE slug = {slug}")
    .ToListAsync();

// Raw execution
await db.Database.ExecuteSqlRawAsync(
    "UPDATE tenants SET last_seen = now() WHERE tenant_id = {0}", tenantId);

Transactions

await using var transaction = await db.Database.BeginTransactionAsync();
try
{
    db.Tenants.Add(tenant);
    await db.SaveChangesAsync();

    foreach (var evt in events)
    {
        db.OutboxEntries.Add(new OutboxEntry(evt.GetType().Name, JsonSerializer.Serialize(evt)));
    }
    await db.SaveChangesAsync();

    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

The await using pattern auto-disposes the transaction. If you didn't commit, dispose rolls back. Most code uses this without explicit Rollback.

For savepoints (nested transactions):

await transaction.CreateSavepointAsync("before_outbox");
// ... do work ...
await transaction.RollbackToSavepointAsync("before_outbox"); // if needed

Migrations — full EF Core CLI workflow

Creating a migration

# Inside the project that has the DbContext
dotnet ef migrations add AddOnboardingState --project src/TenantService.Infrastructure

EF Core diffs your current entity model against the last snapshot, generates a migration class:

public partial class AddOnboardingState : Migration
{
    protected override void Up(MigrationBuilder mb)
    {
        mb.AddColumn<string>(
            name: "OnboardingState",
            table: "UserPreferences",
            type: "text",
            nullable: true);
    }

    protected override void Down(MigrationBuilder mb)
    {
        mb.DropColumn(name: "OnboardingState", table: "UserPreferences");
    }
}

Applying migrations at service startup (current codebase pattern)

// From TenantService/src/TenantService.API/ProgramExtensions.cs
try
{
    await dbContext.Database.MigrateAsync();
    Log.Information("Database migrations applied successfully");
}
catch (Exception ex)
{
    Log.Error(ex, "Database migration failed — service starting in degraded mode");
}

MigrateAsync is idempotent: it reads __EFMigrationsHistory, applies anything missing, no-ops if current.

State tracking

EF Core auto-creates:

CREATE TABLE "__EFMigrationsHistory" (
    "MigrationId"    VARCHAR(150) PRIMARY KEY,
    "ProductVersion" VARCHAR(32) NOT NULL
);

Design-time DbContext factory (for dotnet ef outside the host)

For services where the host requires runtime config the CLI doesn't have (Keycloak issuer URLs, etc.):

public class IdentityDbContextFactory : IDesignTimeDbContextFactory<IdentityDbContext>
{
    public IdentityDbContext CreateDbContext(string[] args)
    {
        var options = new DbContextOptionsBuilder<IdentityDbContext>()
            .UseNpgsql("Host=localhost;Database=identity;Username=postgres;Password=postgres")
            .Options;
        return new IdentityDbContext(options);
    }
}

Reverting

# Apply migrations up to a target
dotnet ef database update PreviousMigrationName

# Generate idempotent SQL script (for prod review)
dotnet ef migrations script --idempotent --output migrate.sql

# Bundle migrations into a standalone executable (for CI/CD)
dotnet ef migrations bundle --output efbundle.exe