Skip to content

Implementation of IDistributedCache with mariadb and dapper

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.

Implementation of IDistributedCache with mariadb and dapper

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.

Sources for corrections

Post a Comment

Previous Post Next Post