QueryCache.Dapper 0.1.0

QueryCache

Query-result cache for EF Core and Dapper on .NET 10, in process or in any HybridCache (Redis, FusionCache...).

QueryCache keeps query results in memory, keyed by the SQL, its parameter values and the database it ran against. A cache hit costs a few microseconds and never touches the database. Concurrent misses for the same query run it once. With EF Core, SaveChanges drops the entries that read the tables it wrote to.

Install

dotnet add package QueryCache.EFCore   # or QueryCache.Dapper

using QueryCache.EFCore;

services.AddDbContext<AppDb>(o => o.UseSqlServer(cs).UseQueryCacheInvalidation());

var active = db.Users.Where(u => u.Active).Include(u => u.Roles).AsSplitQuery();
var users = await active.ToListCachedAsync(TimeSpan.FromMinutes(5), ct);
// SaveChanges touching Users or Roles drops that entry; ExecuteUpdate/raw SQL need await active.InvalidateCacheAsync(ct)

var revenue = await db.Orders.Where(o => o.Paid).Select(o => o.Total).SumCachedAsync(TimeSpan.FromMinutes(1), ct);

using QueryCache.Dapper;

var row = await conn.ToCacheQuery<int>(new CommandDefinition("select id from t where x = @X", new { X = 1 }))
    .QueryFirstOrDefaultAsync(TimeSpan.FromMinutes(5), ct);

Distributed cache

By default entries live in process. To share them (and their invalidation) between instances, hand QueryCache a HybridCache at startup. For several instances use FusionCache with a Redis backplane:

builder.Services.AddFusionCache()
    .WithSerializer(new FusionCacheSystemTextJsonSerializer(new JsonSerializerOptions { PreferredObjectCreationHandling = JsonObjectCreationHandling.Populate }))
    .WithDistributedCache(new RedisCache(new RedisCacheOptions { Configuration = redis }))
    .WithBackplane(new RedisBackplane(new RedisBackplaneOptions { Configuration = redis }))
    .AsHybridCache();
var app = builder.Build();
QueryCacheStore.HybridCache = app.Services.GetRequiredService<HybridCache>();

Keys and tags are SHA-256 digests (no SQL or connection string in Redis). Tested against Redis with two caches per test standing in for two instances:

So with Microsoft's HybridCache, only rely on SaveChanges invalidation within one instance, or keep expirations short. Other trade-offs:

• A hit deserializes, so it costs more than the in-process hit and grows with the row count; callers get their own copies.
• Single-flight is per process.

Navigation properties

In process nothing is serialized, so any entity graph is cached as is. In a distributed cache the result goes through the cache's serializer (System.Text.Json by default), and navigations are where that breaks. Projections (Select into a DTO or record) avoid all of it. For entities:

A result the serializer rejects (a cycle without Preserve) is still returned, just not stored. Microsoft's HybridCache drops it silently; with FusionCache QueryCache records it on querycache.store.failures. Either way that query runs against the database every time, so watch that counter or querycache.misses without hits.

Serializer options with each cache:

var json = new JsonSerializerOptions { ReferenceHandler = ReferenceHandler.Preserve };

// FusionCache
builder.Services.AddFusionCache().WithSerializer(new FusionCacheSystemTextJsonSerializer(json)) /* ... */;

// Microsoft HybridCache: a serializer factory, such as JsonSerializerFactory in tests/QueryCache.IntegrationTests/RedisTests.cs
builder.Services.AddHybridCache().AddSerializerFactory(new JsonSerializerFactory(json));

Benchmarks

Direct query against a cache hit, on in-memory SQLite (no network or disk, so these ratios are the floor; against a real database server the gap is far larger):

A hit costs the same whatever the row count. A miss adds ~32 μs for EF Core and ~2–11 μs for Dapper over the direct query. Methodology, miss costs and how to run them: Benchmarks.

Notes

• Any LINQ/EF operator can come before the cached call. EF results are loaded with no tracking. Relational providers only (the key comes from CreateDbCommand).
• Lists are read-only (IReadOnlyList<T>) because every caller gets the same instance; do not mutate the entities either.
• Key: SQL + parameter values (compared exactly, arrays item by item) + connection string without password.
• Transactions: reads inside a transaction (EF, Dapper command.Transaction, or TransactionScope) skip the cache.
• SaveChanges (with UseQueryCacheInvalidation()): drops entries that read the written tables, and again on commit. ExecuteUpdate, ExecuteDelete, raw SQL and Dapper writes are not seen; call InvalidateCacheAsync(ct) for those.
• SumCachedAsync / MaxCachedAsync work on a projection: query.Select(x => x.Price).SumCachedAsync(...). Each operator has its own entry, so First never answers Single.
• No rows (empty collections, null, 0, false): returned, never cached.
• Single-flight: concurrent misses run the query once and share its result or its exception.
• QueryCacheStore.Capacity sets the in-process entries kept per result type (default 128); set it at startup. Timeout.InfiniteTimeSpan keeps an entry until removed or evicted; zero or negative expirations throw.
• To skip the cache, call EF/Dapper directly.
• Metrics (System.Diagnostics.Metrics, meter QueryCacheStore.MeterName = "QueryCache"): querycache.hits, querycache.misses, querycache.fill.duration (s), querycache.store.failures, tagged with querycache.type. With OpenTelemetry: .WithMetrics(m => m.AddMeter(QueryCacheStore.MeterName)).

Build

dotnet restore QueryCache.slnx
dotnet build QueryCache.slnx --configuration Release
dotnet test --project tests/QueryCache.Tests/QueryCache.Tests.csproj --configuration Release
dotnet test --project tests/QueryCache.IntegrationTests/QueryCache.IntegrationTests.csproj --configuration Release   # needs Docker: SQL Server, PostgreSQL and Redis via Testcontainers
dotnet run --project benchmarks/QueryCache.Benchmarks/QueryCache.Benchmarks.csproj --configuration Release -- --filter *

Public API changes need an entry in src/*/PublicAPI/PublicAPI.Unshipped.txt (the build fails with RS0016 otherwise); python3 .github/scripts/add_missing_public_api.py adds them. A release moves them to PublicAPI.Shipped.txt. New benchmark classes need a group in benchmarks/QueryCache.Benchmarks/benchmark-groups.json.

License

QueryCache is licensed under the MIT License. See LICENSE.