Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Using sql compact for integration tests with entity framework.

, 7 Jan 2013 Apache
Rate this:
Please Sign up or sign in to vote.
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 … Continue reading →

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 maintenance 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”);
          });

License

This article, along with any associated source code and files, is licensed under The Apache License, Version 2.0

Share

About the Author

eric hexter
Chief Technology Officer
United States United States
I am an Architect for Dell.com on the Engineering Excellence team. 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 am a Microsoft MVP.
 
I have founded some online conferences like aspConf, mvcConf, Community For MVC
Follow on   Twitter

Comments and Discussions

 
QuestionUsing "DELETE FROM" versus "Truncate Table" for faster tests Pinmemberkatrash14-Jan-13 10:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 7 Jan 2013
Article Copyright 2013 by eric hexter
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid