Implementation of IDistributedCache with mariadb and dapper
English original published: 2024-04-01T15:44:00.001+03:00. Original English article
Corrected educational edition. Technical review against primary sources: 2026-10-28. Code examples were not compiled or runtime-tested. The original English publication date is preserved; the Hebrew edition uses its actual publication date, not the English original date.
ASP.NET Core's IDistributedCache provides an abstraction for caching serialized values. This guide illustrates a custom IDistributedCache implementation backed by MariaDB/MySQL using Dapper for lightweight data access. source
This is particularly useful when you want to share cached data across multiple application instances without adding a dedicated caching service like Redis.
Why Use MariaDB for Distributed Caching?
Using MariaDB as a cache store can reuse existing infrastructure, share values across app instances and persist entries across restarts. It also adds database load, storage and operational work; it does not make cache capacity, cleanup or failure handling free.
Database Schema
For a new, dedicated test table on an InnoDB configuration supporting the key length below, use this illustrative schema. The corrected example stores UTC ticks and exact UTF-8 key bytes rather than using text collation or server-local timestamps. Do not apply this as a migration over an existing cache table without a reviewed migration plan.
CREATE TABLE Cache (
CacheKey VARBINARY(1024) NOT NULL PRIMARY KEY,
CacheValue LONGBLOB NOT NULL,
AbsoluteExpirationTicks BIGINT NULL,
SlidingExpirationTicks BIGINT NULL,
LastAccessedTicks BIGINT NOT NULL
) ENGINE=InnoDB;The IDistributedCache Implementation
The corrected educational implementation includes all eight IDistributedCache methods, absolute and sliding expiry, cancellation and a host-managed cleanup service. It has not been compiled or runtime-tested. It is not a production-ready provider. source
// Educational .NET 10 outline. Not compiled or runtime-tested.
using System;
using System.Data;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Dapper;
using Microsoft.Extensions.Caching.Distributed;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using MySqlConnector;
public sealed class DistributedMariaDbCache : IDistributedCache
{
private readonly string _connectionString;
public DistributedMariaDbCache(IConfiguration configuration) =>
_connectionString = configuration.GetConnectionString("connection")
?? throw new InvalidOperationException("Missing connection setting.");
private MySqlConnection NewConnection() => new(_connectionString);
private static long Now() => DateTimeOffset.UtcNow.UtcDateTime.Ticks;
private static byte[] KeyBytes(string key)
{
ArgumentNullException.ThrowIfNull(key);
// Reject malformed UTF-16 rather than merging distinct keys via replacements.
var bytes = new UTF8Encoding(false, true).GetBytes(key);
if (bytes.Length is < 1 or > 1024)
throw new ArgumentException("Key must be 1-1024 UTF-8 bytes.", nameof(key));
return bytes;
}
public sealed class CacheRow
{
public byte[] CacheValue { get; set; } = Array.Empty<byte>();
public long? AbsoluteExpirationTicks { get; set; }
public long? SlidingExpirationTicks { get; set; }
public long LastAccessedTicks { get; set; }
}
public byte[]? Get(string key) => GetAsync(key).GetAwaiter().GetResult();
public async Task<byte[]?> GetAsync(string key, CancellationToken token = default)
{
var keyBytes = KeyBytes(key);
await using var connection = NewConnection();
await connection.OpenAsync(token).ConfigureAwait(false);
await using var transaction = await connection.BeginTransactionAsync(
IsolationLevel.ReadCommitted, token).ConfigureAwait(false);
var row = await connection.QuerySingleOrDefaultAsync<CacheRow>(new CommandDefinition(
"SELECT CacheValue, AbsoluteExpirationTicks, SlidingExpirationTicks, " +
"LastAccessedTicks FROM Cache WHERE CacheKey=@Key FOR UPDATE",
new { Key = keyBytes }, transaction, cancellationToken: token)).ConfigureAwait(false);
if (row is null)
{
await transaction.CommitAsync(token).ConfigureAwait(false);
return null;
}
var now = Now();
bool expired = (row.AbsoluteExpirationTicks is long absolute && now >= absolute)
|| (row.SlidingExpirationTicks is long sliding &&
now - row.LastAccessedTicks >= sliding);
string sql = expired
? "DELETE FROM Cache WHERE CacheKey=@Key"
: "UPDATE Cache SET LastAccessedTicks=@Now WHERE CacheKey=@Key";
await connection.ExecuteAsync(new CommandDefinition(sql,
new { Key = keyBytes, Now = now }, transaction,
cancellationToken: token)).ConfigureAwait(false);
await transaction.CommitAsync(token).ConfigureAwait(false);
return expired ? null : row.CacheValue;
}
public void Refresh(string key) => RefreshAsync(key).GetAwaiter().GetResult();
public async Task RefreshAsync(string key, CancellationToken token = default)
{
// Simplicity, not efficiency: Get also refreshes a still-live sliding entry.
_ = await GetAsync(key, token).ConfigureAwait(false);
}
public void Set(string key, byte[] value, DistributedCacheEntryOptions options) =>
SetAsync(key, value, options).GetAwaiter().GetResult();
public async Task SetAsync(string key, byte[] value,
DistributedCacheEntryOptions options, CancellationToken token = default)
{
var keyBytes = KeyBytes(key);
ArgumentNullException.ThrowIfNull(value);
ArgumentNullException.ThrowIfNull(options);
var now = DateTimeOffset.UtcNow;
DateTimeOffset? absolute = options.AbsoluteExpiration;
if (absolute.HasValue && absolute.Value <= now)
throw new ArgumentOutOfRangeException(nameof(options), "Expiration must be future.");
if (options.AbsoluteExpirationRelativeToNow is TimeSpan relative)
{
if (relative <= TimeSpan.Zero) throw new ArgumentOutOfRangeException(nameof(options));
var relativeLimit = now.Add(relative);
if (!absolute.HasValue || relativeLimit < absolute.Value) absolute = relativeLimit;
}
long? slidingTicks = options.SlidingExpiration?.Ticks;
if (slidingTicks is <= 0) throw new ArgumentOutOfRangeException(nameof(options));
await using var connection = NewConnection();
await connection.OpenAsync(token).ConfigureAwait(false);
const string sql = "INSERT INTO Cache " +
"(CacheKey, CacheValue, AbsoluteExpirationTicks, SlidingExpirationTicks, LastAccessedTicks) " +
"VALUES (@Key,@Value,@Absolute,@Sliding,@Now) ON DUPLICATE KEY UPDATE " +
"CacheValue=@Value, AbsoluteExpirationTicks=@Absolute, " +
"SlidingExpirationTicks=@Sliding, LastAccessedTicks=@Now";
await connection.ExecuteAsync(new CommandDefinition(sql, new {
Key = keyBytes, Value = value, Absolute = absolute?.UtcDateTime.Ticks,
Sliding = slidingTicks, Now = now.UtcDateTime.Ticks
}, cancellationToken: token)).ConfigureAwait(false);
}
public void Remove(string key) => RemoveAsync(key).GetAwaiter().GetResult();
public async Task RemoveAsync(string key, CancellationToken token = default)
{
var keyBytes = KeyBytes(key);
await using var connection = NewConnection();
await connection.OpenAsync(token).ConfigureAwait(false);
await connection.ExecuteAsync(new CommandDefinition(
"DELETE FROM Cache WHERE CacheKey=@Key", new { Key = keyBytes },
cancellationToken: token)).ConfigureAwait(false);
}
public async Task CleanExpiredAsync(CancellationToken token)
{
await using var connection = NewConnection();
await connection.OpenAsync(token).ConfigureAwait(false);
await connection.ExecuteAsync(new CommandDefinition(
"DELETE FROM Cache WHERE " +
"(AbsoluteExpirationTicks IS NOT NULL AND AbsoluteExpirationTicks<=@Now) OR " +
"(SlidingExpirationTicks IS NOT NULL AND @Now-LastAccessedTicks>=SlidingExpirationTicks)",
new { Now = Now() }, cancellationToken: token)).ConfigureAwait(false);
}
}
public sealed class CacheCleanupService : BackgroundService
{
private readonly DistributedMariaDbCache _cache;
private readonly ILogger<CacheCleanupService> _logger;
public CacheCleanupService(DistributedMariaDbCache cache, ILogger<CacheCleanupService> logger)
=> (_cache, _logger) = (cache, logger);
protected override async Task ExecuteAsync(CancellationToken stoppingToken)
{
using var timer = new PeriodicTimer(TimeSpan.FromMinutes(5));
try
{
while (!stoppingToken.IsCancellationRequested)
{
try { await _cache.CleanExpiredAsync(stoppingToken).ConfigureAwait(false); }
catch (OperationCanceledException) when (stoppingToken.IsCancellationRequested) { break; }
catch (Exception ex) { _logger.LogError(ex, "Cache cleanup failed."); }
if (!await timer.WaitForNextTickAsync(stoppingToken).ConfigureAwait(false)) break;
}
}
catch (OperationCanceledException) when (stoppingToken.IsCancellationRequested) { }
}
}Register the Cache in Program.cs
builder.Services.AddSingleton<DistributedMariaDbCache>();
builder.Services.AddSingleton<IDistributedCache>(sp =>
sp.GetRequiredService<DistributedMariaDbCache>());
builder.Services.AddHostedService<CacheCleanupService>();Summary
This MariaDB-backed example illustrates the standard ASP.NET Core IDistributedCache interface without adding Redis or Memcached. Validate the selected MariaDB/MySQL version, InnoDB index limits, concurrent expiration behavior, cancellation, cleanup load and recovery before use. Synchronous methods block while delegating to async operations, and Refresh reads the value for simplicity; optimize only after correctness tests. All app instances need synchronized clocks. source
Use .NET 10-compatible Dapper and MySqlConnector packages plus the ASP.NET Core hosting abstractions. This example limits keys to 1–1024 UTF-8 bytes. The cleanup query can scan the table and has no production batching or retry strategy. Its use is educational; no schema or database command was executed for this article.