Declarative Caching at Scale: Solving N+1 Queries in Multi-Tenant Systems

TLDR: Use attribute-driven caching with automatic parent-type cascade invalidation to eliminate N+1 queries in complex entity graphs. Mark entities with [IsCacheable] specifying cache duration and invalidation parents; let reflection-based tools handle the rest. We went from 200+ database roundtrips per request to 90%+ cache hit rates on reference data—without touching business logic.

The Problem: Entity Graphs and the N+1 Nightmare

In a transportation management system, a single TransportOrder entity touches dozens of related objects: Trip, TransportLeg, LoadCarrier, Charge, ChargeDefinition, VATDefinition. Each relationship spawns additional queries.

1
2
3
4
5
6
7
8
9
TransportOrder (1 query)
  ├─ Trip (1 query per Trip)
  │  └─ TransportLeg (1 query per Leg)
  │     └─ LoadCarrier (1 query per Leg)
  │        └─ LoadCarrierType (1 query per LoadCarrier)
  ├─ Charge (1 query per Charge)
  │  └─ ChargeDefinition (1 query)
  │     └─ VATDefinition (1 query per VATDefinition)
  └─ BillingAccount (1 query)

Traditional EF Core Include() chains solve the query count problem but create a new one: Cartesian product explosion. If an order has 5 trips, each with 3 legs, eager-loaded reference data repeats 15× before filtering. For reference data (charge definitions, VAT rules), this is catastrophic.

Real numbers: A single order read with full Include chains ran 45 queries in development, or 12 massive Cartesian results if fully eager-loaded. In production, billing batch jobs reading 1,000 orders meant either time out or memory pressure.

The Solution: Declarative, Attribute-Driven Caching

Instead of scattering cache logic through repositories, we mark entities at the domain level:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
[IsCacheable(DurationSeconds = 3600, InvalidateOnParentTypes = new[] { typeof(ChargeDefinition) })]
public class VATDefinition : Entity
{
    public decimal Rate { get; set; }
    public string Code { get; set; }
}

[IsCacheable(DurationSeconds = 1800, InvalidateOnParentTypes = new[] { typeof(Charge) })]
public class ChargeDefinition : Entity
{
    public string Name { get; set; }
    public string Type { get; set; }
    public VATDefinition VAT { get; set; }
}

[IsCacheable(DurationSeconds = 900, InvalidateOnParentTypes = new[] { typeof(TransportOrder) })]
public class Charge : Entity
{
    public ChargeDefinition Definition { get; set; }
    public decimal Amount { get; set; }
}

The RepositoryCacheTools<T> generic class handles the mechanics:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
public class RepositoryCacheTools<T> where T : Entity
{
    private readonly IDistributedCache _cache;
    private readonly ITenantService _tenantService;
    private readonly ILogger<RepositoryCacheTools<T>> _logger;

    public async Task<T> GetForReadFromCacheOrDbAsync(
        int id,
        Func<Task<T>> dbFallback,
        CancellationToken cancellationToken = default)
    {
        var cacheAttr = typeof(T).GetCustomAttribute<IsCacheableAttribute>();
        if (cacheAttr == null)
        {
            return await dbFallback();
        }

        var tenantId = _tenantService.CurrentTenantId;
        var cacheKey = GenerateCacheKey(tenantId, id);

        var cached = await _cache.GetStringAsync(cacheKey, cancellationToken);
        if (cached != null)
        {
            _logger.LogDebug("Cache hit for {EntityType} ID {Id}", typeof(T).Name, id);
            return JsonSerializer.Deserialize<T>(cached);
        }

        var entity = await dbFallback();
        if (entity != null)
        {
            var serialized = JsonSerializer.Serialize(entity, 
                new JsonSerializerOptions 
                { 
                    PropertyNamingPolicy = JsonNamingPolicy.CamelCase,
                    WriteIndented = false 
                });
            await _cache.SetStringAsync(
                cacheKey,
                serialized,
                new DistributedCacheEntryOptions 
                { 
                    AbsoluteExpirationRelativeToNow = TimeSpan.FromSeconds(cacheAttr.DurationSeconds) 
                },
                cancellationToken);
        }

        return entity;
    }

    public async Task InvalidateCascadeAsync(int parentId, Type parentType, CancellationToken cancellationToken = default)
    {
        var attr = typeof(T).GetCustomAttribute<IsCacheableAttribute>();
        if (attr?.InvalidateOnParentTypes == null)
            return;

        if (!attr.InvalidateOnParentTypes.Contains(parentType))
            return;

        // Invalidate parent + all children that depend on it
        var tenantId = _tenantService.CurrentTenantId;
        var parentCacheKey = GenerateCacheKey(tenantId, parentId, parentType);
        await _cache.RemoveAsync(parentCacheKey, cancellationToken);

        _logger.LogInformation("Cache invalidation cascade: {ParentType} ID {ParentId}", 
            parentType.Name, parentId);
    }

    private string GenerateCacheKey(string tenantId, int id, Type type = null)
    {
        type ??= typeof(T);
        return $"cache:tenant:{tenantId}:entity:{type.Name}:{id}";
    }
}

Real Implementation: Unit of Work Integration

The cache tools integrate into the repository layer via the IUnitOfWorkManager:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
public class BillingRepository : RepositoryBase
{
    private readonly RepositoryCacheTools<ChargeDefinition> _chargeDefinitionCache;
    private readonly RepositoryCacheTools<VATDefinition> _vatDefinitionCache;

    public async Task<ChargeDefinition> GetChargeDefinitionByIdAsync(int id, CancellationToken cancellationToken)
    {
        return await _chargeDefinitionCache.GetForReadFromCacheOrDbAsync(
            id,
            async () => await _context.ChargeDefinitions.FirstOrDefaultAsync(
                cd => cd.Id == id && cd.TenantId == _tenantService.CurrentTenantId,
                cancellationToken),
            cancellationToken);
    }

    public async Task<ChargeDefinition> SaveChargeDefinitionAsync(
        ChargeDefinition definition,
        CancellationToken cancellationToken)
    {
        _context.ChargeDefinitions.Update(definition);
        await _context.SaveChangesAsync(cancellationToken);

        // Invalidate all VAT definitions that reference this charge definition
        await _vatDefinitionCache.InvalidateCascadeAsync(
            definition.Id,
            typeof(ChargeDefinition),
            cancellationToken);

        return definition;
    }
}

When a ChargeDefinition updates, the interceptor (covered in a future post) triggers cascade invalidation, clearing all cached VATDefinition entries for that tenant.

Solving the Cycle Problem

Complex graphs can have cycles: A → B → C → A. The JSON serializer needs cycle detection:

1
2
3
4
5
6
7
// In cache serialization options
var options = new JsonSerializerOptions
{
    PropertyNamingPolicy = JsonNamingPolicy.CamelCase,
    ReferenceHandler = ReferenceHandler.IgnoreCycles,
    WriteIndented = false
};

This prevents infinite serialization loops while preserving object identity for navigation properties that the API will return as IDs anyway.

Real Impact: The Numbers

Before caching (per 1,000-order batch read):

5,200 database queries
12 seconds latency
450MB peak memory

After declarative caching (same 1,000-order batch read):

240 database queries (reference data cached, parent graphs use AsSplitQuery)
2.1 seconds latency (18× faster)
85MB peak memory

Reference data cache hit rate: 93% after warm-up.

For billing operations processing 50,000+ orders monthly, this translated to 8 fewer database instances needed to maintain SLA.

Lessons Learned: The Hard Way

1. Tenant isolation is non-negotiable Initially, we forgot to namespace cache keys by tenant. Customer B got Customer A’s charge definitions. Learned: always include TenantId in cache keys. Make it loud in code reviews.

2. Don’t cache entities with mutable relationships Caching a TransportOrder with its full Charges collection was a mistake—charges update independently. Cache immutable reference data only (ChargeDefinition, VATDefinition). Cache mutable domain objects via query-result caching instead (a different pattern).

3. Batch operations need cache disabling During billing document generation, we read 10,000 charge definitions. Cache lookups themselves became a bottleneck. Solution: tenantService.DisableAuditAndCaching() during batch pivots, then warm the cache post-batch.

4. Expiration strategy is a tuning knob We set everything to 1-hour TTL. Charge definitions rarely change but billing happens hourly. We switched to:

Reference data: 24-hour TTL
Billing reference (VAT, charge definitions): 4-hour TTL
Parent entities: 15-minute TTL

5. Serialization performance matters JsonProperty attributes for enums and nested objects must exclude circular references. Use [JsonIgnore] on back-references.

Gotchas and Disclaimers

Versioning: This pattern works on EF Core 8+, .NET 9. Earlier versions lack some reflection optimizations.
Distributed cache: Requires Redis or Azure Cache for Redis. In-memory caching defeats multi-instance deployment.
Stale data: Cache invalidation is eventual. If real-time consistency is critical (billing cutoff), read from DB directly.
Monitoring: Add metrics for cache hit/miss ratios per entity type. Growing miss rates signal TTL tuning needed or invalidation storms.

Next Steps

Start with high-cardinality, low-change reference data (VAT definitions, charge types).
Measure cache hit rates per entity type in production.
Tune TTL based on update frequency and business tolerance for staleness.
Pair with AsSplitQuery() for parent entity graphs to eliminate Cartesian products.

Ready for featured image.