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
      • Microsoft.SqlServer.Compact
        • SqlServerCompact.IntegrationTestConfiguration – this is a package I created to quickly add the provider configuration into an app.config file.</ol>
        • 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.
          
          • 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. 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”);
                          });</ol>