Understanding Transactions in Entity Framework

Question

Hi I am trying to use transactions along with Entity Framework. With so much information available online on the different ways to implement transactions I must say I am a bit confused on the right way. I have an example database with two tables Employee and Company. The Employee table has a foreign key referring to the company Id. Considering I want to implement a transaction where I insert a record to the Company table and then a record to the Employee table and I want to do this so that records are inserted only if both are successful I have the following code.

public void addCompanyToDatabase()
    {
        using (var context = new myTestEntities())
        {
            context.Connection.Open(); //added this as was getting the underlying   
            //provider failed to open
            using (TransactionScope scope = new TransactionScope())
            {
                try
                {
                    Company c = new Company();
                    c.Name = "xyz";
                    context.Companies.AddObject(c);
                    context.SaveChanges();

                    //int a = 0;
                    //int b = 5 / a;

                    Employee e = new Employee();
                    e.Age = 15;
                    e.Name = "James";
                    e.CompanyId = c.Id;
                    context.Employees.AddObject(e);
                    context.SaveChanges();

                    scope.Complete();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception Occurred");
                }
            }
        }
    }

I wanted to know if this was the right way of implementing transactions. If it is then what is the use of the SaveChanges(false) and the scope.AcceptAllChanges() functions. Any information would be helpful.

score 14 · Accepted Answer · answered Nov 26 '11 at 00:34

14

In your case you don't need to manage any connection or transaction: Entity Framework will do this for you. When you don't supply EF with an opened connection (but with a connection string), it will open a connection and start a transaction during the call to context.SaveChanges(). When something fails during that call, the transaction will be rolled back.

In other words, your method can simply look like this:

public void addCompanyToDatabase()
{
    using (var context = new myTestEntities())
    {
        Company c = new Company();
        c.Name = "xyz";
        context.Companies.AddObject(c);

        Employee e = new Employee();
        e.Age = 15;
        e.Name = "James";
        e.CompanyId = c.Id;
        context.Employees.AddObject(e);

        // Only call SaveChanges last.
        context.SaveChanges();
    }
}

answered Nov 26 '11 at 00:34

Steven

166,672
24
332
435

That makes sense in case of my example. Can you give me an example where I would need a transaction though? and if the code I mentioned above is the way to go about implementing a transaction? I guess updating records with two different contexts is one scenario. – nighthawk457 Nov 26 '11 at 00:56
1

The most common case I've encountered that makes you need a transaction, is when you need to call `SaveChanges` multiple times. This can happen when you need the (database generated) id of a new object, or when you need to force the ORM to execute operations in a certain order (for instance, LINQ to SQL tends to reorder deletes after inserts, but this may trigger a database constraint exception). – Steven Nov 26 '11 at 01:49
3

In all cases, there is still no need to use a `TransactionScope`. If you need a `TransactionScope` because you need operations to be atomic over multiple database, you have probably have a design flaw in your system. I use `TransactionScope`s purely for my automated integration tests. Just rap the calling code in a `TransactionScope` and you can make sure that all (database) changes are rolled back at the end of the test, without having to alter any code for this. – Steven Nov 26 '11 at 01:50

score 0 · Answer 2 · answered Nov 26 '11 at 00:35

1-this service(i think transaction service) must be run in client to support TransactionScope

2-Usefull when you have tow or more databases in your application and you want all databases update transactional(such as change connection string of your context).

3-When you have a database is better use SaveChanges() that internally implement transaction.

Understanding Transactions in Entity Framework

2 Answers2