Caching usually starts with a small frustration: the application keeps loading the same data, even though that data changes much less often than it is read.
I wanted a way to cache those values without repeating serialization, expiration settings and storage calls throughout the application. That led me to a small ICacheProvider around .NET's IDistributedCache, with SQL Server as the backing store.
I have used this approach in FileStore and ShortenUrl, but the idea is reusable. A product catalogue, a configuration lookup or a reference-data service can use the same structure.
Why SQL Server?
If an application already uses SQL Server, a SQL-backed cache can be a practical starting point. Several application instances can read the same cache table, and cached values are separate from the memory of any one web process.
That does not make every SQL lookup faster. Reading a cached row still requires a database call. The benefit needs to come from avoiding something more expensive: several queries, a large calculation or a remote request.
I would measure that difference before choosing it for a busy workload. Cache traffic also consumes database connections, CPU and I/O. A separate cache database can help isolate it from business data, although using the same SQL instance still shares resources.
Give each layer one job
The application should be able to ask for an object by key. It should not need to know how that object becomes bytes or which SQL table stores it.
The structure is simple:
- The application decides what to cache and when to invalidate it.
- ICacheProvider handles typed values, JSON and expiration.
- IDistributedCache provides the storage operations.
- The SQL Server provider implements those operations against a cache table.
The wrapper is optional. IDistributedCache is already an abstraction; my extra interface earns its place by removing repeated object-handling code.
What the original provider does
The providers in my two repositories have the same main design. For example, the original asynchronous store method serializes an object to JSON, converts it to UTF-8 bytes and gives those bytes to IDistributedCache:
public async Task<bool> StoreAsync<T>(
string key, T data, DateTimeOffset expiry)
{
string str = JsonSerializer.Serialize(data);
byte[] bytes = Encoding.UTF8.GetBytes(str);
await _distributed.SetAsync(key, bytes,
new DistributedCacheEntryOptions
{
AbsoluteExpiration = expiry
});
return true;
}
The overload without an expiry uses one day from the current UTC time. That is a default in those projects, not a lifetime I would apply to every kind of data.
The original TryRetrieveAsync<T> returns default when an entry is missing or JSON cannot be deserialized. It does not catch SQL connection errors. Its StoreAsync method returns true after a successful write; a failed write throws rather than returning false.
Those details matter when deciding what a caller can safely assume.
A smaller interface for a new application
For the rest of this article, I will use an adapted example rather than copy the historical provider unchanged. It keeps the same design, uses asynchronous methods and cancellation tokens, and limits cached values to reference types so null can clearly mean a miss.
The interface does not include ExistsAsync. Checking whether a key exists and then reading it would make two storage calls, and the entry could expire between them.
public interface ICacheProvider
{
Task<T?> GetAsync<T>(
string key,
CancellationToken cancellationToken = default)
where T : class;
Task SetAsync<T>(
string key,
T value,
TimeSpan lifetime,
CancellationToken cancellationToken = default)
where T : class;
Task RemoveAsync(
string key,
CancellationToken cancellationToken = default);
}
This example assumes a modern .NET project with nullable reference types and implicit usings enabled. The contract deliberately does not cache null. If an application needs negative caching, I would represent that explicitly with a result object and a short lifetime.
Implement the wrapper
The following implementation uses System.Text.Json. Its methods return a typed object, store an object for a specified lifetime, or remove a key.
using System.Text.Json;
using Microsoft.Extensions.Caching.Distributed;
using Microsoft.Extensions.Logging;
public sealed class CacheProvider : ICacheProvider
{
private readonly IDistributedCache _cache;
private readonly ILogger<CacheProvider> _logger;
public CacheProvider(
IDistributedCache cache,
ILogger<CacheProvider> logger)
{
_cache = cache;
_logger = logger;
}
public async Task<T?> GetAsync<T>(
string key,
CancellationToken cancellationToken = default)
where T : class
{
var bytes = await _cache.GetAsync(key, cancellationToken);
if (bytes is null)
return null;
try
{
return JsonSerializer.Deserialize<T>(bytes);
}
catch (JsonException)
{
_logger.LogWarning(
"A cached value could not be deserialized as {Type}.",
typeof(T).Name);
return null;
}
}
public Task SetAsync<T>(
string key,
T value,
TimeSpan lifetime,
CancellationToken cancellationToken = default)
where T : class
{
if (value is null)
throw new ArgumentNullException(nameof(value));
if (lifetime <= TimeSpan.Zero)
throw new ArgumentOutOfRangeException(nameof(lifetime));
var bytes = JsonSerializer.SerializeToUtf8Bytes(value);
var options = new DistributedCacheEntryOptions
{
AbsoluteExpirationRelativeToNow = lifetime
};
return _cache.SetAsync(key, bytes, options, cancellationToken);
}
public Task RemoveAsync(
string key,
CancellationToken cancellationToken = default)
=> _cache.RemoveAsync(key, cancellationToken);
}
A malformed JSON value becomes a miss and produces a warning without logging its contents. This is a limited policy: unsupported types and storage failures still propagate. I would cache small, deliberate DTOs rather than entire tracked entities or object graphs.
The code also avoids deleting the malformed value during the read. A separate remove could race with another request that has already replaced it. A successful reload will overwrite the value; a versioned key also helps when its shape changes.
Register SQL Server as the backing store
Add the Microsoft.Extensions.Caching.SqlServer package in a version compatible with the application's target framework. The dependency registration can then look like this:
using Microsoft.Extensions.DependencyInjection;
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDistributedSqlServerCache(options =>
{
options.ConnectionString = builder.Configuration
.GetConnectionString("CacheDb")
?? throw new InvalidOperationException(
"The CacheDb connection string is missing.");
options.SchemaName = "dbo";
options.TableName = "DistributedCache";
});
builder.Services.AddSingleton<ICacheProvider, CacheProvider>();
// Register the application's other services and endpoints here.
var app = builder.Build();
app.Run();
The singleton wrapper contains no request-specific state and does not depend on a scoped repository. Registering it as a singleton does not make the values local to that process; the selected IDistributedCache provider still stores them in SQL Server.
Keep the connection string in the application's protected configuration. Do not copy a real database password into a blog example or source repository.
Create the cache table before using it
Service registration does not create the SQL table. Microsoft's sql-cache tool can generate the table and indexes expected by the provider. Install the Microsoft.Extensions.Caching.SqlConfig tool in a version compatible with your environment, then run the creation command against an intended development database.
For example, after installing the tool, this command targets a local development database named CacheDemo that must already exist:
dotnet sql-cache create "Server=(localdb)\MSSQLLocalDB;Database=CacheDemo;Integrated Security=True;" dbo DistributedCache
Treat production table creation as a reviewed deployment step. The schema and table name must match the registration. Use an appropriate runtime database identity and separate table-creation permissions where needed.
FileStore creates its cache table through a FluentMigrator migration instead. That is another way to manage the schema, but the table and indexes still need to match the SQL cache provider. I would use the supported tool's output as the reference when adding this to a new application.
Read from the cache, then load on a miss
Here is an example using a product lookup. ProductDto and IProductRepository are application-specific contracts, not cache-library types:
public sealed record ProductDto(Guid Id, string Name, decimal Price);
public interface IProductRepository
{
Task<ProductDto?> FindAsync(
Guid tenantId, Guid productId, CancellationToken token);
Task UpdateAsync(
Guid tenantId, ProductDto product, CancellationToken token);
}
public sealed class ProductService
{
private readonly ICacheProvider _cache;
private readonly IProductRepository _repository;
public ProductService(
ICacheProvider cache, IProductRepository repository)
{
_cache = cache;
_repository = repository;
}
private static string Key(Guid tenantId, Guid productId)
=> $"catalog:v1:tenant:{tenantId:N}:product:{productId:N}";
public async Task<ProductDto?> GetAsync(
Guid tenantId, Guid productId, CancellationToken token)
{
var key = Key(tenantId, productId);
var cached = await _cache.GetAsync<ProductDto>(key, token);
if (cached is not null)
return cached;
var product = await _repository.FindAsync(
tenantId, productId, token);
if (product is not null)
await _cache.SetAsync(key, product,
TimeSpan.FromMinutes(10), token);
return product;
}
public async Task UpdateAsync(
Guid tenantId, ProductDto product, CancellationToken token)
{
await _repository.UpdateAsync(tenantId, product, token);
await _cache.RemoveAsync(Key(tenantId, product.Id), token);
}
}
This is the cache-aside pattern: try the cache, load from the source on a miss, then store the result for later requests.
The tenant ID must come from a trusted, authorized application context. Putting a tenant ID in a cache key does not replace access control. The repository must also enforce the correct ownership boundary.
Choose keys and lifetimes deliberately
The example key includes an application prefix, a payload version, a tenant and the product ID. That avoids accidental collisions when applications share a table and reduces the risk of reading another tenant's entry.
The v1 segment is useful when the cached DTO changes. A new payload shape can use v2 while old entries expire. Keep keys bounded and avoid including secrets, personal data or entire URLs containing tokens.
Ten minutes is only an example. Reference data may tolerate a longer lifetime; prices or account state may need much tighter rules. Absolute expiration gives the entry a maximum lifetime. Sliding expiration can extend an entry while it is used; if freshness matters, give it an absolute limit too.
Expired entries are treated as unavailable by the provider. Their physical removal is a separate cleanup concern; expiration does not mean a background task deletes every row at the exact expiry instant.
Invalidate after a successful change
The update example removes the cache entry only after the repository update succeeds. The next read can then rebuild it from the source.
This is helpful, but it is not a transaction across the business write and the cache. A concurrent read can fetch old data before the update and write it into the cache after removal. Removal can also fail after the database update has committed.
A short lifetime bounds some staleness. Workflows that need stronger guarantees need a more deliberate design, such as version checks or reliable invalidation events with retries. Lists and derived values also need invalidation; removing the product key alone does not refresh every cached catalogue page.
Decide what happens when the cache fails
The example propagates cache storage failures. That is intentional and visible in the code, but it may not be the policy you want for an optional performance feature.
For optional caching, I would consider falling back to the source when a cache read fails and returning a successful source result even if a cache write fails. That needs targeted exception handling, logging and monitoring. Cancellation should still propagate, and a fallback should not hide a failing source database or overload it during a cache outage.
I would keep the policy in one place rather than scatter broad catch blocks throughout the application.
What this small wrapper does not solve
Two requests can miss the same key and both load it. IDistributedCache does not turn the read-load-write sequence into one atomic operation. A lock inside one process also does not coordinate several application servers.
For expensive or popular keys, I would look at request coalescing or an appropriate distributed coordination strategy. I would also measure payload size, hit rate, SQL latency, cache failures and invalidation failures before deciding the design is enough.
Caching through SQL Server is useful when it avoids work and fits the application's operational setup. The reusable part is the boundary: callers work with typed values and explicit lifetimes, while storage stays behind an interface.
Source and further reading
The original versions of this wrapper are available in FileStore and ShortenUrl. The adapted example above adds a smaller contract, cancellation support and clearer miss behavior; it is not a verbatim copy of either repository.
See Microsoft's distributed caching documentation for SQL Server setup and the provider's configuration options.