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.

...WithLogTo

There are variations of these methods that include logging output - see Tools for capturing EF Core logging for more info.