Using Transactions or SaveChanges(false) and AcceptAllChanges()?

Question

I have been investigating transactions and it appears that they take care of themselves in EF as long as I pass false to SaveChanges() and then call AcceptAllChanges() if there are no errors:

SaveChanges(false);
// ...
AcceptAllChanges();

What if something goes bad? don't I have to rollback or, as soon as my method goes out of scope, is the transaction ended?

What happens to any indentiy columns that were assigned half way through the transaction? I presume if somebody else added a record after mine before mine went bad then this means there will be a missing Identity value.

Is there any reason to use the standard TransactionScope class in my code?

Anselma

This helped me understand why SaveChanges(fase); ... AcceptAllChanges(); was a pattern in the first place. Notice how the accepted answer to the above question, is written by the author of a blog -- and that blog is referenced in the other question. It all comes together. – Monda

Identity columns are never rolled back. Their increment is permanent, even when not actually committed. – Palimpsest

Answer 1

With the Entity Framework most of the time SaveChanges() is sufficient. This creates a transaction, or enlists in any ambient transaction, and does all the necessary work in that transaction.

Sometimes though the SaveChanges(false) + AcceptAllChanges() pairing is useful.

The most useful place for this is in situations where you want to do a distributed transaction across two different Contexts.

I.e. something like this (bad):

using (TransactionScope scope = new TransactionScope())
{
    //Do something with context1
    //Do something with context2

    //Save and discard changes
    context1.SaveChanges();

    //Save and discard changes
    context2.SaveChanges();

    //if we get here things are looking good.
    scope.Complete();
}

If context1.SaveChanges() succeeds but context2.SaveChanges() fails the whole distributed transaction is aborted. But unfortunately the Entity Framework has already discarded the changes on context1, so you can't replay or effectively log the failure.

But if you change your code to look like this:

using (TransactionScope scope = new TransactionScope())
{
    //Do something with context1
    //Do something with context2

    //Save Changes but don't discard yet
    context1.SaveChanges(false);

    //Save Changes but don't discard yet
    context2.SaveChanges(false);

    //if we get here things are looking good.
    scope.Complete();
    context1.AcceptAllChanges();
    context2.AcceptAllChanges();

}

While the call to SaveChanges(false) sends the necessary commands to the database, the context itself is not changed, so you can do it again if necessary, or you can interrogate the ObjectStateManager if you want.

This means if the transaction actually throws an exception you can compensate, by either re-trying or logging state of each contexts ObjectStateManager somewhere.

See my blog post for more.

Leong

Thats great, thanks... So if something fails don't i have to rollback?? SaveChanges, marks it for being saved, but doesn't actually commit until i do acceptallchanges.. but if something goes wrong.. i will need to rollback won't i so that my object returns to its correct state? – Anselma

@Mark: if by "roll-back" you mean, revert your objects to the state that they are in in the database, then no, you wouldn't want to do that because you'd lose all the user's changes to the objects. SaveChanges(false) does the actual updating to the database, while AcceptAllChanges() tells EF, "Okay, you can forget which things need to be saved, because they've been sucessfully saved." If SaveChanges(false) fails, AcceptAllChanges() will never be called and EF will still consider your object as having properties that were changed and need to be saved back to the database. – Weathering

Can you advise how to do this using Code First? There is no parameter to SaveChanges or AcceptAllChanges method – Video

I have asked a question about using this technique with Code First here – Video

This is no longer possible in EF 6.1. Do you know what kind of adjustments need to be made to work now? – Another

Answer 2

If you are using EF6 (Entity Framework 6+), this has changed for database calls to SQL.
See: https://learn.microsoft.com/en-us/ef/ef6/saving/transactions

Use context.Database.BeginTransaction.

From MSDN:

using (var context = new BloggingContext()) 
{ 
    using (var dbContextTransaction = context.Database.BeginTransaction()) 
    { 
        try 
        { 
            context.Database.ExecuteSqlCommand( 
                @"UPDATE Blogs SET Rating = 5" + 
                    " WHERE Name LIKE '%Entity Framework%'" 
                ); 

            var query = context.Posts.Where(p => p.Blog.Rating >= 5); 
            foreach (var post in query) 
            { 
                post.Title += "[Cool Blog]"; 
            } 

            context.SaveChanges(); 

            dbContextTransaction.Commit(); 
        } 
        catch (Exception) 
        { 
            dbContextTransaction.Rollback(); //Required according to MSDN article 
            throw; //Not in MSDN article, but recommended so the exception still bubbles up
        } 
    } 
} 

Hersch

try-catch with roolback is not needed when you are using "using" on the transaction. – Patriotism

I'm taking an exception to trapping the exception like this. It causes the database operation to fail silently. Due to the nature of SO, someone might take this example and use it in a production application. – Garbo

@B2K: Good point, but this code is copied from the linked Microsoft article. I hope no one uses their code in production :) – Raving

@Patriotism According to the MSDN article Rollback() is necessary. They purposefully leave out a Rollback command for the TransactionScope example. @Garbo I have added in the throw; to the MSDN snippet and indicated clearly that it's not the original from the MSDN article. – Unceasing

(If correct) This might clear things up: Sounds like EF + MSSQL doesn't need Rollback, but EF + other SQL providers might. Since EF is supposed to be agnostic of which database it's talking to, Rollback() is called in case it's talking to MySql or something that doesn't have that automatic behavior. – Dihybrid

I note how this is not at all applicable in cases where you are working across two or more database contexts. For that you still need TransactionScope – Paramorph

Additional "Like" for throw; – Confidential

The transaction either commits or rollsback. explicit call is not needed. Also if there is a severe SQL error the explicit call to rollback will fail. github.com/aspnet/EntityFramework.Docs/issues/327 #41386240 – Reinstate

So why is there then a Rollback function if the rollback is done automatically for you if something happens? Only because you could use entity framework with something else than mssql? – Kunming

Answer 3

   public static TransactionScope CreateAsyncTransactionScope(IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
    {
        var transactionOptions = new TransactionOptions
        {
            /* IsolationLevel = isolationLevel,*/
            Timeout = TransactionManager.MaximumTimeout,

        };
        return new TransactionScope(TransactionScopeOption.Required, transactionOptions, TransactionScopeAsyncFlowOption.Enabled);
    }

if use this method the Problem in transaction Scop Option Isolation Level you must be delete this option if you are used

Plenum

Sorry, my friend, this is the first comment I meant to help so i'm try to fix it – Plenum

But the question is at another level. Whether to use TransactionScope or not. Its isolation level is irrelevant. – Palimpsest

that problem i was fall it so after delete this option that problem disappear – Plenum

Well, your problem isn't clear. This doesn't answer the question. – Palimpsest

Answer 4

Because some database can throw an exception at dbContextTransaction.Commit() so better this:

using (var context = new BloggingContext()) 
{ 
  using (var dbContextTransaction = context.Database.BeginTransaction()) 
  { 
    try 
    { 
      context.Database.ExecuteSqlCommand( 
          @"UPDATE Blogs SET Rating = 5" + 
              " WHERE Name LIKE '%Entity Framework%'" 
          ); 

      var query = context.Posts.Where(p => p.Blog.Rating >= 5); 
      foreach (var post in query) 
      { 
          post.Title += "[Cool Blog]"; 
      } 

      context.SaveChanges(false); 

      dbContextTransaction.Commit(); 

      context.AcceptAllChanges();
    } 
    catch (Exception) 
    { 
      dbContextTransaction.Rollback(); 
    } 
  } 
} 

Obvert

I'm taking an exception to trapping the exception like this. It causes the database operation to fail silently. Due to the nature of SO, someone might take this example and use it in a production application. – Garbo

Isn't this essentially the same as this other answer which gave attribution to the MSDN page it quotes? The only difference I see is that you pass false into context.SaveChanges();, and additionally call context.AcceptAllChanges();. – Pressurize

@Garbo the rollback is not required - if transaction does not work nothing is committed. Also explicit call to Rollback can fail - see my answer here #41386240 – Reinstate

The rollback is not what I’m objecting to. The author of this answer updated their code to rethrow the exception, thus resolving what I was objecting to. – Garbo

Sorry, I commented from my phone. Todd re-throws the exception, eMeL does not. There should be something in the catch which notifies either the developer or the user of a problem causing a rollback. That could be writing to a log file, rethrowing the exception or returning a message to the user. – Garbo