Multiple database in one transaction in Entity Framework

Question

I have created two different Contexts for two different databases in Entity Framework. Now I am trying to update these databases in single transaction. My code is like that:

public class LPO_BLL
{
    internal Context1 _context1 = null;
    internal Context2 _Context2 = null;

    public LPO_Detail_BLL(Context1 context1, Context2 context2)
    {
        _context1 = context1;
        _context2 = context2;
    }

    public void Insert(PM_LPO lpo, LPO_Transaction lpo_transaction)
    {
        using (TransactionScope transaction = new TransactionScope())
        {
            _context1.LPO.Add(lpo);
            _context1.SaveChanges();

            _context2.LPO_Transaction.Add(lpo_transaction);
            _context2.SaveChanges();  // I am getting error here...

            transaction.Complete();
        }
    }
}

And in UI project, I am calling this as:

LPO lpo = new LPO();
//setting properties of lpo

LPO_Transaction lpo_trans = new LPO_Transaction();
//setting properties of lpo_trans

Context1 _context1 = new Context1();
//Opening _context1 connection and etc

Context2 _context2 = new Context2();
//Opening _context2 connection and etc

LPO_BLL lpo_bll = new LPO_BLL(_context1, _context2);

lpo_bll.Insert(lpo,lpo_trans);

At the moment, I am getting error : The underlying provider failed on EnlistTransaction

After searching over the internet for last 3 hours and trying different hit and trial methods, I decided to put this on SO. So far, i have found these two links to little bit closer:

http://social.msdn.microsoft.com/Forums/en-US/3ccac6f7-6513-4c87-828a-00e0b88285bc/the-underlying-provider-failed-on-enlisttransaction?forum=adodotnetentityframework

TransactionScope - The underlying provider failed on EnlistTransaction. MSDTC being aborted

score 5 · Accepted Answer · edited May 23 '17 at 11:54

5

Not all DB providers support distributed transactions.

Using transaction scopes will try to enlist the DB transaction in a distributed transacation managed by MSDTC. If your provider doesn't support this, it will fail.

SQL Server and Oracle providers support distributed transactions. But many other EF providers don't.

If your DB provider doesn'd support this, you'll have to use a different one or give up using transactions.

Provided you're using SQL Server 2005, it should be working, but:

MSDTC service must be running (look for it in Services, in the Control Panel).
the connection strings must be adequate for MSDTC to work

Look at this SO Q&A: confusion about transactions and msdtc.

NOTE: The name of the service is MSDTC. So you can run net start msdtc or net stop msdtc. If you're looking for it in the control panel, you'll find a descriptive name like "Distributed Transaction Coordinator" or a localized name like "Coordinador de transacciones distribuidas". Oddly enough, there is no way to show the name column in the control panel list of local services.

edited May 23 '17 at 11:54

Community

1
1

answered Dec 09 '13 at 10:40

JotaBe

38,030
8
98
117

I am using SQL Server 2005 as provider for Both Contexts. – Usman Khalid Dec 09 '13 at 10:43
Is MSDTC running? How did you create the connection strings? – JotaBe Dec 09 '13 at 10:46
I am creating connection string in Global.ascx in Application_Start event. And I don't know about if the MSDTC is running or not? – Usman Khalid Dec 09 '13 at 10:50
I have just checked. MSDTC is enabled on our server. – Usman Khalid Dec 09 '13 at 10:55
Are the SQL server and app server differente machines? Look for MSDTC configuration. There are some things to take into account (ntework connectivity, MSDTC running on both client and server, and some other things). Look at the excpetion stack (inner exceptions) and see if any of the messages clarifies the problem. – JotaBe Dec 09 '13 at 11:06
I got it dude. I checked again. MSDTC was not enabled. Now I am contacting my IT so they can enable it. Thanks for your support. I will mark you answer in a few minutes. :) – Usman Khalid Dec 09 '13 at 11:09
Bit late to the conversation but it might help someone to know that the MSDTC service is actually called "Distributed Transaction Coordinator". There's no MSDTC in the list of services, so it helps to know the actual service name – Nick Mar 15 '16 at 13:47
@Nick I've included a note related to your comment in my answer. – JotaBe Mar 15 '16 at 14:29

score 2 · Answer 2 · answered Jun 17 '15 at 15:54

You have to use ObjectContext in you DbContext in order to use SaveChanges with parameter:

public class EntityDBContext: DbContext, IObjectContextAdapter
{
    ObjectContext IObjectContextAdapter.ObjectContext {
        get { 
           return (this as IObjectContextAdapter).ObjectContext;
        }
    }
}

and then in your Insert method, use:

public void Insert(PM_LPO lpo, LPO_Transaction lpo_transaction)
{
    using (TransactionScope transaction = new TransactionScope())
    {

         context1.ObjectContext.SaveChanges(false);
         context2.ObjectContext.SaveChanges(false);

         _context1.LPO.Add(lpo);
         _context2.LPO_Transaction.Add(lpo_transaction);       

         transaction.Complete();

         context1.ObjectContext.AcceptAllChanges();
         context2.ObjectContext.AcceptAllChanges();

    }
}

score 1 · Answer 3 · edited Nov 13 '15 at 11:21

1

For multiple database set of Savechange(false) and AcceptAllChanges() should be used.

 public void Insert(PM_LPO lpo, LPO_Transaction lpo_transaction)
{
    using (TransactionScope transaction = new TransactionScope())
    {

         context1.SaveChanges(false);
         context2.SaveChanges(false);

        _context1.LPO.Add(lpo);
        _context2.LPO_Transaction.Add(lpo_transaction);       

        transaction.Complete();

       context1.AcceptAllChanges();
       context2.AcceptAllChanges();

    }
}

edited Nov 13 '15 at 11:21

Borys Generalov

2,255
17
19

answered Dec 09 '13 at 11:23

YOusaFZai

698
5
21

I am using Entity Framework 5 and there is no SaveChanges method which accepts any parameter. – Usman Khalid Dec 09 '13 at 11:28
usman may be you find this helpfull http://blogs.msdn.com/b/alexj/archive/2009/01/11/savechanges-false.aspx – YOusaFZai Dec 09 '13 at 11:33
It's good approach but I don't know why SaveChanges(false) is not supported in my application. I am using Entity Framework 5. – Usman Khalid Dec 09 '13 at 11:48
i am not sure but see the documentation for EF5 or try it by using previous version. – YOusaFZai Dec 09 '13 at 11:49
Usman use this SaveChanges(SaveOptions.DetectChangesBeforeSave) is equivalent of SaveChanges(false) – YOusaFZai Dec 09 '13 at 11:51
http://stackoverflow.com/questions/10404934/entity-framework-transactions-replacing-savechangesbool-with-savechangessav – YOusaFZai Dec 09 '13 at 11:52
Yes! I saw it. But SaveChanges at the moment has no overloaded method which accepts any parameter. – Usman Khalid Dec 09 '13 at 12:14
I have downgraded to Entity Framework 4 but still the same. No overloaded SaveChanges. – Usman Khalid Dec 09 '13 at 12:16
brother are you using ObjectContext or DataContext? – YOusaFZai Dec 09 '13 at 12:43
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/42786/discussion-between-salman-khan-and-usman-khalid) – YOusaFZai Dec 09 '13 at 12:47
I changed the whole architecture according to ObjectContext but still no success. I am getting the same error. – Usman Khalid Dec 10 '13 at 10:42

Multiple database in one transaction in Entity Framework

3 Answers3

Linked