Recently I was working on a project where I needed a backing store for persisting structured data with EntiyFramework Microsoft SQL Server being my first choice. However, for a prototype we were building we needed something architecturally similar, but only persisting in memory (and no extra installations needed). A couple of years ago, we used the InMemory Provider of EF, but as I found out, this is now discouraged to use. Enter Sqlite. In contrast to the in-memory provider we get a real SQL database, however with some kind of reduced functionality (especially with ALTER/DROP COLUMN and ROWVERSION columns) and weird behaviour (after closing and reopening the the database connection you start with a new empty database).

In this post I show you quickly what I did to get the Sqlite database running, so I was able to use

• migrations (as I use them later with Microsoft SQL Server too)
• be able to test it from unit tests
• and use a RowVersion byte[] column for concurrency

There is some documentation on the internet, where people get the concurrency thing to work by addedin SQlite specific database triggers. However, this is something I wanted to avoid.

Support for Different Providers

As I wanted to use the same DbContext for my different database providers, I went for option 2 in Migrations with Multiple Providers and started creating separate migrations for Sqlite and SqlServer.

dotnet ef migrations add MyMigration --project ../SqlServerMigrations -- --provider SqlServer
dotnet ef migrations add MyMigration --project ../SqliteMigrations -- --provider Sqlite

As mentioned before, Sqlite needs an open connection the whole time, otherwise the database gets recreated, so I had to use an unusual way to get my DbContext. Instead of initialising the DbContext with a connection string. I created a SqliteConnection singleton in my DI container, that would create the DbContext for me:

public class SqliteConnection<T> : IDisposable
  where T : DbContext
{
  private readonly DbConnection connection;
  private readonly DbContextOptions<T> contextOptions;

  public SqliteConnection()
  {
    connection = new ("Filename=:memory:");
    connection.Open();

    contextOptions = new DbContextOptionsBuilder<T>()
      .UseSqlite(connection)
      .Options;

    using var context = CreateContext();
    context.Database.Migrate();
  }

  public T CreateContext()
  {
    var context = (T) Activator.CreateInstance(typeof(T), contextOptions);

    return context;
  }
}

The setup for the connection and the context then looked like this:

services.AddDbContext<MyDbContext>();
services.TryAddSingleton(typeof(SqliteInMemoryConnection<MyDbContext>));
services.AddScoped<DbContext>(s => s.GetRequiredService<SqliteConnection<MyDbContext>>().CreateContext());

So with this I could at any time create a custom DbContext in my unit tests or have the “real” DbContext and have the mgirations applied, whenever the in-memory database was being created.

All my entity types in the database derive from the same interface which among other defines a [Timestamp] byte[] RowVersion {get;set;} property. And here the problems began.

Sqlite does not support this. And it does not support writing random data into the RowVersion on add or update either. Not even with ValueGeneratedOnAdd().

So I did override the OnModelCreating method with some statement to help Sqlite. With some reflection, I discovered all DbSet<T> in my DbContext and added this:

modelBuilder.Entity(entityType) // the type of the DbSet<>
  .Property(typeof(byte[]), nameof(RowVersion))
  .IsConcurrencyToken()
  .IsRowVersion()
  .ValueGeneratedNever()
  .HasValueGenerator<RowVersionValueGenerator>()
  ;

And the RowVersionValueGenerator is just a somple pseudo-random number generator:

public class RowVersionValueGenerator : ValueGenerator<byte[]>
{
  public override byte[] Next(EntityEntry entry) => PseudoRandomBlobGenerator.Next();

  public override bool GeneratesTemporaryValues => false;
}

public static class PseudoRandomBlobGenerator
{
  private static readonly Random _random;
  
  static PseudoRandomBlobGenerator()
  {
    _random = new Random((int)DateTimeOffset.Now.Ticks);
  }

  public static byte[] Next()
  {
    var bytes = new byte[8];

    _random.NextBytes(bytes);

    return bytes;
  }
}

This helped me to get a RowVersion whenever I added or updated an entity on a DbContext. However, this did not solve me the concurrency issue, as Sqlite would *not* update the RowVersion on saving. But there was help in the form of the DbContext.SavingChanges event

Simply adding a delegate to our CreateContext method, was all it needed:

// added inside SqliteConnection.CreateContext
result.SavingChanges += OnResultOnSavingChanges;


private static void OnResultOnSavingChanges(object? sender, SavingChangesEventArgs args)
{
  var context = (T)sender;

  context.ChangeTracker.Entries()
    .Where(e => e.State == EntityState.Modified)
    .ForEach(e =>
    {
      if (e.Entity is not IDataEntity entity) return;

      entity.RowVersion = PseudoRandomBlobGenerator.Next();
    });
}

Finally, we have to make sure, that our DbContext checks if the Provider is Sqlite and only then adds the modifications, as SqlServer can handle this by itself.

With all this in place, I can run my standalone application with a “full” featured in-memory SQL database, have all my unit tests run against their own unit test, have separate migrations targeting the correct SQL dialect and still get concurrency support.

How great is that?! happy coding