none
How to add transaction to Entity Framework 4 SaveChanges and SqlConnection RRS feed

  • Question

  • Hi, Have a problem.  We are trying to phase out Entity Framework from our project for various reason.  Anyway, the current implementation uses Entity Framework 4 for saving various tables. We added additional methods for other saves using SqlConnection OUTSIDE of the EF data context.

    I attempted to wrap all of these CRUD methods within a using block for a TransactionScope.

     Using scope As New TransactionScope

    .

    .

      ctx.SaveChanges()

    if isNew then

     Using vda As New SomeDataLayerDAExample
           vda.SaveSomethingExample(originalObjectExample)
     End Using

    end if

    end using

    When using the TransactionScope I am getting the error below. 

    Anyone have any ideas how to wrap a Entity Framework 4 SaveChanges within a transaction with SqlConnection methods?

    The underlying provider failed on Open.---System.Data.Entity----System.Data.EntityException: The underlying provider failed on Open. ---> System.Transactions.TransactionException: The operation is not valid for the state of the transaction.
       at System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction, Guid promoterType)
       at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Guid promoterType)
       at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
       at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
       at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
       at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
       at System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, 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, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.Open()
       at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
       --- End of inner exception stack trace ---
       at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
       at System.Data.EntityClient.EntityConnection.Open()
       at System.Data.Objects.ObjectContext.EnsureConnection()
       at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)

    Monday, September 30, 2019 10:49 PM

All replies

  • Maybe you are trying to wrap the default trasaction in EF inside another transaction. 

    From the Microsoft documentation:

    In all versions of Entity Framework, whenever you execute SaveChanges() to insert, update or delete on the database the framework will wrap that operation in a transaction. This transaction lasts only long enough to execute the operation and then completes. When you execute another such operation a new transaction is started.


    Success
    Cor


    • Edited by Cor Ligthert Monday, September 30, 2019 11:00 PM
    Monday, September 30, 2019 10:59 PM
  • Perhaps this might work for you 

    Using context = New SomeContext()
       ctx.Database.Connection.Open()
       Using ts As TransactionScope = ctx.Connection.BeginTransaction()
       Try
          ctx.SaveChanges()
          ts.Commit()
        Catch
          ts.Rollback()
        End Try
       End Using
    End Using
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, September 30, 2019 11:20 PM
  • EF issues can be discussed at the EF forum.

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework

    Tuesday, October 1, 2019 8:00 AM