Using sql compact for integration tests with entity framework.

In my practices using continuous integration, I try to achieve 100% code coverage using integration tests. This is a separate metric from my unit tests, think of these tests as verifying all of my infrastructure code works properly when wired up to data access or other out of process assets like databases or services.  While it is easy to setup sql server on a build server, I have run into instance where organizations using shared build servers do not allow access to create and drop databases as part of the CI process.  A simple way to work around this is to use sql compact in process in the integration test suite. This also gives you an advantage on developer workstations to isolate your integration test data access from your development instance if you are running an End to End application on your workstation, which you should be.

I have run into a number of issues getting the SQL CE working in a unit test project(class library). Here are my notes of how to get it working.

  1. install the following nuget packages:
    1. EntityFramework.SqlServerCompact
    2. Microsoft.SqlServer.Compact
    3. SqlServerCompact.IntegrationTestConfiguration – this is a package I created to quickly add the provider configuration into an app.config file.
  2. Add the native dlls to the integration test project, set the Copy to Output Directory to Always. See how to do this in VS2012 in the screenshot below.
    image
  3. The item is to use a test setup method to remove the data in the database.  There are two ways to accomplish this. Both of these methods help ensure your tests will be isolated from each other in terms of data setup and will not try to reuse test data from one test to the next.
    First you can delete the entire sql compact database file. The downside to doing this is that the tests will run slower since it will recreate the database for each test.  The advantage to this approach, is that as you add new entities to your model, you do not have to update this method in order to keep your test suite clean.
  4. WithDbContext(x =>
                    {
                        if (x.Database.Exists())
                            x.Database.Delete();
                        x.Database.CreateIfNotExists();
                    });

    The second approach is to run a set of delete statements for each table in the test setup. This is faster because the Entity Framework does not need to recreate the entire file. The downside of this is maintance for your tests. Every time you add a new entity to the ORM you need to add a new line to this setup function.

    WithDbContext(x =>
                    {
                        x.Database.ExecuteSqlCommand(“delete from Users”);
                        x.Database.ExecuteSqlCommand(“delete from ShoppingCarts”);
                        x.Database.ExecuteSqlCommand(“delete from Products”);
                    });

Related Articles:

Post Footer automatically generated by Add Post Footer Plugin for wordpress.

About Eric Hexter

I am the CTO for QuarterSpot. I (co)Founded MvcContrib, Should, Solution Factory, and Pstrami open source projects. I have co-authored MVC 2 in Action, MVC3 in Action, and MVC 4 in Action. I co-founded online events like mvcConf, aspConf, and Community for MVC. I am also a Microsoft MVP in ASP.Net.
This entry was posted in .Net, Asp.Net MVC, CodeProject, EntityFramework, Tools, Unittests. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Corey Kaylor

    Maybe using something like this might help out your maintenance?

    SELECT * FROM INFORMATION_SCHEMA.TABLES

    • erichexter

      You bring up a great point. But once you consider foreign keys the ordering of the delete statements gets complex pretty quickly. I had a solution to this, that was developed at Headspring, when I was working there.. Let me find that and update the post with that information.

    • Chris B

      I’ve always like what NH does for this: session.Delete(“from system.object”). I’m not sure, but  I think it’ll work out all the foreign keys in most cases (not sure what happens if you have a cycle, my guess is kapowie).

    • erichexter

      That works for simple schemes but once you add foreign key references between tables, that approach breaks down pretty quickly.

      Eric Hexter

      blog | http://Hex.LosTechies.com
      info | http://www.linkedin.com/in/erichexter

  • PeteB

    I thought this might be of interest http://petebarber.blogspot.co.uk/2012/08/integration-testing-with-nunit-and.html

  • Malcom Frexner

    This is my solution for it in a stored procedure. It should work the same in EF

    At the start:

    EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’EXEC sp_MSForEachTable ‘IF OBJECTPROPERTY(object_id(”?”), ”TableHasForeignRef”) = 1DELETE FROM ?elseTRUNCATE TABLE ?’At the end:EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’

    Malcomn Frexner

  • Iain

    I’ve taken a slightly different approach by wrapping each unit test in a transaction (in the initialize/cleanup methods).  This works well, though you need to be careful about the order of creating the Context and creating the TransactionScope.

  • Stephen

    I was pointed to this post and it seems to cover exactly what I want to achieve which is an in-process version of SQL-CE I can run Integration Tests against with EF. The post however doesn’t cover the basics, i’m new to Integration testing with SQL-CE so I’m a little stuck on how to get started (after installing the nuget packages). Can anyone point me in the direction of a helpful “getting started” post? Thanks

  • Masoud Sanaei

    When I want add the native libraroes to my test project reference I get following error:
    —————————

    Microsoft Visual Studio

    —————————

    A reference to ‘C:Program FilesMicrosoft SQL Server Compact Editionv4.0Privatex86sqlceme40.dll’ could not be added. Please make sure that the file is accessible, and that it is a valid assembly or COM component.

    —————————

    OK

    —————————

    where is the problem do you think?