Caching a database can be a chore but in this Write Stuff article, Mariusz Bojkowski shows how easy it can be to add a Redis cache to your PostgreSQL database if you are using Entity Framework 6.

Database caching is a commonly used technique to improve scalability. By offloading database work to other, faster stores it can also help improve the availability of the data too. Often, though, that caching comes at the cost of hardwired code in the application to check the cache first before the database. But what if we could do it cheaply and transparently to the application? Let's try to leverage C# and the features of Entity Framework 6 to do all the heavy lifting. I’ll show how to use PostgreSQL database with the framework and how to add transparent caching using Redis database.

In this tutorial, I’ll create simple Books table and a console application that will get the data from the table. Next, I’ll upgrade the application to use caching. I’ll be using Visual Studio 2017. The full application source is available on GitHub.

Preparing the PostgreSQL database

First, you need to create PostgreSQL database using the tools or provider of your choice. Next, let’s create a sample database table of books. Connect to the PostgreSQL database and execute the following create statement.

    CREATE TABLE "Books" (
      "Id"      SERIAL       NOT NULL,
      "Title"   VARCHAR(50)  NOT NULL,
      "Author"  VARCHAR(50)  NOT NULL,
      PRIMARY KEY ("Id")
    );

Please remember that all identifiers (table names, column names) are folded to lower case in a PostgreSQL database. To change it, make sure you use double quotation marks in the table name and column names. This is required as it will simplify Book entity mapping to properties of the C# model class.

Create Entity Framework application

Once the database table is ready, create a new console application. Open Visual Studio and click File menu, then New –> Project. From the dialog box, choose Installed –> Templates –> Visual C# –> Windows Classic Desktop. Chose Console App (.NET Framework), then provide a name (I typed RedisCacheForPostgre) and location.

Next, let’s add PostgreSQL Entity Framework provider – add the latest version of Npgsql.EntityFramework NuGet package.

It will also install Entity Framework 6 NuGet package as it’s one of the dependencies.

Please note that at the moment of writing this article the latest version of the Npgsql provider (2.2.7) references Entity Framework version 6.0.0 (not the latest) and the version 6.0.0 will be installed. We will upgrade few paragraphs below.

Configure Entity Framework

Add PostgreSQL connection string

Open App.config file and add connectionStrings section as in the example below. Please keep configSections as the first child element of configuration node – it’s a strict .NET requirement. Otherwise, the application will crash at runtime.

    <configuration>
      <configSections>
        (...)
      </configSections>
      <connectionStrings>
        <add name="PostgreSQL" connectionString="host=hostname;port=5432;database=databaseName;user id=userName;password=secret" providerName="Npgsql" />
      </connectionStrings>
      (...)
    </configuration>

Please note that there is providerName attribute in the connection string definition pointing to the PostgreSQL provider (Npgsql).

Define books entity

Add a new folder to the project and give it ‘Entities’ name.

Next, add Book class to the folder. It will reflect books entities from the database.

    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;

    namespace RedisCacheForPostgre.Entities
    {
        [Table("Books", Schema = "public")]
        public class Book
        {
            [Key]
            public int Id { get; set; }
            public string Title { get; set; }
            public string Author { get; set; }
        }
    }

There is a Table attribute added to the class that defines the database table name. Note the schema parameter – by default Entity Framework uses dbo schema. PostgreSQL uses public schema on the other hand.

Also, the Id property is decorated with Key attribute to instruct Entity Framework that its primary key column.

Define the database context

Add PostgreContext class to the Entities folder.

The class should inherit from System.Data.Entity.DbContext. It will be the main interface for accessing the database.

    using System.Data.Entity;

    namespace RedisCacheForPostgre.Entities
    {
        public class PostgreContext : DbContext
        {
            public PostgreContext() : base(nameOrConnectionString: "PostgreSQL") { }
            public DbSet<Book> Book { get; set; }
        }
    }

There is a connection string name passed to the base class constructor. The Book property will be responsible for operations on the books table.

Add sample data to PostgreSQL

It’s time for doing something real. Open the Program.cs file and add InsertSampleData method.

    using RedisCacheForPostgre.Entities;

    namespace RedisCacheForPostgre
    {
        public class Program
        {
            public static void Main(string[] args)
            {
                InsertSampleData();
            }

            private static void InsertSampleData()
            {
                using (var context = new PostgreContext())
                {
                    context.Book.Add(new Book { Title = "Witcher", Author = "Andrzej Sapkowski" });
                    context.Book.Add(new Book { Title = "A Game of Thrones", Author = "George R.R. Martin" });
                    context.Book.Add(new Book { Title = "Inclusion", Author = "Andrzej W. Sawicki" });
                    context.SaveChanges();
                }
            }
        }
    }

Let’s focus a little bit on the method. First, a new PostgreContext object is created. Then, few new Book objects are created and added to the Book property (of type DbSet<Book>). This way Entity Framework will mark them as new rows. Finally, the SaveChanges method adds the new rows to the database.

You can query the database to confirm that the rows have been added.

Query PostgreSQL database

We have the sample data in the database, so let’s query it in the application. Add a PrintBooks method to Program class.

    using RedisCacheForPostgre.Entities;
    using System;
    using System.Linq;

    namespace RedisCacheForPostgre
    {
        public class Program
        {
            public static void Main(string[] args)
            {
                //InsertSampleData();
                PrintBooks();
            }

            private static void PrintBooks()
            {
                using (var context = new PostgreContext())
                {
                    var books = context.Book.ToList();

                    foreach(var book in books)
                    {
                        Console.WriteLine($"  '{book.Title}' by {book.Author}");
                    }
                }
            }
        }
    }

Again, we create an instance of PostgreContext. Then, we get a list of all books by calling the Book.ToList method. Finally, the list is printed to the console.

Add Redis caching

Add Redis connection string

Edit App.config and insert new connection string to the Redis database.

    <configuration>
      (...)
      <connectionStrings>
        <add name="PostgreSQL" connectionString="host=hostname;port=5432;database=databaseName;user id=userName;password=secret" providerName="Npgsql" />
        <add name="Redis" connectionString="hostname:6379,password=secret"/>
      </connectionStrings>
      (...)
    </configuration>

In order to easily access the connection string later we have to add a reference to System.Configuration assembly – right click the project and choose Add -> Reference from the context menu. Next, select Assemblies -> Framework, find System.Configuration and check the checkbox next to it.

Add cache support

Add EFCache.Redis NuGet package that extends Entity Framework Cache by adding Redis support.

It will update the Entity Framework to 6.1.3 version due to dependencies.

Define caching policy

A cache needs to know how to forget data and that's done through a caching policy. Let's set one for our Redis cache by first adding RedisCachingPolicy to the Entities folder.

The class has to inherit from EFCache.CachingPolicy.

    using System;
    using System.Collections.ObjectModel;
    using System.Data.Entity.Core.Metadata.Edm;
    using EFCache;

    namespace RedisCacheForPostgre.Entities
    {
        public class RedisCachingPolicy : CachingPolicy
        {
            protected override void GetExpirationTimeout(ReadOnlyCollection affectedEntitySets, out TimeSpan slidingExpiration, out DateTimeOffset absoluteExpiration)
            {
                slidingExpiration = TimeSpan.FromMinutes(5);
                absoluteExpiration = DateTimeOffset.Now.AddMinutes(30);
            }
        }
    }

There is GetExpirationTimeout method overridden – it configures:

• absoluteExpiration = 30 minutes, means that every cache entry will expire after 30 minutes.
• slidingExpiration = 5 minutes, means that a cache entry might be expired if it hasn’t been accessed in 5 minutes (sooner than the above).

Of course, it’s useless at this point as the class is used nowhere.

Enable Entity Framework cache

Let’s add the last class to the project a file called Configuration.cs.

It should inherit from System.Data.Entity.DbConfiguration.

    using EFCache;
    using EFCache.Redis;
    using System.Configuration;
    using System.Data.Entity;
    using System.Data.Entity.Core.Common;

    namespace RedisCacheForPostgre.Entities
    {
        public class Configuration : DbConfiguration
        {
            public Configuration()
            {
                var redisConnection = ConfigurationManager.ConnectionStrings["Redis"].ToString();
                var cache = new RedisCache(redisConnection);
                var transactionHandler = new CacheTransactionHandler(cache);
                AddInterceptor(transactionHandler);

                Loaded += (sender, args) =>
                {
                    args.ReplaceService(
                        (s, _) => new CachingProviderServices(s, transactionHandler, new RedisCachingPolicy())
                        );
                };
            }
        }
    }

Entity Framework will search for a class that inherits DbConfiguration at runtime. This way the class becomes a code-based configuration for Entity Framework.

There are a few things happening here.

• Redis connection string is read from an application configuration
• RedisCache object is created – it’s responsible for reading from and writing to the Redis database
• CacheTransactionHandler is created and registered – it monitors database transactions
• On Loaded event replaces the default provider with CachingProviderServices – it tries to get items from the Redis cache first and falls back to the standard provider. Note that we pass a new instance of RedisCachingPolicy – the class was defined in the previous point and is responsible for caching rules (e.g. when data should be forgotten).

Finally, let’s try to run the application. It will print the same set of books. But having a look at Redis database, you can see that new entries appeared there.

Redis Cache Mode

Please also remember to set the Redis to cache mode, otherwise, it’ll keep expanding and scaling up.

Summary

In the sample data used you won’t see significant improvement. It’s caused by small types of queries used (one query) and a very limited number of items queried. The regular PostgreSQL database can optimize it quite well.

The point of this article was to show how easy it is to add the caching to the Entity Framework and how transparent it is. Once the cache was added to the framework we didn’t have to change anything in the PrintBooks method and it still worked. The same would apply for all Entity Framework queries (if we had more).

Do you want to shed light on a favorite feature in your preferred database? Why not write about it for Write Stuff?

attribution Patrick Tomasso