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
- Using SQL Server or Postgres timestamp features
- SQL Server: Using either SQL Server Change Tracking and/or SQL Server Row Versioning
- Postgres: track_commit_timestamp is enabled. This can be done using
ALTER SYSTEM SET track_commit_timestamp to "on"and then restarting the Postgres service
304 Not Modified Flow
Request
CalculateEtag[Calculate current ETag
based on timestamp
from web assembly and SQL]
IfNoneMatch{Has
If-None-Match
header?}
EtagMatch{Current
Etag matches
If-None-Match?}
AddETag[Add current ETag
to Response headers]
304[Respond with
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
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 @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.
app.UseDelta(suffix: httpContext => "MySuffix");
snippet source | anchor
Combining the above
{
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
Ensure SQL Server Change Tracking and/or SQL Server Row Versioning is enabled for all relevant tables.
Example SQL schema:
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
Postgres DB Schema
Example SQL schema:
(
""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
builder.Services.AddScoped(_ => new NpgsqlConnection(connectionString));
var app = builder.Build();
app.UseDelta();
snippet source | anchor
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:
.UseDelta()
.MapGet("/", () => "Hello Group!");
snippet source | anchor
.UseDelta()
.MapGet("/", () => "Hello Group!");
snippet source | anchor
ShouldExecute
Optionally control what requests Delta is executed on.
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:
{
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");
}
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:
application.UseDelta(
getConnection: httpContext => httpContext.RequestServices.GetRequiredService<SqlConnection>());
snippet source | anchor
To use custom connection and transaction discovery:
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
SqlServer DbContext using RowVersion
Enable row versioning in Entity Framework
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
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
builder.Services.AddSqlServer<SampleDbContext>(connectionString);
var app = builder.Build();
app.UseDelta<SampleDbContext>();
snippet source | anchor
Postgres
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:
.UseDelta<SampleDbContext>()
.MapGet("/", () => "Hello Group!");
snippet source | anchor
.UseDelta<SampleDbContext>()
.MapGet("/", () => "Hello Group!");
snippet source | anchor
ShouldExecute
Optionally control what requests Delta is executed on.
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:
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:
snippet source | anchor
For a DbContext:
snippet source | anchor
GetDatabasesWithTracking
Get a list of all databases with change tracking enabled.
foreach (var db in trackedDatabases)
{
Trace.WriteLine(db);
}
snippet source | anchor
Uses the following SQL:
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.
foreach (var db in trackedTables)
{
Trace.WriteLine(db);
}
snippet source | anchor
Uses the following SQL:
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.
snippet source | anchor
Uses the following SQL:
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.
snippet source | anchor
Uses the following SQL:
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.
snippet source | anchor
Uses the following SQL:
For disabling tracking on a database:
snippet source | anchor
For disabling tracking on tables:
snippet source | anchor
SetTrackedTables
Enables change tracking for all tables listed, and disables change tracking for all tables not listed.
snippet source | anchor
Uses the following SQL:
For enabling tracking on a database:
set change_tracking = on
(
change_retention = {retentionDays} days,
auto_cleanup = on
)
snippet source | anchor
For enabling tracking on tables:
snippet source | anchor
For disabling tracking on tables:
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.