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.

This article by Jimmy Bogard is a very good read on the subject: [Isolating database data in integration tests](http://lostechies.com/jimmybogard/2012/10/18/isolating-database-data-in-integration-tests/). — Marius Stănescu, Mar 19 '14 at 11:29

rouan · Accepted Answer · 2012-12-19T11:54:36.110

26

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.

edited Dec 19 '12 at 11:54

answered Dec 19 '12 at 11:46

rouan

5,339
6
22
36

1

I would put the DbContext.Database.CreateIfNotExists(); in a method attributed with [AssemblyInitialize] – Elisabeth Jun 17 '15 at 18:46
If I pass a name to the constructor of my DbContext nothing happens. It still uses the default configuration for the database. Why does this work for you and not me? Weird. – Wouter Schut Jun 28 '15 at 13:55
1

Where is the MyDbContext class? What's that look like? – Sonofblip Mar 08 '16 at 17:18
2

@Sonofblip It's been a while, but I suspect I may have created a subclass of DbContext because I needed to override some behaviour. Does it work if you just use DbContext? – rouan Mar 09 '16 at 20:51
Would it not be more difficult to use a database to test with because you need to know the exact state of the records at each test. For example, you don't want to test record A, when you deleted it in a previous test (you must know that was removed/changed or whatever). Maybe moq testing would be better? I'm just starting out unit testing so just enquiring :) – Ian Jun 27 '16 at 15:24
1

@Kleky mocking makes sense for unit tests - and you will probably have several of these. This question is about writing an integration test, though, which you'll also want to have a few of so that you can make sure your code works with a real database too. – rouan Jun 29 '16 at 09:46
Thanks, that's cleared up my understanding. Unit tests shouldn't be doing database stuff, Integration testing should. – Ian Jun 29 '16 at 14:47
Just be careful about unit tests running in parallel. That would potentially unintentionally break some tests. – Issa Fram Aug 30 '18 at 17:10
Thanks for this. What are you doing in the base class with `protected MyDbContext DbContext;`? Is this supposed to reference your new Integration Test database? Obviously `MyDbContext` throws an intellisense error in my project - I've added a connection string for a localdb but don't know how that ties into declaring the `DbContext`. – Kyle Vassella Oct 23 '18 at 18:40
Also, I was wondering how you went about populating your new IntegrationTests database with the data from your original development database. – Kyle Vassella Oct 23 '18 at 20:01
@KyleVassella I'm sorry but I really can't remember how this works, but I imagine `MyDbContext` is some sort of interface that's instantiated in `TestSetup` function. Re importing your development data - it's best not to! I've always found it much more useful to insert some relevant stub data for each test, specific to that test. – rouan Nov 05 '18 at 15:04

score 7 · Answer 2 · edited Sep 25 '15 at 00:12

7

Just setup a connection string in the app.config of your unit test project that points to the new DB instance.

You can then use the initialisation and cleanup methods in your test class to create and delete the DB.

The connection string is just the usual, e.g.

<add name="UnitTestDBConnection" connectionString="Data Source=(local);Initial Catalog=UnitTestDB;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>

Then to create the DB, once per test, you could do:

    YourContext _ctx;

    [TestInitialize]
    public  void Initiaslise()
    {

        YourNameDbInitialise initialiser = new YourNameDbInitialiseForTest();
        Database.SetInitializer(initialiser);

        _ctx = new YourNameContext();

        initialiser.InitializeDatabase(_ctx);         
    }

and this to delete at the end of each test

    [TestCleanup]
    public  void Cleanup()
    {
        Database.Delete("YourName");
    }

edited Sep 25 '15 at 00:12

Fyodor Soikin

78,590
9
125
172

answered Dec 13 '12 at 09:57

Justin Harvey

14,446
2
27
30

Thanks. Can you please share some code in your answer that shows how this is done? How do you create the new DB and how do you make sure that you provide the correct connection string? – rouan Dec 13 '12 at 10:11
Thanks for this. In OP's original question,what is he doing in the base class with `protected MyDbContext DbContext;`? Is this supposed to reference his new Integration Test database? Obviously `MyDbContext` throws an intellisense error in my project - I've added a connection string for a localdb but don't know how that ties into declaring the `DbContext`. – Kyle Vassella Oct 23 '18 at 18:43
Yes, it simply refers to his test Database. – Justin Harvey Oct 24 '18 at 08:20

score 5 · Answer 3 · edited Jun 14 '16 at 09:14

5

If you are using NUnit, you could use Setup/Teardown attribute with TransactionScope to not commit your changest to the database:

[SetUp]
public void SetUp()
{
    transaction = new TransactionScope();
}

[TearDown]
public void TearDown()
{
    if(transaction != null) 
       transaction.Dispose();
}

If you are using some other unit test framework it should have simmilar attributes. I would recomend creating a base class DbItegrationTest for all your integration test fixtures so if you derive from this class all test methods won't do commits to database.

To configure Entity Framework for other database please override db connection string in your test assembly.

edited Jun 14 '16 at 09:14

marc_s

732,580
175
1,330
1,459

answered Dec 13 '12 at 09:59

petro.sidlovskyy

5,075
1
25
29

1

I would prefer to use an entirely different database for my tests. Using the same database means that it will likely contain seed data and may change if the app is in use for some reason while the tests are running. Using a separate database will also have the added benefit of testing that my migrations are working properly. – rouan Dec 13 '12 at 10:14
3

You may use another database for your tests. For this just update connection string in app.config of your tests assembly. Use the same connections string name as in your MVC application but update database name/credentials etc. If your tests assembly doesn't have app.config you may add it manually. – petro.sidlovskyy Dec 13 '12 at 10:17

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

3 Answers3

Linked