Can't get transaction rollback working for Entity Frameworks 6

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..?
LVL 1
FrancineTaylorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LordWabbitCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FrancineTaylorAuthor Commented:
It worked perfectly!  Thanks for such a precise and easy to follow answer...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.