asp.net core Integration test for database

Question

I am trying to setup integration testing of the database in an asp.netcore project. I am using code first approach to create the database.
For the testing iam using the nudget packages XUnit, FluentAssertions and NUnitestApadter3. When i run the test for the first-time the test passes.

[Collection("Integration test collection")]
public class BookServiceTest : IntegrationTestBase
{
    [Fact]
    public void CanCreateUser()
    {
        using (var context = GivenBPDContext())
        {
            var Book = new BookService(context);

            Data.Database.Entities.Book book = Book.AddNewBook("test");
            context.SaveChanges();

            book.Id.Should().NotBe(0);
            book.Name.Should().Be("test");
        }
    }
}

public class IntegrationTestBase
{
    protected static BPDContext GivenBPDContext()
    {

        var context = new BPDContext(new DbContextOptionsBuilder().Options);

        return context;
    }
    // i tried dropping the database here and it do not work
}

A very basic logic test

public class BookService
{
    private BPDContext _context;

    public BookService(BPDContext context)
    {
        _context = context;
    }

    public Book AddNewBook(string name)
    {
        var book = _context.Books
            .FirstOrDefault(x => x.Name == name);

        if (book == null)
        {
            book = _context.Books.Add(new Data.Database.Entities.Book
            {
                Name = name,
            }).Entity;
        }

        return book;
    }
}

The second time i run the test and change a value being tested it fails. I need a way of dropping the database after each test and then running the migrations to get the database up to the correct version.

Below is how i setup up the database. startup.cs

public void ConfigureServices(IServiceCollection services)
{
    services.AddMvc();
    services.AddTransient<IBPDRepository, BPDRepository>();
    services.AddDbContext<BPDContext>();
}

public class BPDContext:DbContext
{
    public DbSet<Entities.Book> Books { get; set; }
    public DbSet<Entities.User> User { get; set; }
    public DbSet<Entities.Reviewer> Reviewer { get; set; }

    public BPDContext(DbContextOptions options):base(options)
    {

    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        //maybe put something in if its in testing mode
        optionsBuilder.UseSqlServer("Server = (localdb)\\mssqllocaldb;Database = BookProjectDatabase;Trusted_Connection = True; ", options => options.MaxBatchSize(30));
        optionsBuilder.EnableSensitiveDataLogging();
    }

}

In summary I need to delete the database before each test run, then updated the database with the migrations and finally perform the units .

Answer 1

Take a look at Respawn. Another option to avoid migrations is to do a database snapshot/restore. Finally, you could, prior to each test, start a new TransactionScope and then call its Dispose() method after the transaction without calling its Complete() method. This will abort the transaction and roll the database back to the state it was in prior to running the test.

Dropping the database is a bit heavy-handed and will likely increase the time it takes to run your tests.