Smart Query Caching in C#: Auto-Invalidate on Database Changes

A practical guide to keeping your cache fresh using SqlDependency, version stamps, and distributed invalidation patterns (MemoryCache / Redis).

Why database-aware caching?
Patterns at a glance
SQL Server Query Notifications with SqlDependency
Version-Stamp (Polling-Light) Strategy
Event-Driven Invalidation (App-layer)
Distributed Cache + Redis Pub/Sub
Drop-in C# Cache Wrapper
Best practices & pitfalls

Why database-aware caching?

Caching query results boosts performance and saves database I/O. The risk is serving stale data. The fix involves connecting cache invalidation to fundamental database changes.

Lower latency and lower DB load.
Predictable freshness via automatic invalidation.
Scales from single instances to distributed apps.

Patterns at a glance

SqlDependency: push notifications from SQL Server for supported queries.
Version-Stamps: compare a “last updated” token; refresh only when changed.
Event-Driven: clear caches when your app writes to the DB.
Redis Pub/Sub: broadcast invalidations across instances.

1. SQL Server Query Notifications with SqlDependency

Requirements: SQL Server Service Broker enabled; query must follow notification rules (no SELECT *, no temp tables, etc.). Suitable for small-to-medium workloads.

// Install: System.Data.SqlClient (for SqlDependency)
// Add at startup (once per process):
SqlDependency.Start(connectionString);

// Service
public sealed class ProductsQueryCache
{
    private readonly IMemoryCache _cache;
    private readonly string _cs;

    public ProductsQueryCache(IMemoryCache cache, string connectionString)
    {
        _cache = cache;
        _cs = connectionString;
    }

    public IReadOnlyList<ProductDto> GetTopProducts()
    {
        const string cacheKey = "products:top";
        if (_cache.TryGetValue(cacheKey, out IReadOnlyList<ProductDto> cached))
            return cached;

        using var conn = new SqlConnection(_cs);
        using var cmd = new SqlCommand(@"
            SELECT p.Id, p.Name, p.Price
            FROM dbo.Products AS p
            WHERE p.IsActive = 1
            ORDER BY p.Sales DESC;", conn);

        // Wire dependency BEFORE executing:
        var dep = new SqlDependency(cmd);
        dep.OnChange += (s, e) => _cache.Remove(cacheKey);

        conn.Open();
        using var rdr = cmd.ExecuteReader();

        var items = new List<ProductDto>();
        while (rdr.Read())
        {
            items.Add(new ProductDto
            {
                Id = rdr.GetGuid(0),
                Name = rdr.GetString(1),
                Price = rdr.GetDecimal(2)
            });
        }

        // Cache with sensible absolute cap as a fallback
        _cache.Set(cacheKey, items, TimeSpan.FromMinutes(30));
        return items;
    }
}

// On shutdown:
SqlDependency.Stop(connectionString);

public record ProductDto
{
    public Guid Id { get; init; }
    public string Name { get; init; } = "";
    public decimal Price { get; init; }
}

Tip: Wrap each dependent query in a small service. Re-execute the query on cache miss to re-subscribe after an invalidation.

2. Version-Stamp (Polling-Light) Strategy

Maintain a single monotonic version (e.g., DATETIME2 or ROWVERSION) per entity or aggregate. The cache stores both the data and the version; if the DB version changes, refresh the cache.

-- One row per entity/aggregate
CREATE TABLE dbo.CacheVersion
(
  EntityName   sysname       NOT NULL PRIMARY KEY,
  LastUpdated  DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME()
);

-- Keep it up to date (example trigger)
CREATE OR ALTER TRIGGER dbo.trg_Products_VersionBump
ON dbo.Products
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;
  MERGE dbo.CacheVersion AS cv
  USING (SELECT N'Products' AS EntityName) AS s
  ON cv.EntityName = s.EntityName
  WHEN MATCHED THEN
     UPDATE SET LastUpdated = SYSUTCDATETIME()
  WHEN NOT MATCHED THEN
     INSERT(EntityName, LastUpdated) VALUES (s.EntityName, SYSUTCDATETIME());
END;

public sealed class VersionedCache
{
    private readonly IMemoryCache _cache;
    private readonly string _cs;

    public VersionedCache(IMemoryCache cache, string connectionString)
    {
        _cache = cache;
        _cs = connectionString;
    }

    public async Task<IReadOnlyList<ProductDto>> GetTopProductsAsync(CancellationToken ct = default)
    {
        const string key = "products:top";
        var dbVersion = await GetVersionAsync("Products", ct);

        if (_cache.TryGetValue<(DateTime version, IReadOnlyList<ProductDto> data)>(
            key, out var cached) && cached.version == dbVersion)
        {
            return cached.data;
        }

        var data = await LoadFromDbAsync(ct);
        _cache.Set(key, (dbVersion, data), TimeSpan.FromMinutes(30));
        return data;
    }

    private async Task<DateTime> GetVersionAsync(string entity, CancellationToken ct)
    {
        const string sql = "SELECT LastUpdated FROM dbo.CacheVersion WHERE EntityName = @e";
        await using var cn = new SqlConnection(_cs);
        await using var cmd = new SqlCommand(sql, cn);
        cmd.Parameters.AddWithValue("@e", entity);
        await cn.OpenAsync(ct);
        var val = await cmd.ExecuteScalarAsync(ct);
        return (val == null || val == DBNull.Value) ? DateTime.MinValue : (DateTime)val;
    }

    private async Task<IReadOnlyList<ProductDto>> LoadFromDbAsync(CancellationToken ct)
    {
        const string sql = @"
            SELECT TOP (50) Id, Name, Price
            FROM dbo.Products
            WHERE IsActive = 1
            ORDER BY Sales DESC;";

        await using var cn = new SqlConnection(_cs);
        await using var cmd = new SqlCommand(sql, cn);
        await cn.OpenAsync(ct);
        await using var rdr = await cmd.ExecuteReaderAsync(ct);

        var list = new List<ProductDto>();
        while (await rdr.ReadAsync(ct))
        {
            list.Add(new ProductDto{
                Id = rdr.GetGuid(0),
                Name = rdr.GetString(1),
                Price = rdr.GetDecimal(2)
            });
        }
        return list;
    }
}

Suitable for: cross-DB compatibility, complex queries, and large deployments. Overhead is a tiny metadata read per cache check.

3. Event-Driven Invalidation (Application Layer)

When all writes go through your app, clear relevant cache keys immediately after INSERT/UPDATE/DELETE. No polling, no DB features needed.

public interface ICacheBus
{
    void Invalidate(params string[] keys);
}

public sealed class MemoryCacheBus(IMemoryCache cache) : ICacheBus
{
    public void Invalidate(params string[] keys)
    {
        foreach (var k in keys) cache.Remove(k);
    }
}

public sealed class ProductsService
{
    private readonly string _cs;
    private readonly ICacheBus _bus;

    public ProductsService(string cs, ICacheBus bus)
    {
        _cs = cs; _bus = bus;
    }

    public async Task UpdatePriceAsync(Guid id, decimal price, CancellationToken ct)
    {
        const string sql = "UPDATE dbo.Products SET Price=@p WHERE Id=@id";
        await using var cn = new SqlConnection(_cs);
        await using var cmd = new SqlCommand(sql, cn);
        cmd.Parameters.AddWithValue("@p", price);
        cmd.Parameters.AddWithValue("@id", id);
        await cn.OpenAsync(ct);
        await cmd.ExecuteNonQueryAsync(ct);

        // Immediately invalidate related keys:
        _bus.Invalidate("products:top", $"product:{id}");
    }
}

Note. If other systems write to the DB, pair this with Version-Stamps or database triggers to catch external changes.

4. Distributed Cache + Redis Pub/Sub

In multi-instance setups, store data in Redis and broadcast invalidation events with Pub/Sub so all instances drop stale entries simultaneously.

// Using StackExchange.Redis
public sealed class RedisInvalidator
{
    private readonly IConnectionMultiplexer _mux;
    private const string Channel = "cache:invalidate";

    public RedisInvalidator(IConnectionMultiplexer mux) { _mux = mux; }

    public void Publish(params string[] keys)
    {
        var sub = _mux.GetSubscriber();
        sub.Publish(Channel, string.Join(",", keys));
    }

    public void Subscribe(IMemoryCache localCache)
    {
        var sub = _mux.GetSubscriber();
        sub.Subscribe(Channel, (_, msg) =>
        {
            foreach (var key in msg.ToString().Split(',', StringSplitOptions.RemoveEmptyEntries))
                localCache.Remove(key);
        });
    }
}

Combine this with Version-Stamps for safety: even if a node misses a message (rare), the next read detects version mismatch and refreshes.

5. Drop-in Cache Wrapper (Interface + DI)

A small abstraction to centralize caching and invalidation logic.

public interface IQueryCache
{
    Task<T> GetOrCreateAsync<T>(string key, Func<CancellationToken, Task<T>> factory, TimeSpan ttl,
        CancellationToken ct = default);
    void Remove(params string[] keys);
}

public sealed class DefaultQueryCache(IMemoryCache cache) : IQueryCache
{
    public async Task<T> GetOrCreateAsync<T>(string key, Func<CancellationToken, Task<T>> factory, TimeSpan ttl,
        CancellationToken ct = default)
    {
        if (cache.TryGetValue(key, out T value)) return value;
        value = await factory(ct);
        cache.Set(key, value, ttl);
        return value;
    }

    public void Remove(params string[] keys)
    {
        foreach (var k in keys) cache.Remove(k);
    }
}

// Usage inside a repository:
public sealed class ProductsReadModel(IQueryCache qc, string cs)
{
    public Task<IReadOnlyList<ProductDto>> GetTopAsync(CancellationToken ct)
        => qc.GetOrCreateAsync("products:top", _ => Load(ct), TimeSpan.FromMinutes(30), ct);

    private async Task<IReadOnlyList<ProductDto>> Load(CancellationToken ct)
    {
        // ... DB query as shown earlier ...
        return new List<ProductDto>();
    }
}

Best Practices & Pitfalls

Pick a key strategy: prefix by aggregate (products:*) so you can invalidate in groups.
Set a safety TTL: even with perfect invalidation, It protects you from edge cases.
Cache shape matters: cache the final DTO you serve, not raw rows.
Avoid “stampedes”: add jitter to TTL or use a single-flight mutex on rebuild.
Warm hot paths on app start if latency spikes matter.
Measure: track hit ratio, rebuild latency, DB load before/after.
SqlDependency limits: ensure Service Broker is on; keep queries notification-compatible.

Table of Contents