WF4 TransactionScope containing several custom activities with EF4 database updates

Question

I have created several custom activities that update tables in my DB (in this case SQL Server Compact), using Entity Framework 4 with POCOs.

If I put more than one of these inside a WF4 TransactionScope activity, I'm running into problems: EF disposes the DB connection after the first activity has finished, and when the next DB activity tries to do a DB update a new connection is built up. At this moment an exception is thrown.

System.Activities.WorkflowApplicationAbortedException : The workflow has been aborted.
 ----> System.Data.EntityException : The underlying provider failed on Open.
 ----> System.InvalidOperationException : The connection object can not be enlisted in transaction scope.

Do I have to keep the EF connection open during the whole transaction scope? How can I do that? Create an explicit custom activity for that, or is there a standard way?

My current workaround goes like this: I created a new code activity that creates our ObjectContext and explicitely calls dbContext.Connection.Open(). It returns the ObjectContext, which is then saved in a workflow variable. That one is passed to all the DB related activities as an InArgument<>. Inside my DB activities, I use this ObjectContext if it is passed in, otherwise I create a new one.

This does work, but I'm not satisfied with this solution: It needs the new InArgument for every DB related activity. In the workflow designer, I have to insert that special OpenDatabaseConnection activity inside the transaction scope, and then make sure that the correct variable is passed into all DB activities. This seems to be very inelegant and error prone, especially if other team members have to use these DB activities.

What would be a better way to handle this?

Answer 1

The problem is that when you open a second connection in the same transaction scope, an attempt is made to promote the transaction to a distributed transaction (even though there's nothing distributed about it since you connect to the same database). SQL Server CE doesn't support this scenario.

What I would do is create a custom 'container' activity that opens (and closes) the connection and makes it available to child activities. This is still not optimal but at least you no longer need to pass InArgument's around. You get the following activity tree:

TransactionScope
    InitializeConnection
        Sequence
            CustomDataActivity1
            CustomDataActivity2
            CustomDataActivity3

InitializeConnection is a NativeActivity that uses NativeActivityContext.Properties to expose the connection (or the ObjectContext) to child activities.

Make sure you implement proper error handling to ensure you close the connection at all times.

NOTE: Distributed transactions are supported by the full SQL Server only through a Windows service called MSDTC (Microsoft Distributed Transaction Coordinator). You can find this one in your 'Local Services'. Since SQL Server CE is a database that should be able to operate completely standalone, it makes sense that it has no dependency on MSDTC. Therefore it has no support for distributed transactions.