Entity Framework unable to delete database, database in use

Question

I am (like many others) running into the problem that I can't get my NUnit tests to delete the database in the [SetUp] of my test fixture.

What I want to achieve

I want to write integration tests that test my code and verify if the expected results are stored in the database (Assert CRUD methods). That, and I want to be able to actually show the tables in SqlServer and see how the results in the database look like. That last part seems a hard thing to achieve...

What is going wrong

I can run my tests, many times in a row. The database is recreated in the [SetUp] every time and the tests pass the asserts. It goes down the drain as soon as I want to check my result in the actual database in SqlServer. Once I opened a connection from SqlServer, the [SetUp] method is not allowed to delete the database, since it has open connections.

What have I tried

• Database initializer
• ALTER database SET SINGLE_USER WITH ROLLBACK IMMEDIATE
• Pooling=false added to connection string

I took those ideas from this and this SO post.

What do I have

The [SetUp] method:

    [SetUp]
    public void SetUp()
    {
        // TenantSeedInitializer extends the 
        // DropCreateDatabaseAlways<TenantApplicationTestContext> class

        Database.SetInitializer(new TenantSeedInitializer());
        _applicationContext = new TenantApplicationTestContext();
        _applicationContext.Database.ExecuteSqlCommand("ALTER DATABASE " + 
            TenantApplicationTestContext.DatabaseName + 
            " SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
    }

The [TearDown] method:

    [TearDown]
    public void TearDown()
    {
        SqlConnection.ClearAllPools();
    }

And a pretty straight forward test:

    [Test]
    public void AddTenant()
    {
        // add a new tenant to the database and verify that there
        // there is only one tenant present in the table  
    }

As I said, running this test several times in a row works like a charm, until the point where I try to open the table in SqlServer.

Two different (for me unsolved) error scenario's

1) Either I am not allowed to view the table because the connection from visual studio is still open.

Database 'TestTenantDatabase' is already open and can only have one user at a time.

Adding the SqlConnection.ClearAllPools(); does not seem to solve this.

2) Or I am allowed to view the table in SqlServer, and then I am no longer allowed to delete the database from my [SetUp] fixture.

Cannot drop database "TestTenantDatabase" because it is currently in use.

Shutting down SqlServer is the only way I know to get rid of this. But then I find myself restarting SqlServer a lot during the day... (an option to close the connection to the database would also help, but I can't find it).

Can anybody guide me through this?

Answer 1

This message:

Database 'TestTenantDatabase' is already open and can only have one user at a time.

is happening because you have set the database to single user. Usually you would do this kill other connections to the database, and make your connection the only one that can access it. However, if you change your database context some other application could connect in, like Visual Studio, and become the single user, effectively locking you out.

Try this in your setup fixture:

use TestTenantDatabase;
alter database TestTenantDatabase set single_user with rollback immediate;
use master;
drop database TestTenantDatabase;

If you are in the database when you set single user, you will become the single user. Then, if you change to master and then delete the database within the same batch, it should beat anyone trying to connect into it.

Another option is to set offline, instead of single_user, however, when you then delete the database, it will not delete the database .mdf & .ldf (and any .ndf) files, and so you could have issues recreating the database for another set of tests.

If you get errors relating to deadlocks when running single_user, set your deadlock priority to high. You can set it back to normal after the drop, if you are going to reuse the connection.