EF second layer cache to enhance your SQL database performance based on Redis

Entity-Framework Core is a lightweight, extensible, open-source, and cross-platform version of the popular Entity Framework data access technology. It really helps the developer to build applications which access database easily. But in most cases, we may cache some results which do not change frequently, to reduce access to our database.

For example, the home page of a blog may not change frequently, but it is requested very frequently. Executing SQL every time there is an HTTP request costs a lot of database connections. Typically, we might write a memory cache like this:

    public class MyCacheService
    {
        private readonly IMemoryCache _cache;

        public AiurCache(IMemoryCache cache)
        {
            _cache = cache;
        }

        public async Task<T> GetAndCache<T>(string cacheKey, Func<Task<T>> backup, int cachedMinutes = 20)
        {
            if (!_cache.TryGetValue(cacheKey, out T resultValue) || resultValue == null)
            {
                resultValue = await backup();

                var cacheEntryOptions = new MemoryCacheEntryOptions()
                    .SetSlidingExpiration(TimeSpan.FromMinutes(cachedMinutes));

                _cache.Set(cacheKey, resultValue, cacheEntryOptions);
            }
            return resultValue;
        }
    }

And use it like this:

        public Task<Site> GetSiteByName(string siteName, bool allowCache)
        {
            if (allowCache)
            {
                return _aiurCache.GetAndCache($"site_object_{siteName}", () => GetSiteByName(siteName, allowCache: false));
            }
            else
            {
                return _dbContext.Sites.SingleOrDefaultAsync(t => t.SiteName == siteName);
            }
        }

Which supports the calling method cache the entity in memory instead of querying it every time from the database.

But doing so causes us lots of additional work. We need to manually handle the entity query event and clear the cache every time the entity is changed or deleted. Our code need to be changed greatly to apply cache.

So how can we cache every query, and clear the cache every time it is known to be updated, and without changing our code? I searched for solutions and there really exists.

First, install the EFCoreSecondLevelCacheInterceptor with the following command:

$ > dotnet add package EFCoreSecondLevelCacheInterceptor

Which install the NuGet package: https://www.nuget.org/packages/EFCoreSecondLevelCacheInterceptor/

And modify your StartUp class ConfigureServices method like this:

using EFCoreSecondLevelCacheInterceptor;

    public void ConfigureServices(IServiceCollection services)
    {
            services.AddDbContextPool<YourDbContext>((serviceProvider, optionsBuilder) =>
                optionsBuilder.UseSqlServer(_configuration.GetConnectionString("DatabaseConnection"))
                    .AddInterceptors(serviceProvider.GetRequiredService<SecondLevelCacheInterceptor>()));
            services.AddEFSecondLevelCache(options =>
            {
                options.UseMemoryCacheProvider().DisableLogging(true);
                options.CacheAllQueries(CacheExpirationMode.Sliding, TimeSpan.FromMinutes(30));
            });

            // Other code...
    }

This cache is updated when an entity is changed (insert, update, or delete) via a DbContext that uses this library. If the database is updated through some other means, such as a stored procedure or trigger, the cache becomes stale. And you don't have to change any other code.

Now build and run your apps. Normally it runs just like previous, but the performance got a great enhancement. The executed queries complete very fast that only happens in memory.

But, what if our app is running in multiple instances? Database change by other instances may not apply to other instances and may cause many issues. How can we keep our app available for scale?

Now we need the Redis to store our cache.

Redis is an open source (BSD licensed), in-memory data structure store, used as a database, cache and message broker. It supports data structures such as strings, hashes, lists, sets, sorted sets with range queries, bitmaps, hyperloglogs, geospatial indexes with radius queries and streams. Redis has built-in replication, Lua scripting, LRU eviction, transactions and different levels of on-disk persistence, and provides high availability via Redis Sentinel and automatic partitioning with Redis Cluster.

• Option 1: You can install Redis here: https://redis.io/
• Option 2: If you are running Windows Server, install Redis here: https://github.com/microsoftarchive/redis/releases
• Option 3: If you are using Azure, create a new Redis cache instance just with a few clicks:

And after installing and you have a redis instance, install the easy caching with command:

$ > dotnet add package EasyCaching.Redis

And modify your start up method like this:

        private const string _cacheProviderName = "redis";

        public void ConfigureServices(IServiceCollection services)
        {
            var useRedis = _configuration["easycaching:enabled"] == true.ToString();

            services.AddDbContextPool<YourDbContext>((serviceProvider, optionsBuilder) =>
                optionsBuilder.UseSqlServer(_configuration.GetConnectionString("DatabaseConnection"))
                    .AddInterceptors(serviceProvider.GetRequiredService<SecondLevelCacheInterceptor>()));
            services.AddEFSecondLevelCache(options =>
            {
                if (useRedis)
                {
                    options.UseEasyCachingCoreProvider(_cacheProviderName).DisableLogging(true);
                }
                else
                {
                    options.UseMemoryCacheProvider().DisableLogging(true);
                }
                options.CacheAllQueries(CacheExpirationMode.Sliding, TimeSpan.FromMinutes(30));
            });
            if (useRedis)
            {
                services.AddEasyCaching(option =>
                {
                    option.UseRedis(_configuration, _cacheProviderName);
                });
            }

            // Other code...
        }

It lets your app connects to the redis database, and use redis to store the cache for Entity-Framework Core instead of your in-app memory cache, which supports scaling out.

But your app still can't get the correct redis connection info. Add this to your appsettings.json file:

  "Easycaching": {
    "Enabled":  true, // Enable this to use Redis to replace MemoryCache.
    "Redis": {
      "MaxRdSecond": 120,
      "EnableLogging": false,
      "LockMs": 5000,
      "SleepMs": 300,
      "DbConfig": {
        "Password": "yourstrongpassword",
        "IsSsl": true,
        "SslHost": "kahlacachestaging.redis.cache.windows.net",
        "ConnectionTimeout": 5000,
        "AllowAdmin": true,
        "Endpoints": [{ "Host": "kahlacachestaging.redis.cache.windows.net", "Port": 6380 }],
        "Database": 0
      }
    }
  }

To get your keys if your redis is in Azure, click here:

And now your app will try to connect to the redis database during start up, and use redis to cache your database result.

Without touching your code, the performance of your ASP.NET Core app got great enhancement! Long live Redis!

References:

https://github.com/VahidN/EFCoreSecondLevelCacheInterceptor

https://github.com/dotnetcore/EasyCaching