Interactions with SqlLocalDB via Entity Framework Core.
https://nuget.org/packages/EfLocalDb/
The snippets use a DbContext of the following form:
public class TheDbContext(DbContextOptions options) :
DbContext(options)
{
public DbSet<TheEntity> TestEntities { get; set; } = null!;
protected override void OnModelCreating(ModelBuilder model)
=> model.Entity<TheEntity>();
}public class TheEntity
{
public int Id { get; set; }
public string? Property { get; set; }
}SqlInstance needs to be initialized once.
To ensure this happens only once there are several approaches that can be used:
In the static constructor of a test.
If all tests that need to use the SqlInstance existing in the same test class, then the SqlInstance can be initialized in the static constructor of that test class.
[TestFixture]
public class Tests
{
static SqlInstance<TheDbContext> sqlInstance;
static Tests() =>
sqlInstance = new(builder => new(builder.Options));
[Test]
public async Task Test()
{
var entity = new TheEntity
{
Property = "prop"
};
await using var database = await sqlInstance.Build([entity]);
AreEqual(1, database.Context.TestEntities.Count());
}If multiple tests need to use the SqlInstance, then the SqlInstance should be initialized in the static constructor of test base class.
public abstract class TestBase
{
static SqlInstance<TheDbContext> sqlInstance;
static TestBase() =>
sqlInstance = new(
constructInstance: builder => new(builder.Options));
public static Task<SqlDatabase<TheDbContext>> LocalDb(
[CallerFilePath] string testFile = "",
string? databaseSuffix = null,
[CallerMemberName] string memberName = "") =>
sqlInstance.Build(testFile, databaseSuffix, memberName);
}
public class Tests :
TestBase
{
[Test]
public async Task Test()
{
await using var database = await LocalDb();
var entity = new TheEntity
{
Property = "prop"
};
await database.AddData(entity);
AreEqual(1, database.Context.TestEntities.Count());
}
}Some SqlServer options are exposed by passing a Action<SqlServerDbContextOptionsBuilder> to the SqlServerDbContextOptionsExtensions.UseSqlServer. In this project the UseSqlServer is handled internally, so the SqlServerDbContextOptionsBuilder functionality is achieved by passing a action to the SqlInstance.
var sqlInstance = new SqlInstance<MyDbContext>(
constructInstance: builder => new(builder.Options),
sqlOptionsBuilder: sqlBuilder => sqlBuilder.EnableRetryOnFailure(5));Data can be seeded into the template database for use across all tests:
public class BuildTemplate
{
static SqlInstance<TheDbContext> sqlInstance;
static BuildTemplate() =>
sqlInstance = new(
constructInstance: builder => new(builder.Options),
buildTemplate: async context =>
{
await context.Database.EnsureCreatedAsync();
var entity = new TheEntity
{
Property = "prop"
};
context.Add(entity);
await context.SaveChangesAsync();
});
[Test]
public async Task BuildTemplateTest()
{
await using var database = await sqlInstance.Build();
AreEqual(1, database.Context.TestEntities.Count());
}Usage inside a test consists of two parts:
await using var database = await sqlInstance.Build();await using (var data = database.NewDbContext())
{The above are combined in a full test:
public class EfSnippetTests
{
public class MyDbContext(DbContextOptions options) :
DbContext(options)
{
public DbSet<TheEntity> TestEntities { get; set; } = null!;
protected override void OnModelCreating(ModelBuilder model) =>
model.Entity<TheEntity>();
}
static SqlInstance<MyDbContext> sqlInstance;
static EfSnippetTests() =>
sqlInstance = new(builder => new(builder.Options));
[Test]
public async Task TheTest()
{
await using var database = await sqlInstance.Build();
await using (var data = database.NewDbContext())
{
var entity = new TheEntity
{
Property = "prop"
};
data.Add(entity);
await data.SaveChangesAsync();
}
await using (var data = database.NewDbContext())
{
AreEqual(1, data.TestEntities.Count());
}
}
[Test]
public async Task TheTestWithDbName()
{
await using var database = await sqlInstance.Build("TheTestWithDbName");
var entity = new TheEntity
{
Property = "prop"
};
await database.AddData(entity);
AreEqual(1, database.Context.TestEntities.Count());
}BuildShared creates a single database from the template once and reuses it across calls. This is useful for query-only tests that don't need per-test isolation.
[Test]
public async Task SharedDatabase()
{
await using var database = await instance.BuildShared();
var count = await database.Context.TestEntities.CountAsync();
AreEqual(0, count);
}Pass useTransaction: true to get an auto-rolling-back transaction, allowing writes without affecting other tests.
Note: useTransaction: true means that on test failure the resulting database cannot be inspected (since the transaction is rolled back). A workaround when debugging a failure is to temporarily remove useTransaction: true.
[Test]
public async Task SharedDatabase_WithTransaction()
{
await using (var database = await instance.BuildShared(useTransaction: true))
{
NotNull(database.Transaction);
database.Context.Add(new TestEntity { Property = "shared" });
await database.Context.SaveChangesAsync();
}
// Data should be rolled back
await using var database2 = await instance.BuildShared();
var count = await database2.Context.TestEntities.CountAsync();
AreEqual(0, count);
}When building a DbContextOptionsBuilder the default configuration is as follows:
static class DefaultOptionsBuilder
{
static LogCommandInterceptor interceptor = new();
public static DbContextOptionsBuilder<TDbContext> Build<TDbContext>()
where TDbContext : DbContext
{
var builder = new DbContextOptionsBuilder<TDbContext>();
if (LocalDbLogging.SqlLoggingEnabled)
{
builder.AddInterceptors(interceptor);
}
builder.ReplaceService<IQueryProvider, QueryProvider>();
builder.ReplaceService<IAsyncQueryProvider, QueryProvider>();
builder.ReplaceService<IQueryCompilationContextFactory, QueryContextFactory>();
builder.ReplaceService<ICompiledQueryCacheKeyGenerator, KeyGenerator>();
builder.ConfigureWarnings(_ =>
{
_.Ignore(CoreEventId.SensitiveDataLoggingEnabledWarning);
_.Default(WarningBehavior.Throw);
});
builder.EnableSensitiveDataLogging();
builder.EnableDetailedErrors();
return builder;
}
public static void ApplyQueryTracking<T>(this DbContextOptionsBuilder<T> builder, QueryTrackingBehavior? tracking)
where T : DbContext
{
if (tracking.HasValue)
{
builder.UseQueryTrackingBehavior(tracking.Value);
}
}
}