Delta

Delta is an approach to implementing a 304 Not Modified leveraging DB change tracking.

The approach uses a last updated timestamp from the database to generate an ETag. All dynamic requests then have that ETag checked/applied.

This approach works well when the frequency of updates is relatively low. In this scenario, the majority of requests will leverage the result in a 304 Not Modified being returned and the browser loading the content its cache.

Effectively consumers will always receive the most current data, while the load on the server remains low.

See Milestones for release notes.

Assumptions

Frequency of updates to data is relatively low compared to reads

SQL Server

For SQL Server the transaction log is used (via dm_db_log_stats ) if the current user has the VIEW SERVER STATE permission.

If VIEW SERVER STATE is not allowed then a combination of Change Tracking and/or Row Versioning is used.

Give the above certain kinds of operations will be detected:

	Transaction Log	Change Tracking	Row Versioning	Change Tracking and Row Versioning
Insert	✅	✅	✅	✅
Update	✅	✅	✅	✅
Hard Delete	✅	✅	❌	✅
Soft Delete	✅	✅	✅	✅
Truncate	✅	❌	❌	❌

Postgres

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

304 Not Modified Flow

graph TD
    Request
    CalculateEtag[Calculate current ETag<br/>based on timestamp<br/>from web assembly and SQL]
    IfNoneMatch{Has<br/>If-None-Match<br/>header?}
    EtagMatch{Current<br/>Etag matches<br/>If-None-Match?}
    AddETag[Add current ETag<br/>to Response headers]
    304[Respond with<br/>304 Not-Modified]
    Request --> CalculateEtag
    CalculateEtag --> IfNoneMatch
    IfNoneMatch -->|Yes| EtagMatch
    IfNoneMatch -->|No| AddETag
    EtagMatch -->|No| AddETag
    EtagMatch -->|Yes| 304

ETag calculation logic

The ETag is calculated from a combination several parts

AssemblyWriteTime

The last write time of the web entry point assembly

var webAssemblyLocation = Assembly.GetEntryAssembly()!.Location;
AssemblyWriteTime = File.GetLastWriteTime(webAssemblyLocation).Ticks.ToString();

^{snippet source | anchor}

SQL timestamp

SQL Server

`VIEW SERVER STATE` permission

Transaction log is used via dm_db_log_stats.

select log_end_lsn
from sys.dm_db_log_stats(db_id())

No `VIEW SERVER STATE` permission

A combination of change_tracking_current_version (if tracking is enabled) and @@DBTS (row version timestamp)

declare @changeTracking bigint = change_tracking_current_version();
declare @timeStamp bigint = convert(bigint, @@dbts);

if (@changeTracking is null)
  select cast(@timeStamp as varchar)
else
  select cast(@timeStamp as varchar) + '-' + cast(@changeTracking as varchar)

Postgres

select pg_last_committed_xact();

Suffix

An optional string suffix that is dynamically calculated at runtime based on the current HttpContext.

var app = builder.Build();
app.UseDelta(suffix: httpContext => "MySuffix");

^{snippet source | anchor}

Combining the above

internal static string BuildEtag(string timeStamp, string? suffix)
{
    if (suffix == null)
    {
        return $"\"{AssemblyWriteTime}-{timeStamp}\"";
    }

    return $"\"{AssemblyWriteTime}-{timeStamp}-{suffix}\"";
}

^{snippet source | anchor}

NuGet

Delta is shipped as two nugets:

Delta: Delivers functionality using SqlConnection and SqlTransaction.
Delta.EF: Delivers functionality using SQL Server EF Database Provider.

Only one of the above should be used.

Usage

SQL Server DB Schema

Example SQL schema:

-- Tables

CREATE TABLE [dbo].[Companies](
	[Id] [uniqueidentifier] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
	[Content] [nvarchar](max) NULL,
 CONSTRAINT [PK_Companies] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[Companies] ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = OFF)

CREATE TABLE [dbo].[Employees](
	[Id] [uniqueidentifier] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
	[CompanyId] [uniqueidentifier] NOT NULL,
	[Content] [nvarchar](max) NULL,
	[Age] [int] NOT NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Employees_CompanyId] ON [dbo].[Employees]
(
	[CompanyId] ASC
) ON [PRIMARY]

^{snippet source | anchor}

Postgres DB Schema

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}

Add to WebApplicationBuilder

SQL Server

var builder = WebApplication.CreateBuilder();
builder.Services.AddScoped(_ => new SqlConnection(connectionString));
var app = builder.Build();
app.UseDelta();

^{snippet source | anchor}

PostgreSQL

var builder = WebApplication.CreateBuilder();
builder.Services.AddScoped(_ => new NpgsqlConnection(connectionString));
var app = builder.Build();
app.UseDelta();

^{snippet source | anchor}

Add to a Route Group

To add to a specific Route Group:

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

^{snippet source | anchor}

ShouldExecute

Optionally control what requests Delta is executed on.

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

^{snippet source | anchor}

Custom Connection discovery

By default, Delta uses HttpContext.RequestServices to discover the SqlConnection and SqlTransaction:

static (Type sqlConnection, Type transaction) FindConnectionType()
{
    var sqlConnection = Type.GetType("Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient");
    if (sqlConnection != null)
    {
        var transaction = sqlConnection.Assembly.GetType("Microsoft.Data.SqlClient.SqlTransaction")!;
        return (sqlConnection, transaction);
    }

    var npgsqlConnection = Type.GetType("Npgsql.NpgsqlConnection, Npgsql");
    if (npgsqlConnection != null)
    {
        var transaction = npgsqlConnection.Assembly.GetType("Npgsql.NpgsqlTransaction")!;
        return (npgsqlConnection, transaction);
    }

    throw new("Could not find connection type. Tried Microsoft.Data.SqlClient.SqlConnection and Npgsql.NpgsqlTransaction");
}

static Connection DiscoverConnection(HttpContext httpContext)
{
    var (connectionType, transactionType) = FindConnectionType();
    var provider = httpContext.RequestServices;
    var connection = (DbConnection) provider.GetRequiredService(connectionType);
    var transaction = (DbTransaction?) provider.GetService(transactionType);
    return new(connection, transaction);
}

^{snippet source | anchor}

To use custom connection discovery:

var application = webApplicationBuilder.Build();
application.UseDelta(
    getConnection: httpContext => httpContext.RequestServices.GetRequiredService<SqlConnection>());

^{snippet source | anchor}

To use custom connection and transaction discovery:

var application = webApplicationBuilder.Build();
application.UseDelta(
    getConnection: httpContext =>
    {
        var provider = httpContext.RequestServices;
        var connection = provider.GetRequiredService<SqlConnection>();
        var transaction = provider.GetService<SqlTransaction>();
        return new(connection, transaction);
    });

^{snippet source | anchor}

EF Usage

SqlServer DbContext using RowVersion

Enable row versioning in Entity Framework

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();
        company
            .Property(_ => _.RowVersion)
            .IsRowVersion()
            .HasConversion<byte[]>();

        var employee = builder.Entity<Employee>();
        employee.HasKey(_ => _.Id);
        employee
            .Property(_ => _.RowVersion)
            .IsRowVersion()
            .HasConversion<byte[]>();
    }
}

^{snippet source | anchor}

Postgres DbContext

Enable row versioning in Entity Framework

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

SQL Server

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

^{snippet source | anchor}

Postgres

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}

UseResponseDiagnostics

Response diagnostics is an opt-in feature that includes extra log information in the response headers.

Enable by setting UseResponseDiagnostics to true at startup:

DeltaExtensions.UseResponseDiagnostics = true;

^{snippet source | anchor}

Response diagnostics headers are prefixed with Delta-.

Example Response header when the Request has not If-None-Match header.

Delta.SqlServer

A set of helper methods for working with SQL Server Change Tracking and SQL Server Row Versioning

Nuget: Delta.SqlServer

GetLastTimeStamp

For a `SqlConnection`:

var timeStamp = await sqlConnection.GetLastTimeStamp();

^{snippet source | anchor}

For a `DbContext`:

var timeStamp = await dbContext.GetLastTimeStamp();

^{snippet source | anchor}

GetDatabasesWithTracking

Get a list of all databases with change tracking enabled.

var trackedDatabases = await sqlConnection.GetTrackedDatabases();
foreach (var db in trackedDatabases)
{
    Trace.WriteLine(db);
}

^{snippet source | anchor}

Uses the following SQL:

select d.name
from sys.databases as d inner join
  sys.change_tracking_databases as t on
  t.database_id = d.database_id

^{snippet source | anchor}

GetTrackedTables

Get a list of all tracked tables in database.

var trackedTables = await sqlConnection.GetTrackedTables();
foreach (var db in trackedTables)
{
    Trace.WriteLine(db);
}

^{snippet source | anchor}

Uses the following SQL:

select t.Name
from sys.tables as t inner join
  sys.change_tracking_tables as c on t.[object_id] = c.[object_id]

^{snippet source | anchor}

IsTrackingEnabled

Determine if change tracking is enabled for a database.

var isTrackingEnabled = await sqlConnection.IsTrackingEnabled();

^{snippet source | anchor}

Uses the following SQL:

select count(d.name)
from sys.databases as d inner join
  sys.change_tracking_databases as t on
  t.database_id = d.database_id
where d.name = '{database}'

^{snippet source | anchor}

EnableTracking

Enable change tracking for a database.

await sqlConnection.EnableTracking();

^{snippet source | anchor}

Uses the following SQL:

alter database {database}
set change_tracking = on
(
  change_retention = {retentionDays} days,
  auto_cleanup = on
)

^{snippet source | anchor}

DisableTracking

Disable change tracking for a database and all tables within that database.

await sqlConnection.DisableTracking();

^{snippet source | anchor}

Uses the following SQL:

For disabling tracking on a database:

alter database [{database}] set change_tracking = off;

^{snippet source | anchor}

For disabling tracking on tables:

alter table [{table}] disable change_tracking;

^{snippet source | anchor}

SetTrackedTables

Enables change tracking for all tables listed, and disables change tracking for all tables not listed.

await sqlConnection.SetTrackedTables(["Companies"]);

^{snippet source | anchor}

Uses the following SQL:

For enabling tracking on a database:

alter database {database}
set change_tracking = on
(
  change_retention = {retentionDays} days,
  auto_cleanup = on
)

^{snippet source | anchor}

For enabling tracking on tables:

alter table [{table}] enable change_tracking

^{snippet source | anchor}

For disabling tracking on tables:

alter table [{table}] disable change_tracking;

^{snippet source | anchor}

Verifying behavior

The behavior of Delta can be verified as follows:

Open a page in the site
Open the browser developer tools
Change to the Network tab
Refresh the page.

Cached responses will show as 304 in the Status:

In the headers if-none-match will show in the request and etag will show in the response:

Ensure cache is not disabled

If disable cache is checked, the browser will not send the if-none-match header. This will effectively cause a cache miss server side, and the full server pipeline will execute.

Certificates and Chromium

Chromium, and hence the Chrome and Edge browsers, are very sensitive to certificate problems when determining if an item should be cached. Specifically, if a request is done dynamically (type: xhr) and the server is using a self-signed certificate, then the browser will not send the if-none-match header. Reference. If self-signed certificates are required during development in lower environment, then use FireFox to test the caching behavior.

Programmatic client usage

Delta is primarily designed to support web browsers as a client. All web browsers have the necessary 304 and caching functionally required.

In the scenario where web apis (that support using 304) are being consumed using .net as a client, consider using one of the below extensions to cache responses.

Icon

Estuary designed by Daan from The Noun Project.

License

Zananok/Delta

ErrorLooks like something went wrong!

About

Resources

License

Code of conduct

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Languages

Error
Looks like something went wrong!

Packages