Can't get transaction rollback working for Entity Frameworks 6

FrancineTaylor
FrancineTaylor used Ask the Experts™
on
Here's my test code:

    Protected Sub TestEntityTransactions()
        Dim db As New ProdEntities()

        ' Testing the save changes not attached to a transaction
        Dim p As Person = db.People.FirstOrDefault(Function(px) px.PersonID = 8551)

        p.PersonCustom1 = "First;"
        db.SaveChanges()

        Dim transaction As System.Data.Entity.DbContextTransaction

        ' do a commit to the transaction
        transaction = db.Database.BeginTransaction()
        p = db.People.FirstOrDefault(Function(px) px.PersonID = 8551)
        p.PersonCustom1 = p.PersonCustom1 & "second;"
        db.SaveChanges()
        transaction.Commit()
        transaction.Dispose()

        ' do a rollback to the transaction
        transaction = db.Database.BeginTransaction()
        p = db.People.FirstOrDefault(Function(px) px.PersonID = 8551)
        p.PersonCustom1 = p.PersonCustom1 & "rollback;"
        'db.SaveChanges()
        transaction.Rollback()
        transaction.Dispose()

        ' do a commit after a rollback
        transaction = db.Database.BeginTransaction()
        p = db.People.FirstOrDefault(Function(px) px.PersonID = 8551)
        p.PersonCustom1 = p.PersonCustom1 & "fourth;"
        db.SaveChanges()
        transaction.Commit()
        transaction.Dispose()

    End Sub

The resulting string in PersonCustom1 looks like this: "First;second;rollback;fourth;"

I would have expected that it be missing the "rollback;".  I tried without the .Dispose() statements.  I tried with and without the SaveChanges before the rollback.

This is my first experience with Entity Framework.  All the rollback code for framework 6 that I've found on the web seems to indicate that it should be working.  I'm hoping I just missed something simple..?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Thats because you are not disposing your db context, when you retrieve the p record again it is already cached in the context and is pulled from there, instead of from the database.  If you dispose your context, recreate it and then retrieve the record it will fetch it from the database instead of it's internal cache.
   Sub TestEntityTransactions2()
        Using db As ScrapDBEntities = New ScrapDBEntities()
            Dim p As Person = db.People.FirstOrDefault(Function(px) px.PersonId = 8551)
            p.PersonCustom1 = "First;"
            db.SaveChanges()
        End Using

        ' Testing the save changes not attached to a transaction

        Using db As ScrapDBEntities = New ScrapDBEntities()
            Dim transaction As System.Data.Entity.DbContextTransaction

            ' do a commit to the transaction
            transaction = db.Database.BeginTransaction()
            Dim p As Person = db.People.FirstOrDefault(Function(px) px.PersonId = 8551)
            p.PersonCustom1 = p.PersonCustom1 & "second;"
            db.SaveChanges()
            transaction.Commit()
            transaction.Dispose()
        End Using

        Using db As ScrapDBEntities = New ScrapDBEntities()
            Dim transaction As System.Data.Entity.DbContextTransaction
            Dim p As Person
            ' do a rollback to the transaction
            transaction = db.Database.BeginTransaction()
            p = db.People.FirstOrDefault(Function(px) px.PersonId = 8551)
            p.PersonCustom1 = p.PersonCustom1 & "rollback;"
            'db.SaveChanges()
            transaction.Rollback()
            transaction.Dispose()
        End Using

        Using db As ScrapDBEntities = New ScrapDBEntities()
            Dim transaction As System.Data.Entity.DbContextTransaction
            ' do a commit after a rollback
            Dim p As Person
            transaction = db.Database.BeginTransaction()
            p = db.People.FirstOrDefault(Function(px) px.PersonId = 8551)
            p.PersonCustom1 = p.PersonCustom1 & "fourth;"
            db.SaveChanges()
            transaction.Commit()
            transaction.Dispose()
        End Using
    End Sub

Open in new window

Author

Commented:
It worked perfectly!  Thanks for such a precise and easy to follow answer...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial