IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
1 reply Latest Post - ‏2013-02-12T10:51:48Z by TapanKumarGhosh
2826 Posts

Pinned topic Using Non-Distributed Transactions in Entity Framework 5?

‏2013-02-06T18:51:48Z |
I am starting out learning all of this. This is my code:

using (var context = new developContext())
using (TransactionScope transaction = new TransactionScope())
consecs c = context.consecs.First(i => i.consec >= -1);
consecs c2 = context.consecs.First(i => i.consec >= -1);


I open the connection before the transaction because if I do this the other way around, it escalates to a distributed transaction from the beginning. Then I issue the first query. I just wanted to get the only record in the table with only one field, so I used that EF syntax for it.

The problem is, this select closes the connection when it's done, so the second select tries to reopen the connection with a distributed transaction, and fails.

The example I was going for with transactions working, is:
update consecs set consec = consec+1; //update and lock the table
select consec from consecs; // should be the updated value
rollback; // undo all of this so the example is repeatable without altering the database

But I am getting stuck with trying to keep the transaction local, even if I'm not making an update yet.

Any tips?
Updated on 2013-02-12T10:51:48Z at 2013-02-12T10:51:48Z by TapanKumarGhosh
  • TapanKumarGhosh
    10 Posts

    Re: Using Non-Distributed Transactions in Entity Framework 5?

    ‏2013-02-12T10:51:48Z  in response to SystemAdmin
    You should use connection.BeginTransaction() method to make local transaction. you can refer MSDN link :
    Here is the db2 infocenter link :
    which says how to make local transaction which is having demo program as well.
    As per your requirement (update, select ,rollback), I have experimented with below code with my EF model and it is working fine.

    For example:
    using (var context = new SAMPLEEntities())
    using (System.Data.Common.DbTransaction transaction = context.Connection.BeginTransaction())
    EMPLOYEE e1 = context.EMPLOYEEs.First(i => i.FIRSTNME == "SALLY");
    Console.WriteLine("EMPNAME:" + e1.LASTNAME + " JOB:" + e1.JOB);

    e1.SALARY = 6000; //update the salary and lock the table

    EMPLOYEE e2 = context.EMPLOYEEs.First(i => i.SALARY == 6000); //should be
    //the updated salary value
    Console.WriteLine("EMPNAME:" + e2.LASTNAME + " JOB:" + e2.JOB);
    transaction.Rollback(); //undo all of this so the example is repeatable
    //without altering the database

    NOTE : For opening the connection you need to use "context.Connection.Open()" instead of "context.Database.Connection.Open()"

    Could you please be more elaborate on what you wanted to achieve.