Registering a LocalDb instance via MS DI.

RegisterLocalDb.cs

static void RegisterDbContext<TContext>(IServiceCollection services)
        where TContext : DbContext
{
    services.AddSingleton<SqlInstance<TContext>>(p => new SqlInstance<TContext>(builder => (TContext)ActivatorUtilities.CreateInstance<TContext>(p, builder.Options)));

    services.AddScoped<SqlDatabase<TContext>>(p =>
    {
        SqlInstance<TContext> sqlInstance = p.GetRequiredService<SqlInstance<TContext>>();

        // Safe excecution of async via dedicated factory and unwrapping.
        return RunSync(() => sqlInstance.Build(typeof(TContext).Name));
    });

    services.AddScoped<TContext>(p =>
    {
        SqlDatabase<TContext> database = p.GetRequiredService<SqlDatabase<TContext>>();
        return database.NewDbContext();
    });
}

The code above is used to register each db context against the service collection. This is done via a TestServer instance that is used as a fixture for the integration tests.

Doesn't work....yet.

First run....
'Failed to execute SQL command. commandText: declare @command nvarchar(max) set @command = ''

Subsequent runs.
Microsoft.Data.SqlClient.SqlException : There is already an object named 'Tenants' in the database.

EfLocalDb is not really setup for that use case. try moving to the LocalDb nuget and use something like this

public static class DbBuilder<TContext>
    where TContext : DbContext
{
    static SqlInstance sqlInstance;

    public static void Initialise(
        Func<DbContextOptionsBuilder<TContext>, TContext> getInstance)
    {
        sqlInstance = new SqlInstance(
            typeof(TContext).Name,
            connection =>
            {
                var builder = new DbContextOptionsBuilder<TContext>();
                builder.UseSqlServer(connection);
                return getInstance(builder).Database.EnsureCreatedAsync();
            });
    }

    static async Task RegisterDbContext(IServiceCollection services, string testName)
    {
        var database = await sqlInstance.Build(testName);
        services.AddSingleton(database);
        services.AddScoped(p =>
        {
            var builder = new DbContextOptionsBuilder<TContext>();
            builder.UseSqlServer(database.Connection);
            return ActivatorUtilities.CreateInstance<TContext>(p, builder.Options);
        });
    }
}

@SimonCropp just getting back to this.

Couple of questions....

Does this mean a unique database is created per test? If so, how do we clean up after?

When would you call Initialise on the SQLInstance singleton?

Basically I'm looking to transparently inject DbContext instances as if I were using SQLServer. SQLite is an absolute joke with a lack of support for foreign key migrations.

sorry for the delay

SqlInstance maps to a sql server instance. it should be called once per appdomain. so eg in a module init, in a static ctor of a test base class, in a testfixture global [startup]. i find the static ctor of a test base class to be the most common since u only pay the startup cost on test that use the db. so if u run a non-db test in isolation, the db interaction wont inpact it.

it is a unique db per test method. they are not cleaned up, but left running after a test so u can debug the db instance if necessary. u can opt in to delete the db https://github.com/SimonCropp/LocalDb/blob/master/src/LocalDb/SqlDatabase.cs#L55

Database files older than a day will be purged from the data directory.