Using TransactionScope with two DBContext, get error

Question

I have a function which uses TransactionScope. When i use two dbContext, it crashes. The error message is:

System.Data.EntityException: Error underlying provider Open. ---> System.Transactions.TransactionException: The operation is not valid for the state of the transaction. in System.Transactions.TransactionState.EnlistPromotableSinglePhase (InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction AtomicTransaction, Guid promoterType) in System.Transactions.Transaction.EnlistPromotableSinglePhase (IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Guid promoterType) in System.Transactions.Transaction.EnlistPromotableSinglePhase (IPromotableSinglePhaseNotification promotableSinglePhaseNotification) in System .Data.SqlClient.SqlInternalConnection.EnlistNonNull (Transaction tx) in System.Data.SqlClient.SqlInternalConnection.Enlist (Transaction tx)
in System.Data.ProviderBase.DbConnectionInternal.ActivateConnection (Transaction transaction) in System.Data.ProviderBase.DbConnectionPool.PrepareConnection (DbConnection owningObject, DbConnectionInternal obj, Transaction transaction) in System.Data.ProviderBase.DbConnectionPool.TryGetConnection (DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout , allowCreate Boolean, Boolean onlyOneCheckConnection, DbConnectionOptions UserOptions, DbConnectionInternal & connection) in System.Data.ProviderBase.DbConnectionPool.TryGetConnection (DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions
  UserOptions, DbConnectionInternal & connection) in
  System.Data.ProviderBase.DbConnectionFactory.TryGetConnection (DbConnection
  owningConnection, TaskCompletionSource1 retry, DbConnectionOptions UserOptions, DbConnectionInternal oldConnection, DbConnectionInternal & connection) in System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal (DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions UserOptions) in
  System.Data.SqlClient.SqlConnection.TryOpenInner (TaskCompletionSource1 retry) in System.Data.SqlClient.SqlConnection.TryOpen (TaskCompletionSource`1 retry) in System.Data.SqlClient.SqlConnection.Open () in System.Data.EntityClient.EntityConnection. OpenStoreConnectionIf (Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String ExceptionCode, attemptedOperation String, Boolean & closeStoreConnectionOnFailure)

Code

public void example(int c, IEnumerable<int> pi, string comm)
{
    using (var transactionScope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead }))
    {
        var idsList = pi.ToList();
        foreach (var playlist in Context.pl.Where(p => idsList.Contains(p.pid) && p.c== c&& p.stat== 1))
        {
            playlist.TimestampAuthorized = null;

            var pl = "sss";
            notif.generateNotifForUser((new Notification(1, 1, pl, 2, 2)));
        }
        Context.SaveChanges();
        transactionScope.Complete();
    }
}

The problem occurs in the Context.SaveChanges(); If I comment the line notif.generateNotifForUser(); it works. Into that function, I also do context.saveChanges();

Thanks

Answer 1

// code before unchanged
foreach (var playlist in Context.pl.Where(p => idsList.Contains(p.pid) && p.c== c&& p.stat== 1).ToList()) // added ToList
// code after unchanged

The issue has to do with the fact that you are trying to use an open connection for 2 things. Iterating the pl DbSet results and whatever you are doing in generateNotifForUser which is where you are probably getting/doing something else from the DbContext (based on your update you are making a SaveChanges call). To fix this add ToList at the end of your Lamda statement to force the query results to be materialized in full immediately before the iteration starts. Then the connection will no longer be open allowing you to continue to use the DbContext in other parts of your code. This also assumes you are not making similar mistakes further down in the method chain.