In Memory Caching

Decision tree:

• Redis available + user wants it → Use Hybrid (two-level) caching (fastest)
• Redis not available OR user doesn't want it → Use In-memory only caching
• Large dataset (>1GB) → Use Partial cache pattern

Database Support

This skill supports PostgreSQL and MSSQL. Use the appropriate trigger generation script:

• PostgreSQL: scripts/generate_postgresql_trigger.py
• MSSQL: scripts/generate_mssql_trigger.py

Implementation Workflow

Step 1: Generate Database Triggers

For each entity that needs caching, generate database triggers:

PostgreSQL:

python scripts/generate_postgresql_trigger.py Users public > users_trigger.sql

MSSQL:

python scripts/generate_mssql_trigger.py Users dbo > users_trigger.sql

Execute the generated SQL in the database.

Step 2: Create TableRecordChanges Entity

Add to your DbContext:

public class TableRecordChange
{
    public int Id { get; set; }
    public string TableName { get; set; } = string.Empty;
    public int RecordId { get; set; }
    public string ChangeType { get; set; } = string.Empty;
    public DateTime ChangedAt { get; set; }
}

public DbSet<TableRecordChange> TableRecordChanges { get; set; }

Step 3: Implement Functions Class with Caching

Choose the appropriate pattern from references/implementation-patterns.md:

• Pattern 1: Basic entity cache (single-record lookups)
• Pattern 2: Entity with secondary index (ID + GUID lookups)
• Pattern 3: Collection cache (one-to-many relationships)
• Pattern 4: Partial cache (large datasets)
• Pattern 5: Hybrid Redis + In-memory (maximum performance)

Key methods every Functions class must have:

• InitializeCacheAsync() - Load initial data
• Refresh*CacheAsync(int id) - Update cache on change
• Get*ById() - Cache-first read

Step 4: Implement CacheManagementService

Create a background service that:

• Initializes all caches on startup
• Polls TableRecordChanges every 5 seconds
• Routes changes to appropriate Refresh*CacheAsync() methods

See complete implementation in references/implementation-patterns.md.

Step 5: Register Services

In Program.cs:

// Optional: Redis for hybrid caching
builder.Services.AddStackExchangeRedisCache(options =>
{
    options.Configuration = builder.Configuration.GetConnectionString("Redis");
    options.InstanceName = "AppCache:";
});

// Register Functions classes
builder.Services.AddScoped<UserFunctions>();
builder.Services.AddScoped<ProductFunctions>();

// Register cache management service
builder.Services.AddHostedService<CacheManagementService>();

Step 6: Update CacheManagementService Routing

Add cases for each cached entity in ProcessSingleChangeAsync():

switch (change.TableName)
{
    case "Users":
        await scope.ServiceProvider
            .GetRequiredService<UserFunctions>()
            .RefreshUserCacheAsync(change.RecordId);
        break;
    // Add more cases...
}

Cache Pattern Selection

Refer to references/implementation-patterns.md for detailed implementations of:

1. Basic Entity Cache - Simple entities, single lookups
2. Entity with Secondary Index - Multiple lookup keys (ID, GUID)
3. Collection Cache - One-to-many relationships
4. Partial Cache - Large datasets with size limits
5. Hybrid Redis + In-Memory - Two-level cache for maximum performance

Hybrid Caching (Redis + In-Memory)

When Redis is available and the user wants maximum performance:

Benefits:

• L1 (in-memory): <1μs access time, 95%+ hit rate
• L2 (Redis): 1-5ms access time, distributed consistency
• L3 (Database): 10-100ms, only on cache miss

Implementation:

1. Check L1 (in-memory) first
2. If miss, check L2 (Redis)
3. If miss, query database
4. Populate both L1 and L2 on database hit
5. Trigger invalidation updates both levels

See Pattern 5 in references/implementation-patterns.md for complete code.

Performance Characteristics

Cache Hit (L1):        < 1 microsecond
Cache Hit (L2 Redis):  1-5 milliseconds  
Cache Miss:            10-100 milliseconds

Expected improvement: 100-5000x faster than direct database queries

Memory Estimation

Calculate memory requirements:

Entity count × Average size + Dictionary overhead
Example: 100,000 users × 2KB + 100,000 × 48B ≈ 200-210 MB

Best Practices

DO:

• Always implement InitializeCacheAsync() for each Functions class
• Use consistent Refresh*CacheAsync() pattern
• Log all cache operations for debugging
• Handle exceptions during refresh
• Monitor memory usage

DON'T:

• Don't cache mutable objects without defensive copying
• Don't ignore cache invalidation
• Don't cache everything - be selective
• Don't assume cache is always accurate for critical data

Troubleshooting

Example: Complete Implementation

For a complete working example of a Functions class with all patterns, see references/implementation-patterns.md.

Scripts

• scripts/generate_postgresql_trigger.py - Generate PostgreSQL triggers
• scripts/generate_mssql_trigger.py - Generate MSSQL triggers37:["$","$L3f",null,{"content":"$40","frontMatter":{"name":"in-memory-caching","description":"Implement high-performance in-memory caching with trigger-based cache invalidation for C#/.NET applications. Use when creating new entities that need caching, optimizing database queries, implementing cache invalidation strategies, or setting up cache management for ASP.NET Core projects. Supports PostgreSQL and MSSQL databases. Can use Redis for hybrid (two-level) caching if available in the project, or pure in-memory caching if Redis is not present or desired."}}]