How can I use Sql CE 4 databases for functional tests

Question

Due to the potential differences between Linq-to-Entities (EF4) and Linq-to-Objects, I need to use an actual database to make sure my query classes retrieve data from EF correctly. Sql CE 4 seems to be the perfect tool for this however I have run into a few hiccups. These tests are using MsTest.

The problem I have is if the database doesn't get recreated (due to model changes), data keeps getting added to the database after each test with nothing getting rid of the data. This can potentially cause conflicts in tests, with more data being returned by queries than intended.

My first idea was to initialize a TransactionScope in the TestInitialize method, and dispose the transaction in TestCleanup. Unfortunately, Sql CE4 does not support transactions.

My next idea was to delete the database in TestCleanup via a File.Delete() call. Unfortunately, this seems to not work after the first test is run, as the first test's TestCleanup seems to delete the database, but every test after the first does not seem to re-create the database, and thus it gives an error that the database file is not found.

I attempted to change TestInitialize and TestCleanup tags to ClassInitialize and ClassCleanup for my testing class, but that errored with a NullReferenceException due to the test running prior to ClassInitialize (or so it appears. ClassInitialize is in the base class so maybe that's causing it).

I have run out of ways to effectively use Sql CE4 for testing. Does anyone have any better ideas?

---

Edit: I ended up figuring out a solution. In my EF unit test base class I initiate a new instance of my data context and then call context.Database.Delete() and context.Database.Create(). The unit tests run a tad slower, but now I can unit test effectively using a real database

---

Final Edit: After some emails back and forth with Microsoft, it turns out that TransactionScopes are now allowed in SqlCE with the latest release of SqlCE. However, if you are using EF4 there are some limitations in that you must explicitly open the database connection prior to starting the transaction. The following code shows a sample on how to successfully use Sql CE for unit/functional testing:

    [TestMethod]
    public void My_SqlCeScenario ()
    {
        using (var context = new MySQLCeModelContext()) //ß derived from DbContext
        {
            ObjectContext objctx = ((IObjectContextAdapter)context).ObjectContext;
            objctx.Connection.Open(); //ß Open your connection explicitly
            using (TransactionScope tx = new TransactionScope())
            {

                var product = new Product() { Name = "Vegemite" };
                context.Products.Add(product);
                context.SaveChanges();
            }
            objctx.Connection.Close(); //ß close it when done!
        }
    }

Answer 1

In your TestInitialize you should do the following:

System.Data.Entity.Database.DbDatabase.SetInitializer<YourEntityFrameworkClass>(
    new System.Data.Entity.Database.DropCreateDatabaseAlways<YourEntityFrameworkClass>());

This will cause entity framework to always recreate the database whenever the test is run.

Incidentally you can create an alternative class that inherits from DropCreateDatabaseAlways. This will allow you to seed your database with set data each time.

public class DataContextInitializer : DropCreateDatabaseAlways<YourEntityFrameworkClass> {
    protected override void Seed(DataContext context) {
        context.Users.Add(new User() { Name = "Test User 1", Email = "test@test.com" });
        context.SaveChanges();
    }
}

Then in your Initialize you would change the call to:

System.Data.Entity.Database.DbDatabase.SetInitializer<YourEntityFrameworkClass>(
    new DataContextInitializer());

Answer 2

I found the approach in the "final edit" works for me as well. However, it's REALLY annoying. It's not just for testing, but any time you want to use TransactionScope with Entity Framework and SQL CE. I want to code once and have my app support both SQL Server and SQL CE, but anywhere I use transactions I have to do this. Surely the Entity Framework team should have handled this for us!

In the meantime, I took it one step farther to make it a little cleaner in my code. Add this block to your data context (whatever class you derive from DbContext):

public MyDataContext()
{
    this.Connection.Open();
}

protected override void Dispose(bool disposing)
{
    if (this.Connection.State == ConnectionState.Open)
        this.Connection.Close();

    base.Dispose(disposing);
}

private DbConnection Connection
{
    get
    {
        var objectContextAdapter = (IObjectContextAdapter) this;
        return objectContextAdapter.ObjectContext.Connection;
    }
}

This makes it a lot cleaner when you actually use it:

using (var db = new MyDataContext())
{
    using (var ts = new TransactionScope())
    {
        // whatever you need to do

        db.SaveChanges();
        ts.Complete();
    }
}

Although I suppose that if you design your app such that all changes are committed in a single call to SaveChanges(), then the implicit transaction would be good enough. For the testing scenario, we want to roll everything back instead of calling ts.Complete(), so it's certainly required there. I'm sure there are other scenarios where we need the transaction scope available. It's a shame it isn't supported directly by EF/SQLCE.