Setting up SQL Server IDistributedCache with migration in ASP.NET Core
Image from Pexels by Kelly Lacy

Setting up SQL Server IDistributedCache with migration in ASP.NET Core

Embedding SQL Server caching in a project with EFCore migration

Caching data using Microsoft SQL Server may not be the most popular way of caching data because of the performance comparing to caching data to Redis, but it is maybe the most convenient way to keep your state of the application outside the process itself. Beside, if you are already using SQL Server database, you can just move the caching infrastructure to a separate schema inside the same database and keep using it until requirements are met for cache storage of better performance (like Redis for example).

This makes infrastructure not so complex and less dependent, again until the requirements for something faster are met.

In-memory caching is the one with best performing one but it is not distributed, meaning once your process goes down because of an error or simply because you are releasing the the version, you are loosing your cached data. In some cases this can be the state of the application which is in this case destroyed and live user session is corrupted. This is breaking the concept of statelessness of RESTful service and it is not recommended to use in-memory caching for production.

Note

Down the line, thanks to IDistributedCache interface built-in into .NET Core, we can easily switch to one or another infrastructural option depending on our needs by just injecting the different implementation without any, or minimal changes to our application code.

So now when we roughly have an idea when we should use SQL Server caching over other options, we proceed to see how to set it up, what are the problem with documented way of setting up the caching using SQL Server and how to overcome these problems.

Setting up SQL Server cache via global tool

The most convenient way to have you SQL Server cache up and running is to first install the global tool which you will use to setup the database and if needed separate schema where your caching table will sit in the database

dotnet tool install --global dotnet-sql-cache --version 5.0.1 --ignore-failed-sources

Once the CLI tool is installed you just need to invoke it with connection string and additional parameters to have the caching table in place in your database

dotnet sql-cache create "data source=.;initial catalog=CachingSample;user id=sa;password=sa;" app Cache

After the tool command executes you will have Cache table in app schema in your database in place and ready to be used by SQL Server distributed caching library. Now you just need to add SQL Server caching to your services and you will be able to access the cache via IDistributedCache interfce injected via class constructor in your ASP.NET Core application

        public void ConfigureServices(IServiceCollection services)
        {
            ...
            services.AddDistributedSqlServerCache(options =>
            {
                options.ConnectionString = Configuration.GetConnectionString("CacheDbConnection");
                options.SchemaName = "app";
                options.TableName = "Cache";
            });
            ...
        }
    

Let's see how caching storage actually looks under the hood.

Caching Table

As you can see, the table is fairly simple with a primary key for Id column which is the key for your cache record and an index for ExpiresAtTime so that library can easily find a key that expires at certain time.

This is pretty easy way to do it but in order to have this on all environment you need to probably rely on your CI/CD setup where your release will have to run the tool to create the table. Additionally, you can just easily script this table and execute it to any environment you need this table for SQL Server caching purpose.

USE [CachingSample]
GO
/****** Object:  Table [app].[Cache]    Script Date: 2020-12-20 13:57:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [app].[Cache](
	[Id] [nvarchar](449) NOT NULL,
	[Value] [varbinary](max) NOT NULL,
	[ExpiresAtTime] [datetimeoffset](7) NOT NULL,
	[SlidingExpirationInSeconds] [bigint] NULL,
	[AbsoluteExpiration] [datetimeoffset](7) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

    

This is all pretty much easy to do, but if your application uses EF Core to access it's data and you have your migrations triggered from the code, introducing SQL Server cache with all these automated tasks to eaither run a tool or a script can be time consuming.

Would it be nice if you could just do this from the pipeline as you would invoke migration of the rest of the application database infrastructure?

Using EF Core migrations to setup SQL Server caching

Since this part is purely related to infrastructure, it is not required to be in relation with any part of the application and therefore can be developed as an independent library, which what I will do in this example. 

Note

Complete library code can be found inside public GitHub repository https://github.com/dejanstojanovic/efcore-sqlserver-caching

To be able to setup our caching table via EF Core migration we first need to write it's representation in EF Core components. As a first and main component is an entity or a model tart will represent the table structure itself

    internal partial class Cache
    {
        public string Id { get; set; }
        public byte[] Value { get; set; }
        public DateTimeOffset ExpiresAtTime { get; set; }
        public long? SlidingExpirationInSeconds { get; set; }
        public DateTimeOffset? AbsoluteExpiration { get; set; }
    }
    

This is just a skeleton of a table, we still need to impose some rules and indexes and that is done by a separate configuration class that will be later fused with our model inside th DbContext we are going to use

    internal class CacheConfiguration : IEntityTypeConfiguration<Cache>
    {
        readonly SqlServerCacheOptions _options;
        public CacheConfiguration(IOptions<SqlServerCacheOptions> options)
        {
            _options = options.Value;
        }

        public void Configure(EntityTypeBuilder<Cache> builder)
        {
            builder.ToTable(name:_options.TableName, schema: _options.SchemaName);

            builder.HasIndex(e => e.ExpiresAtTime);

            builder.Property(e => e.Id)
                .IsRequired()
                .HasMaxLength(449);

            builder.HasKey(e => e.Id);

            builder.Property(e => e.Value).IsRequired();
        }
    }
    
Note

You may notice that all classes are internal and not public. The reason for this is that we do not want to expose these classes outside our library which will be used by our application. All we need in the application and what you will see in the further text are extension methods that abstract this whole logic

The migration can be easily generated now with ef core global tool

dotnet ef migrations add Init_Cache -o Migrations -c CacheDbContext

but we need to alter our generated migration as we want to inject IOptions<SqlServerCacheOptions> which hold name of the table and schema name and which are already set with extension method for adding SQL Server caching service to service collection

    internal partial class Init_Cache : Migration
    {
        readonly SqlServerCacheOptions _options;
        public Init_Cache(IOptions<SqlServerCacheOptions> options)
        {
            _options = options.Value;
        }

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.EnsureSchema(
                name: _options.SchemaName);

            migrationBuilder.CreateTable(
                name: _options.TableName,
                schema: _options.SchemaName,
                columns: table => new
                {
                    Id = table.Column<string>(type: "nvarchar(449)", maxLength: 449, nullable: false),
                    Value = table.Column<byte[]>(type: "varbinary(max)", nullable: false),
                    ExpiresAtTime = table.Column<DateTimeOffset>(type: "datetimeoffset", nullable: false),
                    SlidingExpirationInSeconds = table.Column<long>(type: "bigint", nullable: true),
                    AbsoluteExpiration = table.Column<DateTimeOffset>(type: "datetimeoffset", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey($"PK_{_options.TableName}", x => x.Id);
                });

            migrationBuilder.CreateIndex(
                name: $"IX_{_options.TableName}_ExpiresAtTime",
                schema: _options.SchemaName,
                table: _options.TableName,
                column: "ExpiresAtTime");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: _options.TableName,
                schema: _options.SchemaName);
        }
    }
    

Unfortunately, you cannot inject service directly to ef core Migration class. Instead you have to do it indirectly by injection this dependency in the DbContext and then in the DbContext itself you need to replace default MigrationsAssembly class instance with your custom which should look something like this.

    internal class CacheMigrationAssembly : MigrationsAssembly
    {
        readonly IOptions<SqlServerCacheOptions> _cacheOptions;
        readonly CacheDbContext _cacheDbContext;

        public CacheMigrationAssembly(
            ICurrentDbContext currentContext,
            IDbContextOptions options,
            IMigrationsIdGenerator idGenerator,
            IDiagnosticsLogger<DbLoggerCategory.Migrations> logger) : base(currentContext, options, idGenerator, logger)
        {
            _cacheDbContext = currentContext.Context as CacheDbContext;
            _cacheOptions = _cacheDbContext.CacheOptions;

        }
        public override Migration CreateMigration(TypeInfo migrationClass, string activeProvider)
        {
            var hasCtorWithCacheOptions = migrationClass
                    .GetConstructor(new[] { typeof(IOptions<SqlServerCacheOptions>) }) != null;

            if (hasCtorWithCacheOptions)
            {
                var instance = (Migration)Activator.CreateInstance(migrationClass.AsType(), _cacheOptions);
                instance.ActiveProvider = activeProvider;
                return instance;
            }

            return base.CreateMigration(migrationClass, activeProvider);
        }
    }
    

The logic of this custom MigrationsAssembly class is to try to find the specific constructor of the migration and to inject the dependency which is previously injected to the DbContext. Unfortuntely, you cannot inject your dependency directly to your implementation of MigrationsAssembly class, but you can do it indirectly by previously injecting the dependency to the DbContext

    internal partial class CacheDbContext : DbContext
    {
        readonly IEntityTypeConfiguration<Cache> _configuration;
        readonly IOptions<SqlServerCacheOptions> _cacheOptions;

        public IOptions<SqlServerCacheOptions> CacheOptions
        {
            get => _cacheOptions;
        }

        public CacheDbContext(
            IOptions<SqlServerCacheOptions> cacheOptions,
            IEntityTypeConfiguration<Cache> configuration) : base()
        {
            _configuration = configuration;
            _cacheOptions = cacheOptions;
        }

        public CacheDbContext(
            IOptions<SqlServerCacheOptions> cacheOptions,
            DbContextOptions<CacheDbContext> options,
            IEntityTypeConfiguration<Cache> configuration)
            : base(options)
        {
            _cacheOptions = cacheOptions;
            _configuration = configuration;
        }

        public virtual DbSet<Cache> Cache { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.ReplaceService<IMigrationsAssembly, CacheMigrationAssembly>();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.ApplyConfiguration(_configuration);
        }
    }
    

We have pretty much all pieces put together, all is left to be done is to register our DbContext to services collection and to actually invoke it's migration method from the pipeline. As I mentioned before, this is the part where we will add only public classes that will be visible outside of the library that takes care of SQL Server caching infrastructure from the code, without any need for additional tool or script run.

Registering and executing SQL Server cache migrations via extension methods

We got to the final step and that is consuming the logic which will create the caching table and using ef core services configured for SQL Server cache. These are the only classes and methods which are declared public

public static class DependencyInjection
{
/// <summary>
/// Adds Microsoft SQL Server caching infrastructure using <see cref="SqlServerCacheOptions"/> values set previously by <see cref="SqlServerCachingServicesExtensions.AddDistributedSqlServerCache(IServiceCollection, Action{SqlServerCacheOptions})"/> method"
/// </summary>
/// <param name="services">The <see cref="IServiceCollection"/> of application services to be injected</param>
/// <param name="dbContextOptions">DbContext options for cache db context</param>
public static void AddSqlServerCachingInfrastructure(this IServiceCollection services, Action<SqlServerCacheDbContextOptions> dbContextOptions = null)
{
services.AddScoped<IEntityTypeConfiguration<Cache>, CacheConfiguration>();
if(dbContextOptions!=null)
services.Configure<SqlServerCacheDbContextOptions>(dbContextOptions);
using (var provider = services.BuildServiceProvider())
{
var cacheOptions = provider.GetService<IOptions<SqlServerCacheOptions>>().Value;
var cacheDbContextOptions = provider.GetService<IOptions<SqlServerCacheDbContextOptions>>().Value;
services.AddDbContext<CacheDbContext>(options =>
{
options.UseSqlServer(cacheOptions.ConnectionString, o =>
{
           o.MigrationsHistoryTable(
                            cacheDbContextOptions.MigrationHistoryTable,
                            cacheOptions.SchemaName);
                    });
                });
            }
        }

        /// <summary>
        /// Executes migration to create the table for Microsoft SQL Server based caching
        /// </summary>
        /// <param name="app">ASP.NET Core pipeline <see cref="IApplicationBuilder"/> instance</param>
        public static void SetupSqlServerCachingInfrastructure(this IApplicationBuilder app)
        {
            using (var serviceScope = app.ApplicationServices
                .GetRequiredService<IServiceScopeFactory>()
                .CreateScope())
            {
                using (var context = serviceScope.ServiceProvider.GetService<CacheDbContext>())
                {
                    context.Database.Migrate();
                }
            }
        }
    }
    

As addition to be able to have control over migrations history table name and any additional configurations which may be required in the future, a simple options class is also introduced which by default sets ef core migration table to a default one

    public class SqlServerCacheDbContextOptions : IOptions<SqlServerCacheDbContextOptions>
    {
        public SqlServerCacheDbContextOptions()
        {
            MigrationHistoryTable = "__EFMigrationsHistory";
        }
        public String MigrationHistoryTable { get; set; }
        public SqlServerCacheDbContextOptions Value => this;
    }
    

This is necessary when in case you use one database, you can easily separate your application domain migration from the cache migration executed in the same database.

Using extension methods in the main application 

All we are left to do is to consume the logic and extension method mentioned above, inside our application. To make things even easier, I packed this library into a NuGet package so simple adding of a reference to this package will let you access extension methods in your Startup.cs class

Caching Nuget

dotnet add package Caching.SqlServer.Infastructure --version 1.0.1

Once the reference to this nuget package is added to your project, there are two methods you need to slightly change

        public void ConfigureServices(IServiceCollection services)
        {
            ...
            services.AddDistributedSqlServerCache(options =>
            {
                options.ConnectionString = Configuration.GetConnectionString("CacheDbConnection");
                options.SchemaName = "app";
                options.TableName = "Cache";
            }).AddSqlServerCachingInfrastructure();
            ...
        }
    

This will add caching DbConext under the hood, but we still need to initiate the migration for the caching table. This is done from the pipeline setup in Configure method in Startup class

        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            ...
            app.SetupSqlServerCachingInfrastructure();
            ...
        }
    

To test out the caching, I created a simple controller method that generates guid value and caches it. This means you will always get the same value at this endpoint as only first time guid is generated and every next time it is retrieved from the cache

    [Route("api/[controller]")]
    [ApiController]
    public class ValuesController : ControllerBase
    {
        readonly IDistributedCache _cache;

        public ValuesController(IDistributedCache cache)
        {
            _cache = cache;
        }

        [HttpGet]
        public async Task<IActionResult> Get()
        {
            var key = "Sample-Key";
            var value = await _cache.GetStringAsync(key);

            if(!string.IsNullOrWhiteSpace(value))
                return Ok(value);

            value = Guid.NewGuid().ToString();
            await _cache.SetStringAsync(key,value);

            return Ok(value);
        }
    }
    

Caching Swagger

References

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.


About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN  Loginlinkedin Logintwitter Logingoogleplus Logingoogleplus

JavaScript

read more

SQL/T-SQL

read more

Umbraco CMS

read more

PowerShell

read more

Comments for this article