How should I set up my integration tests to use a test database with Entity Framework?

Question

I am writing integration tests for an application and have not been able to find any best practices on how to set up a test database for my integration suite. I am working on an ASP.NET MVC4 application using Entity Framework code-first.

I can confirm that the tests in my test project talk to the local development database on my machine by default. This is not ideal, as I want to have a fresh database every time I run the tests.

How can I set up my test project so that my tests talk to a separate instance? I'm assuming that it is possible to set up an SQL Server Compact Edition instance, but I'm not sure how to configure this.

Answer 1

Thanks so much to @Justin and @Petro for your answers, which have helped me immensely. The solution I have come up with is a combination of the techniques you suggested. The solution described below provides a new database for each run of the tests, and a separate transaction for each test.

I added a connection string for my test database in the App.config of my Test project:

  <connectionStrings>     <add name ="TestDatabase"      providerName="System.Data.SqlClient"      connectionString="Data Source=(LocalDb)\v11.0;Database=TestDatabase;Integrated Security=True"/>   </connectionStrings> 

I created a base class for my integration tests, to provide setup and teardown. Setup instantiates the context, creates the DB if it doesn't exist yet and starts a transaction. Teardown rolls back the transaction.

public class EntityFrameworkIntegrationTest {     protected MyDbContext DbContext;      protected TransactionScope TransactionScope;      [TestInitialize]     public void TestSetup()     {         DbContext = new MyDbContext(TestInit.TestDatabaseName);         DbContext.Database.CreateIfNotExists();         TransactionScope = new TransactionScope(TransactionScopeOption.RequiresNew);     }      [TestCleanup]     public void TestCleanup()     {         TransactionScope.Dispose();     } } 

Finally, I have a class that takes care of deleting the database after all the tests have run:

[TestClass] public static class TestInit {     // Maps to connection string in App.config     public const string TestDatabaseName = "TestDatabase";      [AssemblyCleanup]     public static void AssemblyCleanup()     {         Database.Delete(TestDatabaseName);     } } 

I should add that I found this blog post about Entity Framework useful for a deeper understanding of what Entity Framework is doing under the hood / by convention.