Using SQL Server databases

Helpers for creating unique SQL Server databases

The library has two methods that will create options to provide an SQL Server database for unit testing. One provides a class-level unique database name, and one provides a method-unique database name.

NOTE: you need at least unit test class-level unique databases when using xUnit, because xUnit runs all the unit test classes in parallel, and you don't want multiple unit tests trying to update the same database!

The CreateUniqueClassOptions() extension method

This returns a SQL Server options with the connection string from the appsettings.json file (see this docs page about this file) but the name of the database now has the type name of the object (which should be this) as a suffix. See test code below

[Fact]
public void TestSqlServerUniqueClassOk()
{
    //SETUP

    //ATTEMPT
    var options = this.CreateUniqueClassOptions<EfCoreContext>();
    using var context = new EfCoreContext(options)

    //VERIFY
    var builder = new SqlConnectionStringBuilder(context.Database.
        GetDbConnection().ConnectionString);
    builder.InitialCatalog.ShouldEndWith(this.GetType().Name);
}

Ability to add extra options to the DbContextOptionsBuilder<T>

The SQL Server options extension methods have an optional parameter that allows you to set extra options at the DbContextOptionsBuilder<T> level. Below is part of the unit tests showing how to add/override options.

//... previous code removed to focus on the feature
var options = this.CreateUniqueClassOptions<BookContext>(
     //sets a tracking behavior
    builder => builder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));
using (var context = new BookContext(options))
{
    //VERIFY
    var book = context.Books.First();
    context.Entry(book).State.ShouldEqual(EntityState.Detached);
}

The CreateUniqueMethodOptions() extension method

This returns a SQL Server options with the connection string from the appsettings.json file but the name of the database now has the type name of the object (which should be this) followed by the method name as a suffix. See test code below

[Fact]
public void TestSqlServerUniqueMethodOk()
{
    //SETUP
    //ATTEMPT
    var options = this.CreateUniqueMethodOptions<EfCoreContext>();
    using var context = new EfCoreContext(options))
    
    //VERIFY
    var builder = new SqlConnectionStringBuilder(context.Database.GetDbConnection().ConnectionString);
    builder.InitialCatalog
        .ShouldEndWith($"{GetType().Name}.{nameof(TestSqlServerUniqueMethodOk)}" );
    
}

NOTE: You shouldn't really need the CreateUniqueMethodOptions<T> method, as xUnit runs the methods inside a test class serially, so CreateUniqueClassOptions<T> should be enough to avoid parallel unit tests accessing the same database.

Creating a new database, which returns EF Core's logging data

Its often useful to see what EF Core is doing when something isn't working properly. The CreateUniqueClassOptionsWithLogTo<T> or CreateUniqueMethodOptionsWithLogTo<T> methods returns the logs. Below is a simple version that captures the logs into a list, but more complex options are possible - see Tools for capturing EF Core logging for more details.

var logs = new List<string>();
var options = this.CreateUniqueClassOptionsWithLogTo<BookContext>(log => logs.Add(log));
using var context = new BookContext(options);
//... rest of test left out

How to get an empty database with the correct schema

There are two ways to create an empty database with the correct schema. The obvious one is to call EnsureDeleted, followed by EnsureCreated. This is an excellent approach, but there is another approach that is quicker.

This uses a method within EF Core's internal testing code that Arthur Vickers alerted me to called EnsureClean. With permission from the EF Core team I have copied this method into this library - see the code below

using var options = this.CreateUniqueClassOptions<EfCoreContext>();
using var context = new BookContext(options);

context.Database.EnsureClean();

NOTE: EnsureDeleted + EnsureCreated is much faster since EF Core 5 due to a change in the Microsoft.Data.SqlClient library, but EnsureClean is maybe twice as fast as EnsureDeleted + EnsureCreated.

The EnsureClean's setUpSchema parameter

NOTE: The EnsureClean method has a parameter boolean parameter called setUpSchema that defaults to true. If you set this to false then it doesn't call the EnsureClean method, which allows you to use another way to set the database schema.*

WARNING: The EnsureClean method doesn't delete the default EF Core migration table, so if you want to call EnsureClean(false) followed by context.Database.Migrate(), then you must set a different migration table name - see this example from the associated GitHub repo for my book, Entity Framework Core in Action, second edition.