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.
This skill provides a production-ready in-memory caching architecture for C#/.NET applications that combines thread-safe concurrent collections, database-driven cache invalidation, and background service management.
The caching system consists of four layers:
ConcurrentDictionary for lock-free in-memory cachingTableRecordChangesCacheManagementService monitoring and refreshing cachesCRITICAL: Always check if Redis is available in the project:
IDistributedCache in dependency injectionappsettings.jsonAddStackExchangeRedisCache in Program.csDecision tree:
This skill supports PostgreSQL and MSSQL. Use the appropriate trigger generation script:
scripts/generate_postgresql_trigger.pyscripts/generate_mssql_trigger.pyFor 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.
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; }
Choose the appropriate pattern from references/implementation-patterns.md:
Key methods every Functions class must have:
InitializeCacheAsync() - Load initial dataRefresh*CacheAsync(int id) - Update cache on changeGet*ById() - Cache-first readCreate a background service that:
TableRecordChanges every 5 secondsRefresh*CacheAsync() methodsSee complete implementation in references/implementation-patterns.md.
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>();
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...
}
Refer to references/implementation-patterns.md for detailed implementations of:
When Redis is available and the user wants maximum performance:
Benefits:
Implementation:
See Pattern 5 in references/implementation-patterns.md for complete code.
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
Calculate memory requirements:
Entity count × Average size + Dictionary overhead
Example: 100,000 users × 2KB + 100,000 × 48B ≈ 200-210 MB
DO:
InitializeCacheAsync() for each Functions classRefresh*CacheAsync() patternDON'T:
| Issue | Solution |
|---|---|
| Stale data | Verify triggers exist, check TableRecordChanges |
| Memory leak | Ensure TryRemove() called for deletions |
| High latency | Check initialization completed, verify triggers |
| Out of memory | Implement size limits (Pattern 4) |
For a complete working example of a Functions class with all patterns, see references/implementation-patterns.md.
scripts/generate_postgresql_trigger.py - Generate PostgreSQL triggersscripts/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."}}]