16

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)?!

Gary McGill
  • 26,400
  • 25
  • 118
  • 202
  • 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](http://stackoverflow.com/questions/1690892/transactionscope-automatically-escalating-to-msdtc-on-some-machines) – Mark Oreta Oct 10 '12 at 00:36
  • 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 – Pawel Oct 10 '12 at 05:12
  • @markoreta: I'm using SQL server 2012. I don't have (or want) multiple connections, and don't really want to use MSDTC! – Gary McGill Oct 10 '12 at 07:21
  • @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? – Gary McGill Oct 10 '12 at 07:29
  • 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) – Pawel Oct 10 '12 at 14:00
  • @Pawel: there is no ambient transaction, unless I create one, which is exactly what I was trying to do!!! – Gary McGill Oct 10 '12 at 15:31
  • @Pawel: you say "nested transactions are not really supported on SqlServer". What's your evidence for this? (I think this is wrong). – Gary McGill Oct 10 '12 at 15:32
  • @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: http://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. – Pawel Oct 10 '12 at 16:30
  • @GaryMcGill - I assume you open your connection inside the transaction and not outside. – Pawel Oct 10 '12 at 16:32
  • 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 – MJDConsultingGroup May 26 '15 at 15:50

3 Answers3

13

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

Wahid Bitar
  • 13,776
  • 13
  • 78
  • 106
8

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();
}
Asiri Dissanayaka
  • 474
  • 1
  • 7
  • 18
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • If it is in same DbContext, EF6.0 have context.Database.BeginTransaction(). But this works well if operations in different DBContexts. – Tony Bao Jun 25 '17 at 18:37
7

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.

Paul Zahra
  • 9,522
  • 8
  • 54
  • 76
  • 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. – Gary McGill Feb 26 '13 at 11:57
  • What if the first update operation goes fine but the second update operation fails, is your data in the DB consistent? – Paul Zahra Feb 26 '13 at 12:15
  • Since the whole thing is wrapped in TransactionScope, then I should hope so! – Gary McGill Feb 26 '13 at 12:19
  • 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. – Paul Zahra Feb 26 '13 at 14:19