locked
Transaction in Entity Framework RRS feed

  • Question

  • Hello.

    I have a system, which pick up a value in database, example my table.

    ID        DESCRIPTION       VALUE
     1               SALES              10
     2             EMPLOYEE           30

    For example, I want pick up value the Employee, so I created this code
    public decimal returnValue()
    {
    
       ApplicationEntity ent = new ApplicationEntity();
    
       Table t = new Table(); 
    
        t.VALUE = (from tab in ent.TABLE
                        select tab.VALUE
                        where tab.ID = 2);
    
       decimal v = t.VALUE;
    
       t.VALUE += 1;
    
       ent.AddToTable(t);
    
       ent.SaveChanges();  
    
    }

    What do I want? I want this code happen in a transaction, when the application pick up value 30, method can no longer return this value.

    Can Somebody help me?

    Thank in advance.



    Marcos Aguiar Jr - Brazil
    Thursday, August 20, 2009 3:15 PM

Answers

  • Some notes:

    1. All EF updates happen in a transaction. You do not need to begin a transaction yourself.

    2. This isn't actually a transaction issue but a multi-threading issue - you have two ways of solving this:
     a. You can lock the method itself, if the method is the only mechanism by which the ID is changed, this will work.
     b. You can use optimistic concurrency instead to ensure that the object was not changed while you were modifying it.

    a is simple and will work for single-client systems. if you are running multiple servers or appdomains in your middle tier, you will want to go with b.

    HTH,
     Noam


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Yichun_Feng Thursday, August 27, 2009 8:24 AM
    Tuesday, August 25, 2009 9:10 PM
  • Hi Marcos,
    Try using the TransactionScope class of System.Transactions.
    You can read about how to use it in EF and also get example in the following links:
    http://msdn.microsoft.com/en-us/library/bb738530.aspx
    http://msdn.microsoft.com/en-us/library/bb738523.aspx

    I hope it will help you.
    Gil Fink
    • Marked as answer by Yichun_Feng Thursday, August 27, 2009 8:24 AM
    Wednesday, August 26, 2009 1:42 PM

All replies

  • Not sure I understand. You want to run this code in a loop for 30 times and commit the changes after 30 times?

    SaveChanges uses a transaction by itself, all entities that are saved to the db are commited at once or none at all.

    You can use System.Transaction to create a transaction scope and inside it call SaveChanges multiple time and all these calls will be enlisted to the same transaction.

    Does this help?
    Please mark posts as answers/helpful if it answers your question
    Thursday, August 20, 2009 6:35 PM
  • Hello Ido. Thanks by your help. Don't want to do a loop. When my transaction pick up 30 value, don't want other transaction get same value. Imagination two transaction running this method, may occur the both get same value, using transaction I believe than this don't happen. Can you understand my problem? Thank in advance

    Marcos Aguiar Jr - Brazil
    Thursday, August 20, 2009 7:23 PM
  • It's see code down. 

    ent.Connection.Open(); 
    
    ent.Connection.BeginTransaction(IsolationLevel.Serializable);
     
    EMPLOYEE emp = NEW EMPLOYEE(); 
    
    emp.ID = 1;
    
    emp.NAME = "MARK"; 
    
    ent.AddToEMPLOYYE(emp); 
    
    ent.SaveChanges(); 

    How I execute commit? Which enter different use or not use the BeginTransaction?

    Thank in advance.

    Marcos Aguiar Jr - Brazil
    Thursday, August 20, 2009 9:45 PM
  • Hi Marcos Aguiar Jr,

     

    You can commit the transaction like this way:

                StudentEntities context = new StudentEntities();

     

                DbTransaction dbTran = null;

                try

                {

                    context.Connection.Open();

                  

                    dbTran = context.Connection.BeginTransaction();

                    //The manipulation of DB

                    dbTran.Commit();

                }

                catch (Exception ex)

                {

                    dbTran.Rollback();

                }

                finally

                {

                    context.Connection.Close();

                }

     

    The main function of transaction is that it can rollback all the changes done before in the transaction if certain step is failed.  For more about the function of using transaction, you can refer to this URL:

    http://en.wikipedia.org/wiki/Database_transaction

     

     

    Does this works for you? If you have any questions or concerns, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng

     

    Monday, August 24, 2009 6:59 AM
  • Hi Yichun Feng.

    Thanks for help me.

    It's exactly what I wanted to do, but I have other ask, Is possible send transaction for other method?

    Thank in advance.

    Marcos Aguiar Jr - Brazil
    Monday, August 24, 2009 2:06 PM
  • Hi Marcos Aguiar Jr,

     

    That depends on your method. If the method is about database manipulation, you can put it in the comment line of my code. If it isn’t related to database manipulation, then transaction will not work for it.

     

    Best Regards

    Yichun Feng

    Tuesday, August 25, 2009 2:31 AM
  • Hi Yichun Feng.

    Example

    // example method
    public void Test(DBTransaction db)
    {
                TesttEntities context = new TesttEntities();
                
                 
                DbTransaction dbTran = null;
                try
                {
                    context.Connection.Open();
                    
                    if (db == null)
                        dbTran = context.Connection.BeginTransaction();
                    else
                        dbTran = db;
                    
                    //The manipulation of DB
                    if (db == null)
                        dbTran.Commit();
                }
                catch (Exception ex)
                {
                    dbTran.Rollback();
                }
                finally
                {
                    context.Connection.Close();
                }
    }
    
    
    // your method.
    StudentEntities context = new StudentEntities();
     
     DbTransaction dbTran = null;
                try
                {
                    context.Connection.Open();
                  
                    dbTran = context.Connection.BeginTransaction();
    
                    this.Test(dbTran);
                    
                    //The manipulation of DB
                    dbTran.Commit();
                }
                catch (Exception ex)
                {
                    dbTran.Rollback();
                }
                finally
                {
                    context.Connection.Close();
                }



    I Want to do something like that. Is this possible?

    Thank in Advance



    Marcos Aguiar Jr - Brazil
    Tuesday, August 25, 2009 12:31 PM
  • Some notes:

    1. All EF updates happen in a transaction. You do not need to begin a transaction yourself.

    2. This isn't actually a transaction issue but a multi-threading issue - you have two ways of solving this:
     a. You can lock the method itself, if the method is the only mechanism by which the ID is changed, this will work.
     b. You can use optimistic concurrency instead to ensure that the object was not changed while you were modifying it.

    a is simple and will work for single-client systems. if you are running multiple servers or appdomains in your middle tier, you will want to go with b.

    HTH,
     Noam


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Yichun_Feng Thursday, August 27, 2009 8:24 AM
    Tuesday, August 25, 2009 9:10 PM
  • Hi Noam.

    Thanks for help me.

    Imagine three methods.
    public void AddPerson(Person pes)
    {
        PersonEntities context = new PersonEntities();
    
        context.AddToPerson(pes);
        
    }
    
    public void AddPersonAdress(PersonAdress adress)
    {
        PersonEntities context = new Person Entities();
    
        context.AddtoPersonAdress(adress);
    }
    
    public void insertPerson(Person pes, PersonAdress adress)
    {
        AddPerson(pes);
        AddPersonAdress(adress);
    }
    
    
    If I insert one person in system, the data about person (name, birth date) is save in a table different that the adress (adress, city, country, zip code, state).

    If an error happen when my application will insert PersonAdress, I need to do a rollback. 

    The problem is that Person is save, didn't happen rollback.

    Can anybody help me?

    Thanks in Advance.


    Marcos Aguiar Jr - Brazil
    Wednesday, August 26, 2009 12:46 PM
  • Hi Marcos,
    Try using the TransactionScope class of System.Transactions.
    You can read about how to use it in EF and also get example in the following links:
    http://msdn.microsoft.com/en-us/library/bb738530.aspx
    http://msdn.microsoft.com/en-us/library/bb738523.aspx

    I hope it will help you.
    Gil Fink
    • Marked as answer by Yichun_Feng Thursday, August 27, 2009 8:24 AM
    Wednesday, August 26, 2009 1:42 PM
  • Hi Marcos Aguiar Jr,

     

    I’ve tried this example, and it works fine when the second inserting fails, the first inserting rolls back.

     

                StudentEntities context = new StudentEntities();

                DbTransaction dbTran = null;

     

                try

                {

                    context.Connection.Open();

                    dbTran = context.Connection.BeginTransaction();

     

                    Persons person = new Persons { id = 98, name = "typot" };

                    Stu stu = new Stu() { name = "pigtypot" };

     

                    context.AddToPersons(person);

                    context.AddToStu(stu);

                    context.SaveChanges();

     

                    dbTran.Commit();

                }

                catch (Exception ex)

                {

                    dbTran.Rollback();

                    MessageBox.Show(ex.ToString());

                }

                finally

                {

                    context.Connection.Close();

                }

     

    If there is a foreignkey constraint in the two table, you can use one add method for inserting.

    Like in this thread:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/5d9273db-8083-4598-a0e9-28fe1fcf096b/

     

    Does this works for you? If you have any questions or concerns, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng

    • Proposed as answer by Yichun_Feng Thursday, August 27, 2009 8:25 AM
    Thursday, August 27, 2009 3:04 AM
  • Hi Yichun Feng.

    I did one example equals It's and work, but the problem is that I have other method that are other class and I need to use in just with one transaction, so I need to pass the object context.

    Using your example, I need to create two method, one to Person another to Student and use the same object context.

    Example:

    public void AddStudent()
    {
        StudentEntity ent = new StudentEntity();


        try
        {
            ent.Connection.Open();
            dbTrans = ent.Connection.BeginTransaction();

            this.InsertPerson(ent);
            this.insertStudent(ent);

            dbTrans.Commit();
         }
         catch
        {
           dbTrans.RollBack();
        }
        finally
        {
           dbTrans.Connection.Close();
         }

    I believe so solve my problem.

    What do you think?

    Thanks in advance.
    Marcos Aguiar Jr - Brazil
    • Proposed as answer by Tony Gupta Wednesday, July 17, 2013 3:11 AM
    • Unproposed as answer by Tony Gupta Wednesday, July 17, 2013 3:11 AM
    Thursday, August 27, 2009 11:42 AM