How to perform updates in entity framework 3.5 within an SqlTransaction

Question

I'm working on a plugin that gets loaded via IOC from a 3rd party data driver. The plugin and data driver both operate on the same SQL Server 2005 database, but the plugin focuses on a small subset of tables that have foreign key relationships to the tables that the data driver manages.

My problem is that in some operations, the data driver is creating an SQLTransaction around calls to my plugin, which is causing my operations to fail (timeout exception). A typical scenario follows this path of pseudo-code / code:

DataDriver.InsertEntity(IBusinessObject businessObject)
{
    CreateSqlTransaction();
    AddEntityToEntityTable(businessObject);
    Plugin.PersistAdditionalData(businessObject);
    CommitOrRollbackTransaction();
}

In my plugin, I'm doing something like this:

Plugin.PersistAdditionalData(IBusinessObject businessObject)
{
    var dbObject = GetObjectFromDatabase(businessObject);  // Via Entity Framework
    var additionalData = CreateAdditionalDataObject(businessObject);

    // Insert data to a table with a foreign key relationship to a table that the
    // data driver has just inserted the business object data to in the current
    // transaction.
    _entityModel.AddToObject_AdditionalData_AssociationSet(additionalData);
    _entityModel.SaveChanges();        
}

I've tried wrapping my method in a using(TransactionScope){...} block, but haven't been able to get that to work. It did move the failure from the read (GetObjectFromDatabase) to the save however.

I don't have access to the data driver code or to any notifications that the transaction has been committed or rolled back. It may be possible for me to get the developer to make some changes if I know what to suggest though, but I would prefer a solution that doesn't require that.

Answer 1

This is only guessing...

The data driver is using a different sql connection than your plugin. So your code runs in a different transaction (or without one), and is conflicting with the uncommited changes from the data driver, resulting in a dead lock.

Solution ideas:

• Make your code use the same connection as the data driver. This would require that the connection is somehow provided to the plugins. And you'd need a way to make the entity model use it (never worked with the entity model my self). This would be the nicest solution. You stuff would automatically participate in the transaction, with all the nice consequences like consistency and such.
• Try to ensure that both connections run in the same transaction. This would theoretically be possible using System.Transactions. This also requires adapting the data driver to use the System.Transactions model. I doubt that the two connections would see each others changes that way, so this probably won't work.
• Don't use transactions. You might be able to instruct the database server to ignore transactions, so you don't need changes to the data driver. This would work. Of course transactions were invented for a reason...

I would go for the first one. Figure out how you can tell the entity model to use a particular sql connection and then ask the data driver developers to give it to you. This might even result in a better performance since fewer connections are required.