Click here to Skip to main content
13,189,324 members (69,101 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


3 bookmarked
Posted 7 Jan 2013

Using SQL Compact for Integration Tests with Entity Framework

, 7 Jan 2013
Rate this:
Please Sign up or sign in to vote.
How to use 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 instances 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.

WithDbContext(x =>
        if (x.Database.Exists())

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");
  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.


  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.


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


About the Author

eric hexter
Chief Technology Officer
United States United States
I am an Architect for 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

You may also be interested in...


Comments and Discussions

QuestionUsing "DELETE FROM" versus "Truncate Table" for faster tests Pin
katrash14-Jan-13 9:39
memberkatrash14-Jan-13 9:39 

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

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

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