Working sample at the end of this article (I kept all things I tried [reason for long article], so that others could benefit from it later)
I am trying to write integration tests for my EF Core 3.1 class library. As unit test framework, I have used XUnit and followed guide from Microsoft: https://learn.microsoft.com/en-us/ef/core/testing/sharing-databases
Here is how the setup looks like (it's a bit longer because I am actually creating a database in my SQL Server in case I need to see the real result from tests output):
public class SharedDatabaseFixture : IDisposable
{
private static readonly object _lock = new object();
private static bool _databaseInitialized;
private static string _DatabaseName = "Database.Server.Local";
private static IConfigurationRoot config;
public SharedDatabaseFixture()
{
config = new ConfigurationBuilder()
.AddJsonFile($"appsettings.Development.json", true, true)
.Build();
var test = config.GetValue<string>("DataSource");
var connectionStringBuilder = new SqlConnectionStringBuilder
{
DataSource = config.GetValue<string>("DataSource"),
InitialCatalog = _DatabaseName,
IntegratedSecurity = true,
};
var connectionString = connectionStringBuilder.ToString();
Connection = new SqlConnection(connectionString);
CreateEmptyDatabaseAndSeedData();
Connection.Open();
}
public bool ShouldSeedActualData { get; set; } = true;
public DbConnection Connection { get; set; }
public ApplicationDbContext CreateContext(DbTransaction transaction = null)
{
var identity = new GenericIdentity("[email protected]", "Admin");
var contextUser = new ClaimsPrincipal(identity); //add claims as needed
var httpContext = new DefaultHttpContext() { User = contextUser };
var defaultHttpContextAccessor = new HttpContextAccessor();
defaultHttpContextAccessor.HttpContext = httpContext;
var context = new ApplicationDbContext(new DbContextOptionsBuilder<ApplicationDbContext>().UseSqlServer(Connection).Options, null, defaultHttpContextAccessor);
if (transaction != null)
{
context.Database.UseTransaction(transaction);
}
return context;
}
private static void ExecuteSqlCommand(SqlConnectionStringBuilder connectionStringBuilder, string commandText)
{
using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = commandText;
command.ExecuteNonQuery();
}
}
}
private static SqlConnectionStringBuilder Master => new SqlConnectionStringBuilder
{
DataSource = config.GetValue<string>("DataSource"),
InitialCatalog = "master",
IntegratedSecurity = true
};
private static string Filename => Path.Combine(Path.GetDirectoryName(typeof(SharedDatabaseFixture).GetTypeInfo().Assembly.Location), $"{_DatabaseName}.mdf");
private static string LogFilename => Path.Combine(Path.GetDirectoryName(typeof(SharedDatabaseFixture).GetTypeInfo().Assembly.Location), $"{_DatabaseName}_log.ldf");
private static void CreateDatabaseRawSQL()
{
ExecuteSqlCommand(Master, $@"IF(db_id(N'{_DatabaseName}') IS NULL) BEGIN CREATE DATABASE [{_DatabaseName}] ON (NAME = '{_DatabaseName}', FILENAME = '{Filename}') END");
}
private static List<T> ExecuteSqlQuery<T>(SqlConnectionStringBuilder connectionStringBuilder, string queryText, Func<SqlDataReader, T> read)
{
var result = new List<T>();
using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = queryText;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result.Add(read(reader));
}
}
}
}
return result;
}
private static void DestroyDatabaseRawSQL()
{
var fileNames = ExecuteSqlQuery(Master, $@"SELECT [physical_name] FROM [sys].[master_files] WHERE [database_id] = DB_ID('{_DatabaseName}')", row => (string)row["physical_name"]);
if (fileNames.Any())
{
ExecuteSqlCommand(Master, $@"ALTER DATABASE [{_DatabaseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;EXEC sp_detach_db '{_DatabaseName}', 'true'");
fileNames.ForEach(File.Delete);
}
if (File.Exists(Filename))
File.Delete(Filename);
if (File.Exists(LogFilename))
File.Delete(LogFilename);
}
private void CreateEmptyDatabaseAndSeedData()
{
lock (_lock)
{
if (!_databaseInitialized)
{
using (var context = CreateContext())
{
try
{
DestroyDatabaseRawSQL();
}
catch (Exception) { }
try
{
CreateDatabaseRawSQL();
context.Database.EnsureCreated();
}
catch (Exception) { }
if (ShouldSeedActualData)
{
List<UserDB> entities = new List<UserDB>()
{
new UserDB() { Id = "[email protected]", Name= "Admin" }
};
context.Users.AddRange(entities);
context.SaveChanges();
List<IdentityRole> roles = new List<IdentityRole>()
{
new IdentityRole(){Id = "ADMIN",Name = nameof(DefaultRoles.Admin), NormalizedName = nameof(DefaultRoles.Admin)},
new IdentityRole(){Id = "FINANCE",Name = nameof(DefaultRoles.Finance), NormalizedName = nameof(DefaultRoles.Finance)}
};
context.Roles.AddRange(roles);
context.SaveChanges();
}
}
_databaseInitialized = true;
}
}
}
public void Dispose()
{
Connection.Dispose();
}
}
Then, test class looks like following (for simplicity showing just 2 tests):
public class BaseRepositoryTests : IClassFixture<SharedDatabaseFixture>
{
private readonly SharedDatabaseFixture fixture;
private IMapper _mapper;
public BaseRepositoryTests(SharedDatabaseFixture fixture)
{
this.fixture = fixture;
var config = new MapperConfiguration(opts =>
{
opts.AddProfile<CountriesDBMapper>();
opts.AddProfile<EmployeeDBMapper>();
opts.AddProfile<EmployeeAccountDBMapper>();
});
_mapper = config.CreateMapper();
}
[Fact]
public async Task EntityCannotBeSavedIfDbEntityIsNotValid()
{
using (var transaction = fixture.Connection.BeginTransaction())
{
using (var context = fixture.CreateContext(transaction))
{
var baseCountryRepository = new BaseRepository<CountryDB, Country>(context, _mapper);
var invalidCountry = new Country() { };
//Act
var exception = await Assert.ThrowsAsync<DbUpdateException>(async () => await baseCountryRepository.CreateAsync(invalidCountry));
Assert.NotNull(exception.InnerException);
Assert.Contains("Cannot insert the value NULL into column", exception.InnerException.Message);
}
}
}
[Fact]
public async Task EntityCanBeSavedIfEntityIsValid()
{
using (var transaction = fixture.Connection.BeginTransaction())
{
using (var context = fixture.CreateContext(transaction))
{
var baseCountryRepository = new BaseRepository<CountryDB, Country>(context, _mapper);
var item = new Country() { Code = "SK", Name = "Slovakia" };
//Act
var result = await baseCountryRepository.CreateAsync(item);
Assert.NotNull(result);
Assert.Equal(1, result.Id);
}
}
}
}
Finally here is a sample of repository implementation (CRUD):
public async Task<TModel> CreateAsync(TModel data)
{
var newItem = mapper.Map<Tdb>(data);
var entity = await context.Set<Tdb>().AddAsync(newItem);
await context.SaveChangesAsync();
return mapper.Map<TModel>(entity.Entity);
}
public async Task<bool> DeleteAsync(long id)
{
var item = await context.Set<Tdb>().FindAsync(id).ConfigureAwait(false);
if (item == null)
throw new ArgumentNullException();
var result = context.Set<Tdb>().Remove(item);
await context.SaveChangesAsync();
return (result.State == EntityState.Deleted || result.State == EntityState.Detached);
}
If I run these tests individually, each one of them passes without a problem. However if I run all tests from BaseRepositoryTests then I am getting random problems as somehow, database transactions are not rolled back but the data is saved and shared between tests.
I have checked and truly, each transaction has it's own unique ID, thus they should not collide. Am I missing anything here? I mean according to Microsoft this is correct approach, but there is clearly something I have missed. The only thing different from other guides that I could find is, that I am using SaveChangesAsync in my repository implementation, while other's use SaveChanges...however I believe that this should not be the root cause of my problem.
Any help in respect to this matter would be highly appreciated.
Update 1:
As suggested by comments, I have tried two separate approaches. First one was to use CommitableTransaction like following:
Method update:
[Fact]
public async Task EntityCanBeSavedIfEntityIsValid()
{
using (var transaction = new CommittableTransaction(new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))
{
using (var context = fixture.CreateContext(transaction))
{
var baseCountryRepository = new BaseRepository<CountryDB, Country>(context, _mapper);
var item = new Country() { Code = "SK", Name = "Slovakia" };
//Act
var result = await baseCountryRepository.CreateAsync(item);
Assert.NotNull(result);
Assert.Equal(1, result.Id);
}
}
}
Shared fixture update:
public ApplicationDbContext CreateContext(CommittableTransaction transaction = null)
{
... other code
if (transaction != null)
{
context.Database.EnlistTransaction(transaction);
}
return context;
}
This unfortunately ended with the same result when running my code tests in bulk (data that I was saving ended up being incremented and not discarded after each test)
Second thing I tried was using TransactionScope like following:
[Fact]
public async Task EntityCanBeModifiedIfEntityExistsAndIsValid()
{
using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }, TransactionScopeAsyncFlowOption.Enabled))
{
using (var context = fixture.CreateContext())
{
var baseCountryRepository = new BaseRepository<CountryDB, Country>(context, _mapper);
var item = new Country() { Code = "SK", Name = "Slovakia" };
//Act
var insertResult = await baseCountryRepository.CreateAsync(item);
Assert.NotNull(insertResult);
Assert.Equal(1, insertResult.Id);
Assert.Equal("SK", insertResult.Code);
Assert.Equal("Slovakia", insertResult.Name);
//Act
insertResult.Code = "SVK";
var result = await baseCountryRepository.UpdateAsync(insertResult.Id, insertResult);
Assert.Equal(1, result.Id);
Assert.Equal("SVK", result.Code);
Assert.Equal("Slovakia", result.Name);
}
scope.Complete();
}
}
Just as before this did not yield any new results.
Last thing that I tried was to drop :IClassFixture<SharedDatabaseFixture> from the test class and instead, create a new instance of my database fixture in constructor (which is being triggered for each test run) like following:
public BaseRepositoryTests()
{
this.fixture = new SharedDatabaseFixture();
var config = new MapperConfiguration(opts =>
{
opts.AddProfile<CountriesDBMapper>();
opts.AddProfile<EmployeeDBMapper>();
opts.AddProfile<EmployeeAccountDBMapper>();
});
_mapper = config.CreateMapper();
}
Just as before, no new results came from this update.
Working setup
Shared database fixture (basically class responsible for creating database...the main difference between previous version now is, that in constructor it accepts unique guid that is used when creating database -> to create a database with unique name. Furthermore I have also added a new method ForceDestroyDatabase() which is responsible to destroy the database after test has done it's job. I did not place it in Dispose() method, as sometimes you want to check what actually happened to database, where in that case you just don't call the method...see later)
public class SharedDatabaseFixture : IDisposable
{
private static readonly object _lock = new object();
private static bool _databaseInitialized;
private string _DatabaseName = "FercamPortal.Server.Local.";
private static IConfigurationRoot config;
public SharedDatabaseFixture(string guid)
{
config = new ConfigurationBuilder()
.AddJsonFile($"appsettings.Development.json", true, true)
.Build();
var test = config.GetValue<string>("DataSource");
this._DatabaseName += guid;
var connectionStringBuilder = new SqlConnectionStringBuilder
{
DataSource = config.GetValue<string>("DataSource"),
InitialCatalog = _DatabaseName,
IntegratedSecurity = true,
};
var connectionString = connectionStringBuilder.ToString();
Connection = new SqlConnection(connectionString);
CreateEmptyDatabaseAndSeedData();
Connection.Open();
}
...other code the same as above, skipped for clarity
private void CreateEmptyDatabaseAndSeedData()
{
lock (_lock)
{
using (var context = CreateContext())
{
try
{
DestroyDatabaseRawSQL();
}
catch (Exception ex) { }
try
{
CreateDatabaseRawSQL();
context.Database.EnsureCreated();
}
catch (Exception) { }
if (ShouldSeedActualData)
{
List<UserDB> entities = new List<UserDB>()
{
new UserDB() { Id = "[email protected]", Name= "Robert Moq" },
new UserDB() { Id = "[email protected]", Name= "Test User" }
};
context.Users.AddRange(entities);
context.SaveChanges();
List<IdentityRole> roles = new List<IdentityRole>()
{
new IdentityRole(){Id = "ADMIN",Name = nameof(FercamDefaultRoles.Admin), NormalizedName = nameof(FercamDefaultRoles.Admin)},
new IdentityRole(){Id = "FINANCE",Name = nameof(FercamDefaultRoles.Finance), NormalizedName = nameof(FercamDefaultRoles.Finance)}
};
context.Roles.AddRange(roles);
context.SaveChanges();
}
}
}
}
public void ForceDestroyDatabase()
{
DestroyDatabaseRawSQL();
}
public void Dispose()
{
Connection.Close();
Connection.Dispose();
}
}
Sample test class:
public class DailyTransitDBRepositoryTests : IDisposable
{
private readonly SharedDatabaseFixture fixture;
private readonly ApplicationDbContext context;
private IMapper _mapper;
public DailyTransitDBRepositoryTests()
{
this.fixture = new SharedDatabaseFixture(Guid.NewGuid().ToString("N"));
this.context = this.fixture.CreateContext();
this.context.Database.OpenConnection();
var config = new MapperConfiguration(opts =>
{
opts.AddProfile<DailyTransitDBMapper>();
opts.AddProfile<EmployeeDBMapper>();
opts.AddProfile<EmployeeAccountDBMapper>();
opts.AddProfile<CountriesDBMapper>();
});
_mapper = config.CreateMapper();
}
...other code ommited for clarity
public void Dispose()
{
this.context.Database.CloseConnection();
this.context.Dispose();
this.fixture.ForceDestroyDatabase();
this.fixture.Dispose();
}
[Fact]
public async Task GetTransitsForYearAndMonthOnlyReturnsValidItems()
{
var employees = await PopulateEmployeesAndReturnThemAsList(context);
var countries = await PopulateCountriesAndReturnThemAsList(context);
var transitRepository = new DailyTransitDBRepository(context, _mapper);
var transitItems = new List<DailyTransit>() {
new DailyTransit()
{
Country = countries.First(),
Employee = employees.First(),
Date = DateTime.Now,
TransitionDurationType = DailyTransitDurationEnum.FullDay
},
new DailyTransit()
{
Country = countries.First(),
Employee = employees.Last(),
Date = DateTime.Now.AddDays(1),
TransitionDurationType = DailyTransitDurationEnum.FullDay
},
new DailyTransit()
{
Country = countries.First(),
Employee = employees.Last(),
Date = DateTime.Now.AddMonths(1),
TransitionDurationType = DailyTransitDurationEnum.FullDay
}
};
//Act
await transitRepository.CreateRangeAsync(transitItems);
//retrieve all items
using (var context2 = fixture.CreateContext())
{
var transitRepository2 = new DailyTransitDBRepository(context2, _mapper);
var items = await transitRepository2.GetEmployeeTransitsForYearAndMonth(DateTime.Now.Year, DateTime.Now.Month);
Assert.Equal(2, items.Count());
Assert.Equal("Janko", items.First().Employee.Name);
Assert.Equal("John", items.Last().Employee.Name);
}
}
}
Robert, Glad It helped! As per your request, I re-submit the answer for anyone that could find this answer helpful as you.
I learn the hard way that trying to share the entity framework database context over IClassFixture or CollectionFixtures would eventually end up in tests being polluted with another test data or deadlock/race conditions due to the parallel execution of xUnit, entity framework throwing exceptions because it already tracked that object with a given Id and more headaches like that.
Personally, I would kindly recommend that for your specific use cause, stick the database context creation/cleanup within the constructor/dispose alternative such as:
public class TestClass : IDisposable
{
DatabaseContext DatabaseContext;
public TestClass()
{
var options = new DbContextOptionsBuilder<DatabaseContext>()
.UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
.Options;
DatabaseContext = new DatabaseContext(options);
//insert the data that you want to be seeded for each test method:
DatabaseContext.Set<Product>().Add(new Product() { Id = 1, Name = Guid.NewGuid().ToString() });
DatabaseContext.SaveChanges();
}
[Fact]
public void FirstTest()
{
var product = DatabaseContext.Set<Product>().FirstOrDefault(x => x.Id == 1).Name;
//product evaluates to => 0f25a10b-1dfd-4b4b-a69d-4ec587fb465b
}
[Fact]
public void SecondTest()
{
var product = DatabaseContext.Set<Product>().FirstOrDefault(x => x.Id == 1).Name;
//product evaluates to => eb43d382-40a5-45d2-8da9-236d49b68c7a
//It's different from firstTest because is another object
}
public void Dispose()
{
DatabaseContext.Dispose();
}
}
Of course you can always do some refinement, but the idea is there