/Delta

An approach to implementing a 304 Not Modified leveraging SqlServer change tracking

Primary LanguageC#MIT LicenseMIT

Delta

Build status NuGet Status NuGet Status

Delta is an approach to implementing a 304 Not Modified leveraging SqlServer 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

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
Loading

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

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)

snippet source | anchor

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:

Only one of the above should be used.

Usage

DB Schema

Ensure SQL Server Change Tracking and/or SQL Server Row Versioning is enabled for all relevant tables.

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]

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

Add to WebApplicationBuilder

var builder = WebApplication.CreateBuilder();
builder.Services.AddScoped(_ => new SqlConnection(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 Connection DiscoverConnection(HttpContext httpContext)
{
    var provider = httpContext.RequestServices;
    var connection = provider.GetRequiredService<SqlConnection>();
    var transaction = provider.GetService<SqlTransaction>();
    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 webApplication = webApplicationBuilder.Build();
webApplication.UseDelta(
    getConnection: httpContext =>
    {
        var provider = httpContext.RequestServices;
        var sqlConnection = provider.GetRequiredService<SqlConnection>();
        var sqlTransaction = provider.GetService<SqlTransaction>();
        return new(sqlConnection, sqlTransaction);
    });

snippet source | anchor

EF Usage

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

Add to WebApplicationBuilder

var builder = WebApplication.CreateBuilder();
builder.Services.AddSqlServer<SampleDbContext>(database.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

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.

Helpers

Utility methods for working with databases using the Delta conventions.

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 left join
  sys.change_tracking_tables as c on t.[object_id] = c.[object_id]
where c.[object_id] is not null

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

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.