Data Integration Tests and Transactions - Part 2

2014-05-25_data_integration_tests_and_multiple_data_framework_transactions

Last time I talked about how to use the TransactionScope class to handle the rollback of any changes made during a data integration test. This time, I would like to talk about another issue that will eventually come up using transactions: using Entity Framework code-first in combination with any other data access framework while leveraging TransactionScope.

In our case, we use Dapper to insert data before our tests and to assert things about the state of the database after exercising the code-first Entity Framework data layer functionality we are testing. Below is our example.

private TransactionScope _scope;

[SetUp]
public void SetUp()
{
    _scope = new TransactionScope();
}

[TearDown]
public void TearDown()
{
    _scope.Dispose();
}

[Test]
public void test_to_demo_ef_and_dapper_connections()
{
    var person = new Person {FirstName = "Todd", LastName = "Meinershagen"};
    var sql = "INSERT INTO dbo.Persons (FirstName, LastName) ";
    sql = sql + "VALUES (@FirstName, @LastName)";

    using (var connection = new SqlConnection("a connection string"))
    {
        connection.Execute(sql, person);
    }

    var db = new PersonContext();
    var matchingPersons = 
        from p in db.Persons
        where
            (p.FirstName == person.FirstName) &&
            (p.LastName == person.LastName)
        select p;

    matchingPersons.FirstOrDefault().Should().NotBeNull();
}

Both Dapper and EF establish their own connections, and we would expect that each connection would take part in the ambient transaction being created during the [SetUp] of our test fixture.

Normally, this is not an issue, but when the tests are run, we get a message similar to below:

MSDTC on server 'servername' is unavailable.

This can occur for any number of reasons such as the following:

  • Opening multiple connections with same connection string to SQL Server 2005.
  • Opening multiple nested connections with same connection string to SQL Server 2008.
  • Opening multiple connection to two different SQL Server 2008 instances.

In the case of our tests, we are making two connections (one for EF and one for Dapper) to SQL Server 2008 using the same connection string. Based on the guidance above, this should not force our system to escalate to MSDTC.

So, what is happening here?

After searching diligently on the internet (thank God for the internet!), I found an article explaining that Microsoft cleverly adds information to a code-first connection string to allow Microsoft to collect statistics from those using Azure and Entity Framework to determine what percentage use code-first as opposed to database-first. (Why Microsoft, why?) This is supposed to have been fixed in EF 6.0.

So, instead of using a connection string as you specified:

Data Source=(local);
Initial catalog=LocalDb;
Integrated Security=True;

The system uses the following for EF:

Data Source=(local);
Initial catalog=LocalDb;
Integrated Security=True;
Application Name=EntityFrameworkMUE

So, what does this mean?

Unfortunately, this causes our system to see the two connections (Dapper and EF) as two different connection strings and therefore, it looks like you are connecting to two different data sources which escalate to MSDTC. What a pain!

So, how do we get around this issue.

One way would be to use EF for both production and test code, although this robs us of the benefit of quickly setting up data using a light-weight framework like Dapper. Another option is to modify our test project’s configuration file by explicitly specifying the Application Name for our connection string. The system will then see the two connections as the same. No more escalation to MSDTC!

Hope this helps.

Data Integration Tests and Transactions - Part 1

2014-05-25_integration_tests_and_transactions

I have been writing some integration tests in .NET lately to specify behavior for my data layer. The issue that always comes up is how to make sure that each test is completely isolated from other tests. This requires each test to initialize needed data and at the end to clean up any data that was created so that other tests are not impacted by it. In the past I have set up compensating queries to delete that same data on tear down.

This can create one of two problems:

  • maintainability - it is hard to maintain this logic going forward
  • reliability - it doesn’t guarantee successful rollback of the initial inserts because the query could possibly fail leaving the tests in a position for unsuccessful future tests against the database

So, what do you do?

I have been using the handy TransactionScope class to allow any connections to participate in the ambient transaction and then dispose of the transaction without committing on tear down of the fixture.

In the example below, I have used NUnit, but this could work with other testing frameworks quite well.

[TestFixture]
public class MyFixture
{
    private TransactionScope _scope;

    [SetUp]
    public void SetUp()
    {
        _scope = new TransactionScope();
    }

    [TearDown]
    public void TearDown()
    {
        _scope.Dispose();
    }

    ///<summary>This is a silly sample test for display purposes only.</summary>
    [Test]
    public void given_context_when_something_happens_should_have_expected_outcome()
    {
        ExecuteSomeLogicForInsertingDataForContext();

        RunSomeActionToMakeSomethingHappen();

        AssertThatSomeExpectedOutcomeOccured();
    }
}

You could make this an abstract base class and make the SetUp and TearDown methods virtual if you would like to reuse this across any of your data test fixtures. As long as you don’t call _scope.Complete() the changes you have made should be rolled back/aborted on the disposal of the transaction.

Hope this helps!