Redis cache for PostgreSQL in Entity Framework 6


facebooktwittergoogle_pluslinkedinmail

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 the 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.

This article was originally published on Compose: https://compose.com/articles/how-to-enable-a-redis-cache-for-postgresql-with-entity-framework-6/

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 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 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 latest version of Npgsql.EntityFramework NuGet package.

image

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

image

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 strict .NET requirement. Otherwise the application will crash in 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.

image

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 ‘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 it’s primary key column.

Define the database context

Add PostgreContext class to the Entities folder.

image

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 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.

image

Query PostgreSQL database

We have the sample data in the database, so let’s query it in the application. Add 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 crate instance of PostgreContext. Then, we get a list of all books by calling Book.ToList method. Finally, the list is printed to the console.

image

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.

image

Add cache support

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

image

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

image

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.

image

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 – Configuration.

image

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 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 default provider with CachingProviderServices – it tries to get items from Redis cache first and falls back to 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.

image

Redis Cache Mode

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

image

Summary

In the sample data used you won’t see significant improvement. It’s caused by small types of queries used (one query) and 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).

facebooktwittergoogle_pluslinkedinmail

Leave a comment

Your email address will not be published. Required fields are marked *