How to Use SQL Server Based Distributed Cache in ASP.NET Core

ASP.NET Core supports also distributed cache solutions. Out-of-box there is support for SQL Server and Redis based distributed caches. This blog post focuses to SQL Server based cache we can use when there are really no better options.

We don’t need distributed cache with solutions running on one box. But if we have cluster of web servers or we have multiple instances of application running on cloud then we cannot use local memory cache anymore. These caches get filled at different times and they are not in synch meaning that one box may show older content while the other is showing up-to-date content.

IDistributedCache interface

All distributed cache implementations follow IDistributedCache interface. It has methods for synchronous and asynchronous calls. How these calls are implemented is up to developer who builds the implementation of cache.

public interface IDistributedCache
{
    byte[] Get(string key);
    Task<byte[]> GetAsync(string key);
    void Refresh(string key);
    Task RefreshAsync(string key);
    void Remove(string key);
    Task RemoveAsync(string key);
    void Set(string key, byte[] value, DistributedCacheEntryOptions options);
    Task SetAsync(string key, byte[] value, DistributedCacheEntryOptions options);
}

Preparing application for SQL Server cache

We start with configuring our project to support SQL Server distributed cache and related tooling. FIrst add reference to SqlServer cache NuGet package:

• Microsoft.Extensions.Caching.SqlServer

After this unload the project, open project file and make sure there is CLI tools reference to SQL Server command-line tools.

<ItemGroup>
  <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="1.0.0" />
  <DotNetCliToolReference Include="Microsoft.Extensions.Caching.SqlConfig.Tools" Version="1.0.0-msbuild3-final" />
</ItemGroup>

We are ready now to create database tables for cache. For this we have to open command-line and run the following command:

• dotnet sql-cache create <connection string> <schema> <table>

As a result the following table is created to given database. In my case the table name is CacheTable.

CREATE TABLE [dbo].[CacheTable](
    [Id] [nvarchar](449) NOT NULL,
    [Value] [varbinary](max) NOT NULL,
    [ExpiresAtTime] [datetimeoffset](7) NOT NULL,
    [SlidingExpirationInSeconds] [bigint] NULL,
    [AbsoluteExpiration] [datetimeoffset](7) NULL,
 CONSTRAINT [pk_Id] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
       ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
CREATE NONCLUSTERED INDEX [Index_ExpiresAtTime] ON [dbo].[CacheTable]
(
    [ExpiresAtTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
       SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
       ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
       ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Now we are done with preparation work and it’s time to get to some real code.

Configuring SQL Server cache

Before we can use caching we have to introduce required components to our application. To do this we have to add the following block of code to Configure() method of Startup class.

services.AddDistributedSqlServerCache(o =>
{
    o.ConnectionString = Configuration["ConnectionStrings:Default"];
    o.SchemaName = "dbo";
    o.TableName = "Cache";
});

Let’s use distributed cache in default controller. For this we inject the instance of IDistributedCache to controller through its constructor.

public class HomeController : Controller
{
    private readonly IDistributedCache _cache;
 
    public HomeController(IDistributedCache cache)
    {
        _cache = cache;
    }
    public async Task<IActionResult> Index()
    {
        await _cache.SetStringAsync("TestString", "TestValue");
 
        var value = _cache.GetString("TestString");
 
        return View();
    }
}

Let’s put breakpoint to after reading string from cache and run the application. The screenshot below shows the result.

We can control absolute and sliding expiration through cache options like shown in the following code.

public async Task<IActionResult> Index()
{
    var options = new DistributedCacheEntryOptions
    {
        AbsoluteExpiration = DateTime.Now.AddHours(1)
    };
 
    await _cache.SetStringAsync("TestString", "TestValue", options);
 
    var value = _cache.GetString("TestString");
 
    return View();
}

Wrapping up

We started with IDistributedCache interface and moved then to SQL Server based distributed cache provided by Microsoft. There are tools that help us create cache table and we had to add reference to these tools manually by editing project file. To use cache we have to inject IDistirbutedCache to controllers and other components using dependency injection. IDistributedCache offers synchronous and asynchronous methods to handle cache. There are also options class available that helps us to control expiration of cache items.