RAIC.Extensions.Configuration.EntityFrameworkCore.SqlServer 0.1.1

RAIC.Extensions.Configuration.EntityFrameworkCore.SqlServer

This library enhances RAIC.Extensions.Configuration.EntityFrameworkCore with support for reload on update the via the query change notifications capability exposed by Microsoft.Data.SqlClient.SqlDependency along with SQL Server's Change Tracking feature.

Goals

1. No polling!
2. Updates happen in background via a hosted service implementation
3. Only update settings which change rather than updating them all

Requirements

• .NET 8

Gotchas

• Won't work with Azure SQL until Microsoft adds/enables Service Broker support
• Setting values cannot be null (as signified by the RequiredAttribute on ISetting.Value)
• Consider adding ConnectRetryCount and ConnectRetryInterval parameters to your connection string if not already present

Known Issues

• Not tested under load
• Transient failure detection logic is not well tested given the challenges in reproducing these issues

Configuration Options

There are two properties which can be configured (cf. the SqlServerNotificationConfigurationReloaderOptions POCO)

1. ConnectionString - the full connection string for the SQL Server instance
2. TransientErrors - the list of SqlError.Number values that will be treated as transient if present in a thrown SqlException's Errors collection while listening for or processing notifications (ie. reconnection will be attempted if any are present)

Setup

For SqlServerNotificationConfigurationReloader to work it requires Change Tracking to be enable on the Settings table (and therefore also on the database itself) eg:

ALTER DATABASE myDatabase
SET CHANGE_TRACKING = ON (AUTO_CLEANUP = ON);

ALTER TABLE dbo.Settings
ENABLE CHANGE_TRACKING;

Recommend adding your SQL to the migration which adds the Settings table/view (or a new migration if that table/view already exists).

Usage Example

using RAIC.Extensions.Configuration.EntityFrameworkCore;
using RAIC.Extensions.Configuration.EntityFrameworkCore.Extensions;
using RAIC.Extensions.Configuration.EntityFrameworkCore.SqlServer.Extensions;

[Table("settings", Schema = "dbo")] // Need to explicitly set schema somehow, this is one way to do it
public record Setting : ISetting
{
    [Key]
    public required string Key { get; set; }

    [Required]
    public required string Value { get; set; }
}

public class MyDbContext(DbContextOptions<MyDbContext> options) : DbContext(options), ISettingsDbContext<DbSet<Setting>>, ISettingsDbContextFactory<MyDbContext>
{
    public DbSet<Setting> Settings { get; set; }

    public static MyDbContext Create(DbContextOptions<MyDbContext> options) => new(options);
}


var builder = Host.CreateApplicationBuilder(args); // or  WebApplication.CreateBuilder(args); 

// build an initial configuration
builder.Configuration.AddJsonFile("appsettings.json")
                     ...
                     .AddUserSecrets<Program>(); // or wherever your connection string lives

builder.Configuration.AddDbContext<MyDbContext>(dbContextOptions => dbContextOptions.UseSqlServer(builder.Configuration.GetConnectionString("Default")));

...
// Add the SqlServerNotificationConfigurationReloader background service and supporting services to obtain setting reloading functionalty
builder.Services.AddSqlServerNotificationConfigurationReloadService<MyDbContext, Settings>(); // uses connection string from MyDbContext. Other overrides exist if this doesn't work for you - see cods docs

await builder.Build().RunAsync(); // use config as normal

Read more about Configuration and Options on the Microsoft Docs site.