Managing SpecFlow acceptance test data with Entity Framework

A look into how Entity Framework can be used to simplify the management of data for your acceptance test scenarios.

In a previous set of posts I looked at how it is possible to use SpecFlow and Selenium to create automated acceptance tests driven via the browser.

An important part of any acceptance or integration test is the test data. It is important that the data for each test is isolated from any other test. Often these tests require complicated data setup which can add additional complication and maintenance to to the test suite. This post assumes that the system under test is using a SQL database.

Traditional ways to manage test data

There are a few ways to manage the test data for acceptance tests. Probably the most widely used are:

  • Run some SQL scripts as part of the test setup and tear-down – Can be effective but means extra maintenance of the scripts is required. This type of script can be prone to errors as they are built outside of the type safety that the rest of the system benefits from.  This approach also requires a mechanism to run the scripts when the tests run.
  • Use a standard set of data for all tests – the main issue with this approach is that it may not allow for proper isolation of the test data. It is possible to use transactions or some similar mechanism to allow for this, but sometimes it is only the order of the running tests that ensures the correct data is available for the tests.

There are of course other ways, and many variations on the theme.

A better approach..?

It would be better if it were possible to add the data as part of the test setup in a type-safe way, using the language of the system without the need for external scripts.

If you are using an ORM in your application, it is very likely that you already have the tools needed to do just this. In this post I am going to use Entity Framework 5, but you could just as easily use nHibernate or something else to get the same result.

An example

Here is the SpecFlow scenario we are going to automate.

Scenario: Search for events in a region and display the results
	Given that the following regions exist
	| Id | Name   |
	| 1  | North  |
	| 2  | South  |
	| 3  | London |
	And the following events exist
	| Code    | Name                              | Description                                   | Region Id |
	| CR/1001 | Crochet for Beginners             | A gentle introduction into the art of crochet | 1         |
	| CH/3001 | Cat Herding                       | A starter session for the uninitiated         | 3         |
	| CH/3002 | Cat Herding - Advanced techniques | Taking it to the next level                   | 3         |
	When I navigate to the event search page
	And I select the "London" region 
	And perform a search
	Then The search results page is displayed
	And the following results are displayed
	| Code    | Name                              | Description                           | Region |
	| CH/3001 | Cat Herding                       | A starter session for the uninitiated | North  |
	| CH/3002 | Cat Herding - Advanced techniques | Taking it to the next level           | North  |

It is only the first two steps of the scenario that we are interested in for this post. Even before we start satisfying the first step we should make sure that the database is in the correct state to start adding the data for the test.

To do this we can use a BeforeScenario step. As the name suggests, this step is called before each scenario is run. You will notice that our scenario had a tag of @eventSearch. The same tag will be used by the BeforeScenario attribute to ensure that the BeforeScenario method is only triggered by scenarios that have the @eventSearch tag. This becomes important when you have multiple feature files in the same project.

public void Setup()
    using (var context = new CrazyEventsContext())
        context.Database.ExecuteSqlCommand("DELETE FROM Events DBCC CHECKIDENT ('CrazyEvents.dbo.Events',RESEED, 0)");
        context.Database.ExecuteSqlCommand("DELETE FROM Regions DBCC CHECKIDENT ('CrazyEvents.dbo.Events',RESEED, 0)");

We are using the ExecuteSqlCommand method to clear the contents of the tables and reseed the identity columns of the tables. If the tables are not used as part of a foreign key relationship it is possible to use TRUNCATE TABLE.

The next step is to use Entity Framework to populate the region and event details from the SpecFlow table. Although I have these steps as part of a scenario, you could also use a Background step to have the data populated at the start of each scenario.

[Given(@"that the following regions exist")]
public void GivenThatTheFollowingRegionsExist(Table table)
    var regions = table.CreateSet<Region>();
    using (var context = new CrazyEventsContext())
        foreach (var region in regions)
[Given(@"the following events exist")]
public void GivenTheFollowingEventsExist(Table table)
    var events = table.CreateSet<Event>();
    using (var context = new CrazyEventsContext())
        foreach (var crazyEvent in events)

SpecFlow has some table helper extension methods. I am using table.CreateSet(T) to create an IEnumerable of the specified type T from the data in the table. For this to work your table column names need to be the same as the properties of your entity (you can use spaces for readability if you like).

After I have the list of entities, I simply use the Entity Framework context to add each entity to the context in turn. Remember to call SaveChanges() to perform the update.

And that is all there is to it. The nice thing about this method of managing test data is that the data required for the test is specified as part of the test itself. Everyone can see what data is expected to be present when the test is run.


7 Responses to Managing SpecFlow acceptance test data with Entity Framework

  1. I’m not familiar with specflow, but if you’re using code first, why would a DropCreateDatabaseAlways database initializer with data set up in the Seed method not work in this scenario? That’s how I handle my integration tests for entity framework with regular testing (not specflow). Possibly there’s a big difference between what I’m doing and what you are trying to accomplish and how specflow changes the situation.

    • jheppinstall says:

      Hi Julie, thanks for your comment.

      When we push to our QA environment from TeamCity for manual testing we do use the Seed method to populate the reference data when we run the EF migrations.

      Our SpecFlow tests are part of our automated suite of tests. They mostly use Selenium to test the behaviour of the sites. Dropping and recreating the database for each test (currently running into the hundreds) would add a large overhead in time, especially as the database and associated reference data grows.

      Another reason this approach works well with SpecFlow is that the Scenarios are viewed by all the project team, not just developers and the tests are very explicit as to the data requirements for the feature under test. Our analysts and testers are also involved in the writing of the scenarios.

      When test data is set up this way it can be targeted to the feature been tested. If only certain reference data items are needed then it is not necessary to add all the reference data for the system.

  2. Pingback: Getting started with automated acceptance tests in an Agile project | James Heppinstall: On Development

  3. Mark Jones says:

    I like your solution, it is almost good enough, and I will no doubt use it very soon, however my current issue is I don’t have a context. What if you have a new MVC 4 app and you are working agile and lean, and have not yet needed a database. However, you have used the built-in account controller and its use of the websecurity class to handle simple user login. So you have got a DB, and it even uses EF. But you have not gone to the trouble of creating a data context. In this scenario, how can you leverage the same ideas, only with no context to access the DB? Can you get the same solution to work with only the account controller and the WebSecurity Class? I realy need to clear down a user account.

    • jheppinstall says:

      Hi Mark,

      I am not sure about how you would get the same effect using just the account controller, but there are other ways to do it. You could just use some plain old Ado.Net to tidy up the DB before the tests until you get a context in place.

      However working in an agile way does not preclude you from creating a context. You will be familiar with differing commitment until the last possible moment, but I guess you could have reached that point. After all, your test code should be treated as first class code in your application.

  4. Andrew Poulter says:

    Hi James,
    thanks for your post. I am attempting something very similar using SpecFlow to write integration tests with EF and a Sql Server database. I use the BeforeScenario hook to create a new context for each scenario, resetting and reseeding the database. This works fine when I run each test (scenario) on its own but when I run a batch of tests (all scenarios contained in a feature file) then the context creation and reseeding of the database does not work as it should. The data created in one scenario is not deleted before the next one runs and so I have issues with duplicates and so on. One particularly striking case: I have defined one scenario to use a transaction, create a new entity and then call rollback on the context and and check that the created entity is not present in the database. This works fine when only this scenario is tested. But as part of the suite of tests in the feature file, at the end of the batch I find that the entity created in the rollback scenario has been persisted to the database!

    Would you have any idea of what is responsible for this behaviour in SpecFlow? If I cannot reliably reset the context for each scenario then I must look for an alternative approach.
    Thanks in advance for your thoughts and any pointers you can give.


  5. jheppinstall says:

    Hi Andy,

    This sounds like it could be something to do with the way your context is created and disposed. When I am working with context in the tests I tend to use a short lived context within a using statement to ensure that the context lifetime is controlled.

    I appreciate that this may not be possible for you if you require a context across scenarios and I don’t see any immediate problem with creating a shared context across the feature. It could be something as simple as the location that you are calling SaveChanges().

    I would be happy to take a look at the feature file to investigate further. I have email you with my contact details.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: