Use a SqlConnection inside an Entity Framework transaction

3

I have an EF6 project that uses some legacy library (I can't modify this library). The code is like these in my project:

Using scope As New TransactionScope()
   //Many DAOs calls that work just nice
   //The call to the legacy library that fails
End Using

The code is like these in the library:

Using connection As new Sqlconnection("bla")
   connection.Open()  //THIS LINE FAILS INTERMITTENTLY
   //more code
End Using

I have moved the isolation level and the scope of the transaction of the EF project but I have not luck, it still happens. Is valid opening that connection manually inside a Transaction of EF?

The exceptions that I get are:

The operation is not valid for the state of the transaction

or

Communication with the underlying transaction manager has failed.

2014-10-11 13:49:16,736 ERROR[DataAccess.Services]: Timbrar: The operation is not valid for the state of the transaction. InnerException: The MSDTC transaction manager was unable to pull the transaction from the source transaction manager due to communication problems. Possible causes are: a firewall is present and it doesn't have an exception for the MSDTC process, the two machines cannot find each other by their NetBIOS names, or the support for network transactions is not enabled for one of the two transaction managers. (Exception from HRESULT: 0x8004D02B) 2014-10-11 13:49:16,767 ERROR[DataAccess.Services]: at System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction) at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification) at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx) at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at GuardarBitacora(String sCnn, Int64 CtePrincipalID, String NombreArchivos, String numUUID, String Proveedor, Int32 EstatusOper, String MensajeFolio, String MensajeErrorMetodo) at CoreGenWS(String pstrXml, String usuario, String rfc, String password, Boolean sello, Boolean test) at GenArchivoWS(String usuario, String rfc, String password, Byte[] xml, Boolean sello, Boolean test) at Func(String usuario, String rfc, Int64 pCtePrincipalId, Boolean pftest, Byte[] xml, String pwdFac) at Services.Func(RazonSocial razonSocial, Byte[] xmlValido) at Services.(Boolean esBorrador, Int64 usuarioId, , encabezado, , List1 detalles, Func1 , Func3 , Func1 , Func`2 )

I have done a double check of the msdtc service. Is corretly configured in both servers and stragenly, this happends only sometimes when the system have some load.

.net
sql-server
vb.net
entity-framework
transactions
asked on Stack Overflow Oct 11, 2014 by rsan • edited Oct 12, 2014 by Masoud

1 Answer

1

I found the solution (more or less). The problem was that EntityFramework wasn't able to enlist the second connection in the same transaction and escalated it to a distributed transaction managed by the MSDTC service.

This was happening because even when all the connections were made to the same database some connections were made using a different connection string. The solution was using the same connection string for all connections and setting the Application Name property in this connection string. After that I was able to disable the MSDTC service and the app is able to work without exceptions always. Even when we have heavy load in the system.

Here is the savior post: http://joeknowsdotnet.wordpress.com/2012/07/19/entity-framework-msdtc-gotchya/

My app is working propertly and life is happy but I still dont know why when using shared transactions MSDTC fails sometimes....

answered on Stack Overflow Oct 13, 2014 by rsan

User contributions licensed under CC BY-SA 3.0