EF: How do I call SaveChanges twice inside a transaction?

Question

Using Entity Framework (code first in my case), I have an operation that requires me to call SaveChanges to update one object in the DB, and then SaveChanges again to update another object. (I need the first SaveChanges to resolve an issue where EF can't figure out which object to update first).

I tried doing:

using (var transaction = new TransactionScope())
{
    // Do something

    db.SaveChanges();

    // Do something else

    db.SaveChanges();

    tramsaction.Complete();
}

When I run that, I get an exception at the second SaveChanges call, saying "the underlying provider failed on open". The inner exception says that MSDTC is not enabled on my machine.

Now, I've seen posts elsewhere that describe how to enable MSDTC, but it seems that I would also need to enable network access, etc. This sounds like complete overkill here, since there are no other databases involved, let alone other servers. I don't want to do something that's going to make my whole application less secure (or slower).

Surely there must be a more lightweight way of doing this (ideally without MSDTC)?!

Ignescent

Are you using SQL 2008? Depending on what your actual logic is, you can open multiple connections without escalating. Here's a great post breaking down when the DTC gets called: Transaction scope Automatically escalating to MSDTC – Checkmate

If you want to do everything in just one transaction what is the difference between saving everything once or saving everything multiple Times? In both cases either everything will be saved or nothing will be saved so I don't see any advantage of multiple saves. As per the comment below - in Sql Server 2005 opening multiple connection within a transaction (even if the source is the same) causes the transaction to be promoted to be a distributed transaction. This was improved in Sql Server 2008 where you can open multiple connections to the same datasource within a trx without causing promotion – Austenite

@markoreta: I'm using SQL server 2012. I don't have (or want) multiple connections, and don't really want to use MSDTC! – Ignescent

@pawel: I don't understand. I thought that each SaveChanges would use its own transaction, and so the first could succeed and the second could fail and roll back, leaving my database inconsistent. That's why I thought I needed an outer transaction. Was I wrong? – Ignescent

If there is an ambient transaction your connection will be automatically enrolled in this transaction and SavaChanges will not create a new transaction. In addition nested transactions are not really supported on SqlServer (i.e. nested Begin Tran are being ignored) – Austenite

@Pawel: there is no ambient transaction, unless I create one, which is exactly what I was trying to do!!! – Ignescent

@Pawel: you say "nested transactions are not really supported on SqlServer". What's your evidence for this? (I think this is wrong). – Ignescent

@GaryMcGill when you create TransactionScope you do create an ambient transaction and when openning a connection in its scope the connection will automatically enlisted into this transaction and therefore EF will not run BeginTransaction. Take a look at this: msdn.microsoft.com/en-us/library/ms189336(v=sql.105).aspx it reads: "Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction." You can use save points but EF does not do it. – Austenite

@GaryMcGill - I assume you open your connection inside the transaction and not outside. – Austenite

For the final answer selected above, there is a typo. Corrected line below: objectContext.Connection.Open(); Also, need to add references for System.Data.Entity.Infrastructure and System.Transactions – Feudatory

Answer 1

It is probably caused by two different connections used in your transaction. Try to control connection for your operation manually:

var objectContext = ((IObjectContextAdapter)db).ObjectContext;

try {
    //Open Connection
    objectContext.Connection.Open();

    using (var transaction = new TransactionScope()) {
        // Do something

        db.SaveChanges();

        // Do something else

        db.SaveChanges();

        transaction.Complete();
    }
} finally {
    //Close connection after commit
    objectContext.Connection.Close();
} 

Assortment

If it is in same DbContext, EF6.0 have context.Database.BeginTransaction(). But this works well if operations in different DBContexts. – Thurlow

Answer 2

I know it's kind of late answer but i found it useful to share.

Now in EF6 it's easier to acheeve this by using dbContext.Database.BeginTransaction()

like this :

using (var context = new BloggingContext())
{
    using (var dbContextTransaction = context.Database.BeginTransaction())
    {
        try
        {
            // do your changes
            context.SaveChanges();

            // do another changes
            context.SaveChanges();

            dbContextTransaction.Commit();
        }
        catch (Exception)
        {
            dbContextTransaction.Rollback();
        }
    }
}

for more information look at this

again it's in EF6 Onwards

Doralyn

Thanks, that's more like it! – Ignescent

You should do more with the catch block than just rollback and swallow the exception. Log, rethrow, ... – Naominaor

@WahidBitar: Is the try/catch block necessary? Doesn't the using take care of handling the exception? (something like calling Rollback in Dispose method of dbContextTransaction) – Spruill

@Spruill I'm not sure 100% but as I know dispose itself will not do a rollback. – Doralyn

Answer 3

By calling SaveChanges() as you are is causing the data to be persisted to the database and the EF to forget about the changes it just made.

The trick is to use SaveChanges(false) so that the changes are persisted to the DB but EF doesn't forget the changes it makes thus making logging / retrying possible.

        var scope = new TransactionScope(
            TransactionScopeOption.RequiresNew,
            new TransactionOptions() { IsolationLevel = IsolationLevel.Serializable }
        );

        using (scope)
        {
            Entities context1 = new Entities();
            // Do Stuff
            context1.SaveChanges(false);

            Entities context2 = new Entities();
            // Do Stuff
            context2.SaveChanges(false);

            scope.Complete();
            context1.AcceptAllChanges();
            context2.AcceptAllChanges();
        }

P.S. As soon as you have more than one connection open inside transactionscope it WILL escalate to DTC.

Pugh

Thanks. That sounds eminently plausible, though in my case I don't care about the state of the local context if there's an error, since in that case I'll be bombing out and destroying the context anyway. Still, good to know. – Ignescent

What if the first update operation goes fine but the second update operation fails, is your data in the DB consistent? – Pugh

Since the whole thing is wrapped in TransactionScope, then I should hope so! – Ignescent

Indeed, what I meant was that using SaveChanges(false) while it updates the database it will leave the context as is, so it is easier to determine what / where the function went wrong, and retry if you like. – Pugh