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();

^{snippet source | anchor}

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}

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}