postgres-ef.md

PostgreSQL usage with EntityFramework

Docs for when using when using the PostgreSQL EF Database Provider

Implementation

Postgres required track_commit_timestamp to be enabled. This can be done using ALTER SYSTEM SET track_commit_timestamp to "on" and then restarting the Postgres service

Timestamp calculation

select pg_last_committed_xact();

Usage

Example SQL schema

create table IF NOT EXISTS public."Companies"
(
    "Id" uuid not null
        constraint "PK_Companies"
            primary key,
    "Content" text
);

alter table public."Companies"
    owner to postgres;

create table IF NOT EXISTS public."Employees"
(
    "Id" uuid not null
        constraint "PK_Employees"
            primary key,
    "CompanyId" uuid not null
        constraint "FK_Employees_Companies_CompanyId"
            references public."Companies"
            on delete cascade,
    "Content"   text,
    "Age"       integer not null
);

alter table public."Employees"
    owner to postgres;

create index IF NOT EXISTS "IX_Employees_CompanyId"
    on public."Employees" ("CompanyId");

^{snippet source | anchor}

DbContext

public class SampleDbContext(DbContextOptions options) :
    DbContext(options)
{
    public DbSet<Employee> Employees { get; set; } = null!;
    public DbSet<Company> Companies { get; set; } = null!;
    protected override void OnModelCreating(ModelBuilder builder)
    {
        var company = builder.Entity<Company>();
        company.HasKey(_ => _.Id);
        company
            .HasMany(_ => _.Employees)
            .WithOne(_ => _.Company)
            .IsRequired();

        var employee = builder.Entity<Employee>();
        employee.HasKey(_ => _.Id);
    }
}

^{snippet source | anchor}

Add to WebApplicationBuilder

var builder = WebApplication.CreateBuilder();
builder.Services.AddDbContext<SampleDbContext>(
    _ => _.UseNpgsql(connectionString));
var app = builder.Build();
app.UseDelta<SampleDbContext>();

^{snippet source | anchor}

Add to a Route Group

To add to a specific Route Group:

app.MapGroup("/group")
    .UseDelta<SampleDbContext>()
    .MapGet("/", () => "Hello Group!");

^{snippet source | anchor}

app.MapGroup("/group")
    .UseDelta<SampleDbContext>()
    .MapGet("/", () => "Hello Group!");

^{snippet source | anchor}

ShouldExecute

Optionally control what requests Delta is executed on.

var app = builder.Build();
app.UseDelta<SampleDbContext>(
    shouldExecute: httpContext =>
    {
        var path = httpContext.Request.Path.ToString();
        return path.Contains("match");
    });

^{snippet source | anchor}

Suffix and Authentication

When using a suffix callback that accesses HttpContext.User claims, authentication middleware must run before UseDelta. If UseDelta runs first, the User claims won't be populated yet, and all users will get the same cache key.

Delta automatically detects this misconfiguration and throws an InvalidOperationException with a helpful message if:

• A suffix callback is provided
• The user is not authenticated (context.User.Identity?.IsAuthenticated != true)

var app = builder.Build();

// Authentication middleware must run before UseDelta
// so that User claims are available to the suffix callback
app.UseAuthentication();
app.UseAuthorization();

app.UseDelta<SampleDbContext>(
    suffix: httpContext =>
    {
        // Access user claims to create per-user cache keys
        var userId = httpContext.User.FindFirst(ClaimTypes.NameIdentifier)?.Value;
        var tenantId = httpContext.User.FindFirst("TenantId")?.Value;
        return $"{userId}-{tenantId}";
    });

^{snippet source | anchor}

AllowAnonymous

For endpoints that intentionally allow anonymous access but still want to use a suffix for cache differentiation (e.g., based on request headers rather than user claims), use allowAnonymous: true:

var app = builder.Build();

// For endpoints that intentionally allow anonymous access
// but still want a suffix for cache differentiation
app.UseDelta<SampleDbContext>(
    suffix: httpContext => httpContext.Request.Headers["X-Client-Version"].ToString(),
    allowAnonymous: true);

^{snippet source | anchor}

GetLastTimeStamp:

GetLastTimeStamp is a helper method to get the DB timestamp that Delta uses to calculate the etag.

It can be called on a DbContext:

var timeStamp = await dbContext.GetLastTimeStamp();

^{snippet source | anchor}

Or a DbConnection:

var timeStamp = await connection.GetLastTimeStamp();

^{snippet source | anchor}