Read MemoryCache Or InMemory Automatically for Lookup Tables in Database

Question

Instead of reading database lookup tables directly, team wants to apply (a) MemoryCache or (b) Entity Framework InMemoryDatabase/SQLite to reduce database usage and increase memory performance.

How to automatically forward existing Lookup Tables DbContext Linq queries to read from Memory? Lookup tables only change few times a year, are mostly static, AddressCategory, CustomerStatus, ProductType . The database is composed of Large Transaction tables which should be read from Database, and lookup tables to be read from Memory.

Note: Company has existing Linq queries, and want to auto forward existing code to Cache.

Method 1: EF InMemory / SQLite Proposed Solution:

1. Copy/Paste Original DbContext (StoreContext) into new DbContext copy called StoryMemoryContext. In Startup.cs

   services.AddDbContext<StoreContext>(options => options.UseSqlServer(@"Server=localhost;Database=StoreDatabase";));
   
   services.AddDbContext<StoreMemoryContext>(options => options.UseInMemoryDatabase(databaseName: "StoreMemoryContext"));
   
   services.AddDbContext<CustomDbContext>(options => options.UseSqlServer(@"Server=localhost;Database=StoreDatabase")));
   

2. Save Lookup Mini lookup tables into InMemoryDatabase 'StoryMemoryContext' (around 5 Mb max).

3. Have CustomDbContext inherit from original, where Original StoreContext get accessors forward to StoryMemoryContext.

Loop code all lookup Tables with T4 or Powershell automation

public class CustomDBContext : StoreContext
{
    public StoreMemoryContext _storeMemoryContext; = new StoreMemoryContext();
    public CustomDBContext()
    {
    }

    public override DbSet<ProductType> ProductType
    {
        set
        {
            base.ProductType = value;
            _storeMemoryContext.ProductType = value;
        }
        get
        {
            return _storeMemoryContext.ProductType;
        }
    }

Questions:

1) Would this Get method work? Any issues to research for? Feel free to revise/edit in answer. Open to any solution not only this,

Get accessors seems to be working.

• If executing _customDbContext.ProductType.ToList() , it reads InMemory EF.

• _customDbContext.CustomerTransaction.Include(c => c.ProductType).ToListAsync() will read database, matching intended behavior. Don't want to read large non-lookup table customer transaction from InMemory Database.

2) Set Accessors only partially works, since many ways exist to modify DBSet and DBContext: Add, AddRange, Remove, Add Entity Graph (is challenging to track) Physical lookup tables change only couple times a year. Thought about ChangeTracker, however to place If statement after every SaveChanges() to validate if lookup table changed/then update InMemory database, may slow application, as we have 500 transactions/per second. (this is what I was told? willing to hear more opinions about ChangeTracker and effect on performance/speed) Note: Physical and InMemory Lookup tables, can have 30 min sync period between two, per customer requirement, as lookup tables rarely change. However near instantaneous data sync between two is goal solution for question(< 5 sec).

Method 2: MemoryCache Does not Seem to Work: Similar create Inherited/custom DbContext where read overrides forward lookup tables to MemoryCache. Storing DBContext in MemoryCache only stores IQueryable, not actual Materialized values. Going from controller to controller refreshes DbContext, and clears out cache, feel free to revise/edit in answer.

public class CustomDBContext : EcommerceContext
{
    public CustomDBContext(IMemoryCache memoryCache)
    {
        _memoryCache = memoryCache;
    }

    public override DbSet<ProductType> ProductType
    {
        get
        {
            return _memoryCache.Get<DbSet<ProductType>>("ProductTypeMemoryCache");
        }
    }

Update:

Answer from David Browne will not work for question requirements, as has Usage like

var pt2 = db.Cache.LK_ProductTypes; //from cache

Company has many existing queries, and want to forward existing code to Cache. Additionally, programmers may forget to apply Cache, so code below should automatically forward

var pt1 = db.LK_ProductTypes.ToList() ;  

We have mix of large transactional tables which should not be cached and small lookup tables.

DBA does not allow use SQL Server memory-optimized tables due to some limitations. Memory Optimized restrictions

Resources:

Does DbContext need MemoryCache or Redis in Net Core?

How to make Entity Framework Data Context Readonly

Net Core: Automatically Update Lookup Table Cache after Entity Framework Save and UnitOfWorkPattern

Using EF Core 2.2,

Answer 1

Currently thinking of creating own dbcontext where read overrides can forward LK_ tables to MemoryCache. Would this work?

No. That would break the DbContext, and prevent you from writing queries that joins the lookup items with non-cached items. So you'll need a seperate cache. You can try a pattern like this:

public class Db : DbContext
{
    private IMemoryCache _memoryCache;
    public Db(IMemoryCache memoryCache)
    {
        _memoryCache = memoryCache;
    }

    public class EntityCache
    {
        private Db db;
        public EntityCache(Db db)
        {
            this.db = db;
        }

        public IList<LK_ProductType> LK_ProductTypes => db._memoryCache.GetOrCreate<IList<LK_ProductType>>("LK_ProductTypeMemoryCache",f => db.LK_ProductTypes.AsNoTracking().ToList());
    }
    public EntityCache Cache => new EntityCache(this);

    public DbSet<LK_ProductType> LK_ProductTypes { get; set; }

}

Usage would be like

using var db = new Db(memoryCache);
var pt1 = db.LK_ProductTypes.ToList();  //from database
var pt2 = db.Cache.LK_ProductTypes; //from cache

EF Core 3 introduces a new query interception framework, which should make it possible to introduce a query results cache in your DbContext. Here's a really rough example of how this would work.

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Threading;

namespace EfCore3Test
{

    class Customer
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    class Db : DbContext
    {

        string constr;
        private static QueryResultsCache cache { get; } =  new QueryResultsCache();
        private QueryResultsCache Cache { get; } = cache;

        public IList<T> CacheQueryResults<T>(IQueryable<T> query)
        {
            return Cache.ReadThrough(query);
        }
        public Db() : this("server=.;database=EfCore3Test;Integrated Security=true")
        { }

        public Db(string constr)
        {
            this.constr = constr;
        }
        static readonly ILoggerFactory loggerFactory = LoggerFactory.Create(builder => 
               {
                   builder.AddFilter((category, level) =>
                      category == DbLoggerCategory.Database.Command.Name
                      && level == LogLevel.Information).AddConsole();
               }
               );
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(constr, a => a.UseRelationalNulls(true));
            optionsBuilder.AddInterceptors(cache);

            optionsBuilder.UseLoggerFactory(loggerFactory);
            base.OnConfiguring(optionsBuilder);
        }
        public DbSet<Customer> Customers { get; set; }
    }

    public class QueryResultsCache : DbCommandInterceptor
    {
        class CacheEntry
        {
            public CacheEntry(DataTable dt)
            {
                this.Data = dt;
                this.LastRefresh = DateTime.Now;
            }
            public DateTime LastRefresh { get; set; }
            public DataTable Data { get; set; }
        }

        private ConcurrentDictionary<string, CacheEntry> resultCache = new ConcurrentDictionary<string, CacheEntry>();

        AsyncLocal<bool> cacheEntry = new AsyncLocal<bool>();
        public IList<T> ReadThrough<T>(IQueryable<T> query)
        {
            cacheEntry.Value = true;
            var results = query.ToList();
            cacheEntry.Value = false;
            return results;
        }
        public override InterceptionResult<DbDataReader> ReaderExecuting(
          DbCommand command,
          CommandEventData eventData,
          InterceptionResult<DbDataReader> result)
        {
            if (resultCache.ContainsKey(command.CommandText))
            {
                Console.WriteLine("Query Result from Cache");
                return InterceptionResult<DbDataReader>.SuppressWithResult(resultCache[command.CommandText].Data.CreateDataReader());
            }

            if (cacheEntry.Value)
            {
                using (var rdr = command.ExecuteReader())
                {
                    var dt = new DataTable();
                    dt.Load(rdr);
                    resultCache.AddOrUpdate(command.CommandText, s => new CacheEntry(dt), (s, d) => d);
                    Console.WriteLine("Cached Result Created");
                    return InterceptionResult<DbDataReader>.SuppressWithResult(dt.CreateDataReader());
                }
            }

            return result;
        }
    }
    class Program
    {

        static void Main(string[] args)
        {
            using var db = new Db();
            db.Database.EnsureCreated();

            var c = db.CacheQueryResults( db.Customers );

            for (int i = 0; i < 1000; i++)
            {
                var c2 = db.Customers.ToList();
            }


            Console.WriteLine(c);


        }
    }
}

Answer 2

You already have a working Data Access Layer (DAL) which does a DB lookup for you. Leave it untouched.

For caching, create another abstraction over the current DAL. Let's call this Cache Access Layer (CAL). This CAL is what your application should be using now, rather than the DAL.

The CAL would now be responsible for:

• Replying to Data Requests;
• Sending data from cache when there's a hit;
• Populating the data in cache from the DB, when there's a miss;
• Writing the data to DB when it is updated in the cache.

---

Your application architecture would look like this before and after your changes:

---

Here is how it is usually done in Cloud. The use case may be different but the principles are the same, viz:

• Deciding when to cache;
• Determining how to cache effectively;
• Caching highly dynamic data;
• Managing data expiration in cache.