A practical guide to keeping your cache fresh using SqlDependency, version stamps, and distributed invalidation patterns (MemoryCache / Redis).
Table of Contents
- 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.