Cache your SQL queries with Redis in C#

Time to read: 4 minutes

February 13, 2023
Written by
Bryan Hogan
Contributor
Opinions expressed by Twilio contributors are their own
Reviewed by
Niels Swimberghe
Twilion

Cache your SQL queries with Redis in C#

As fast and optimized as your database is, there are times when a cache will boost your performance, or you may want to reduce the load on your database. In this post, you will use Redis in combination with a traditional Microsoft SQL (MSSQL) Server. When looking up data, you will first query Redis, if the data is there, you're done. If it is not there, you will then query the database.

You will run Redis and MSSQL Server in Docker containers. The application querying the data will be a .NET 7 (but you can use .NET 6 if that is your preference) Web API application.

Prerequisites

You will need the following things in this tutorial:

Create a Web API application that uses Redis and SQL Server

You are going to create a Web API application that seeds the SQL Server with some key-values on startup. The application will use an API endpoint to look up these key-values. The API endpoint will first look up the key in Redis, if it is not there, it will then look it up in SQL Server.

If the value is found in SQL Server, it will be added to Redis for future lookups.

Create the new Web API application using:

dotnet new web -n RedisAndEntityFrameworkWebApi --no-https
cd RedisAndEntityFrameworkWebApi

HTTPS and OpenAPI are turned off to keep the Program.cs file smaller for this tutorial.

Add these two NuGet packages that are required for this application:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.Extensions.Caching.StackExchangeRedis

Create a new directory in your project named Data. You will add three files to that directory.

KeyAndValue.cs, a simple pair of strings that represents a key-value pair.

namespace RedisAndEntityFrameworkInWebApi.Data;

public class KeyAndValue
{
        public string Key { get; set; }
        public string Value { get; set; }
}

KeyAndValueContext.cs, a DbContext for working with the database.

using Microsoft.EntityFrameworkCore;

namespace RedisAndEntityFrameworkInWebApi.Data;

public class KeyAndValueContext : DbContext
{
    public KeyAndValueContext(DbContextOptions<KeyAndValueContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<KeyAndValue>()
            .HasKey(k => k.Key);
    }

    public DbSet<KeyAndValue>? KeyAndValues { get; set; }
}

Seeder.cs, a class that seeds the database with simple key-value pairs. The keys are letters of the alphabet, and the values are the position of the letter in the alphabet.

namespace RedisAndEntityFrameworkInWebApi.Data;

public static class Seeder
{
    public static async Task SeedAsync(this KeyAndValueContext keyAndValueContext)
    {
        if (!keyAndValueContext.KeyAndValues.Any())
        {
            int i = 1;
            foreach (string letter in Alphabet)
            {
                keyAndValueContext.Add(new KeyAndValue 
                { 
                    Key = letter, 
                    Value = $"The letter \"{letter}\" is at position {i++} in the alphabet" 
                });
            }

            await keyAndValueContext.SaveChangesAsync();
        }
    }

    public static IEnumerable<string> Alphabet
    {
        get
        {
            for (char letter = 'a'; letter <= 'z'; letter++)
            {
                yield return letter.ToString();
            }
        }
    }
}

Seeding your database with dummy data can be helpful for development and demos. Instead of manually entering data before you can test your app, the app can seed the data beforehand, saving you and testers time. However, be cautious when using this technique in production to not delete or pollute production data.

Two connection strings are needed for this application, one for the database, and one for Redis.

Open the appsettings.Development.json file and add the two connection strings:

{
  ...,
  "ConnectionStrings": {
        "MyDatabase": "Server=localhost;Database=KeyValueDb;User Id=SA;Password=A!VeryComplex123Password;MultipleActiveResultSets=true;TrustServerCertificate=true",
        "Redis": "localhost:6379"
  }
}

Note the inclusion of TrustServerCertificate=True, this was not needed in earlier versions of the Docker SQL Server Image. But now, you will get a connection error if it is absent: System.Security.Authentication.AuthenticationException : The remote certificate was rejected by the provided RemoteCertificateValidationCallback.

Open the Program.cs file. At the top of the file, add three using statements:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Caching.Distributed;
using RedisAndEntityFrameworkInWebApi.Data;

Below the line var builder = WebApplication.CreateBuilder(args);, add Redis and SQL Server to the service collection:

builder.Services.AddStackExchangeRedisCache(options =>
{
   options.Configuration = builder.Configuration.GetConnectionString("Redis");
});

builder.Services.AddDbContext<KeyAndValueContext>(options =>
   options.UseSqlServer(builder.Configuration.GetConnectionString("MyDatabase")));

There is a little more configuration to do. Below the line var app = builder.Build(); add the following:

using(var scope = app.Services.CreateScope())
{
   var keyAndValueContext = scope.ServiceProvider.GetRequiredService<KeyAndValueContext>();
   await keyAndValueContext.Database.EnsureDeletedAsync();
   await keyAndValueContext.Database.EnsureCreatedAsync();
   await keyAndValueContext.SeedAsync();
}

This code gets an instance of the KeyAndValueContext from the service provider, deletes and recreates the database. It then seeds the database with the key-value pairs.

Now all that is left is to create an endpoint that will return the value for a given key, first checking the Redis cache, then if necessary the database.

Add the following code below the previous code:

var slidingExpiration = new DistributedCacheEntryOptions
{
    SlidingExpiration = TimeSpan.FromSeconds(5)
};

app.MapGet("/{key}", async (
    string key,
    IDistributedCache distributedCache,
    KeyAndValueContext keyAndValueContext
) =>
{
    string? value = distributedCache.GetString(key);
    if (value is not null)
    {
        return $"Key:{key}, Value:{value}. Source:Redis";
    }

    var keyAndValue = await keyAndValueContext.FindAsync<KeyAndValue>(key);

    if (keyAndValue is null)
    {
        return $"{key} not found";
    }

    await distributedCache.SetStringAsync(key, keyAndValue.Value, slidingExpiration);
    return $"Key:{key}, Value:{keyAndValue.Value}. Source:MSSQL";
});

The first line sets up a sliding expiration of 5 seconds for the cache. This means that if the value is not accessed for 5 seconds, it will be removed from the cache.

The endpoint takes a letter as a parameter from the URL, and the IDistributedCache (Redis) and KeyAndValueContext (SQL Server) from the dependency injection container.

It then checks the cache for the letter you are looking for, if found, it returns the value.

If it is not found, it checks the database for the letter, and if found, it adds the key-value pair to the cache and returns the value.

If the letter is not found in the database, it returns a message saying so.

Those are all the code changes needed.

Running SQL Server and Redis in Docker Containers

You will use a docker-compose.yml file to run both SQL Server and Redis in Docker containers, and expose their default ports to your local machine.

Before you start, make sure Docker is running. You can do this by running the following command in a terminal:

docker info

If it is not running, you will see an error like this:

Server:
ERROR: error during connect: This error may indicate that the docker daemon is not running.: Get "http://%2F%2F.%2Fpipe%2Fdocker_engine/v1.24/info": open //./pipe/docker_engine: The system cannot find the file specified.
errors pretty printing info

The important part here is - "This error may indicate that the docker daemon is not running". Follow the instructions for starting Docker on your operating system.

To run both Redis and SQL Server in Docker containers, you will use Docker Compose.

To do this, you will create a docker-compose.yml file. This is a YAML file that defines the services you want to run in Docker, it also allows you to make those services available to your local machine.

Create a new file called docker-compose.yml in the root of your .NET project. Add the following content to the file:

version: "3.9"
   
services:
  redis:
    image: "redis"
    ports:
        - "6379:6379"

  mssql:
    image: "mcr.microsoft.com/mssql/server"
    ports:
      - "1433:1433"
    environment:
      - ACCEPT_EULA=y
      - SA_PASSWORD=A!VeryComplex123Password

Now you are ready to start the containers, and when they are running, you will start your application.

Starting the containers and running the application

Open a terminal in the root of the project directory, run the following command to start the containers:

docker-compose up

Start your application from your terminal and open the localhost URL in your browser:

dotnet run

Alternatively, start your application from your IDE, this should open your browser to localhost and port of your application.

To invoke the endpoint, add the following to the URL in the browser /a. You will see the following output:"Key:a, Value:The letter "a" is at position 1 in the alphabet. Source:MSSQL".

If you quickly refresh the page, you will see the following output: "Key:a, Value:The letter "a" is at position 1 in the alphabet. Source:Redis".

Two browsers at localhost/a saying key "a" is at position 1 in the alphabet. Browser 1 says source is MSSQL and the other Redis.

Conclusion

In this post, you have seen how to use Redis and SQL Server together to improve the response times of your requests and to reduce the workload on your database. The demo shows how to do this with a simple query and key value pairs, but the same concept can be applied to complex queries with lots of data.

Bryan Hogan is a blogger, podcaster, Microsoft MVP, and Pluralsight author. He has been working on .NET for almost 20 years. You can reach him on Twitter @bryanjhogan.

Related Posts

Related Resources

A newspaper article

Twilio Docs

From APIs to SDKs to sample apps

API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.
An Open book

Resource Center

The latest ebooks, industry reports, and webinars

Learn from customer engagement experts to improve your own communication.
User group reactions

Ahoy

Twilio's developer community hub

Best practices, code samples, and inspiration to build communications and digital engagement experiences.