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

Knowing the details of the exception would be helpful, such as its type, message, etc. — , Oct 25 '16 at 18:14
Could be a dupe of http://stackoverflow.com/q/193154/1260204 — Igor, Oct 25 '16 at 18:29
If `notif.generateNotifForUser((new Notification(1, 1, pl, 2, 2)));` is causing the error then you need to show us what that method looks like — Omar Himada, Oct 25 '16 at 18:33
Already tried another kind of isolation level? And you have MSDTC running right? — dlcardozo, Oct 25 '16 at 18:33

score 1 · Accepted Answer · answered Oct 25 '16 at 18:38

// 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.

Omar Himada · Answer 2 · 2016-10-25T18:35:09.847

0

EDIT:

It appears that the notification method was causing the error because it was also calling Context.SaveChanges

You only need to call Context.SaveChanges once

I would remove the Context.SaveChanges in the notification method, only calling it once at the end of the transaction scope.

edited Oct 25 '16 at 18:35

answered Oct 25 '16 at 18:29

Omar Himada

2,540
1
14
31

2

"You shouldn't access a Context in a foreach loop like that." **why**? – Oct 25 '16 at 18:30
Well a similar problem arises when you try to enumerate over an IEnumerable in a foreach loop. It attempts to enumerate every time it iterates over the loop. – Omar Himada Oct 25 '16 at 18:32
2

The nice thing about our profession is that code is predictable. "Weird things" do not just happen, there is always a reason why a component or piece of code behaves the way it does. I have yet to encounter a problem with a piece of code that could not be explained in a logical manner. Adding "weird things happen when..." to an answer simply invalidates everything else that comes after it (IMO). – Igor Oct 25 '16 at 18:33
Okay i'll copy and paste my comment in the post to appease people in our profession such as yourself. EDIT: Nevermind. – Omar Himada Oct 25 '16 at 18:34

Using TransactionScope with two DBContext, get error

2 Answers2