Using transactions with linq to entities

Question

I am a EntityFramework newbie. I am facing a very weird issue. I am not being able to use transaction using a linq to entity query. I have used for DbTransaction as well as TransactionScope. The error occurs as soon as the query is hit. I am using EF 4.3. The context derives from DbContext as usual. I am using the Code First approach.

        using (TransactionScope s = new TransactionScope())
        {
         using(var context = new XYZContext())
         {   
               context.Database.Connection.Open();
               Int32 count = (from xyz in context.XYZs
                           where xyz.Name == "SameName"
                           select xyz.Name).Count();
               Assert.AreEqual(count, 1);
          }
        }

I have some doubts. I do not want to use the TrasactionScope because I have heard it has performance issues. So how can I use the DbTransaction instead(also throwing error for me)? Why do I need to open the connection explicitly? When I am 'newing' up a context then should it not open the connection automatically for me?

Possible duplicate of http://stackoverflow.com/questions/5222734/how-to-perform-a-transaction-with-an-entity-framework-object-context?rq=1 — nick_w, Oct 11 '12 at 09:34
@Vaibhav: As per your code, i don't think you need to maintain any transaction (as its a select statement). — Dr. Rajesh Rolen, Oct 11 '12 at 09:49
@Vaibhav: i don't think you will have to open connection explicitly. it should be automatically handled by EF. Check this example: http://www.codeproject.com/Articles/418671/Using-the-Entity-Framework-4-3-in-NET-Development — Dr. Rajesh Rolen, Oct 11 '12 at 10:02
Why do you need transaction for reading? When you do SaveChanges EF will create a transaction for you... — Pawel, Oct 11 '12 at 16:06
Some clarifications. This code is part of a test method and as such transaction is needed so that the test database changes are rolled back. I am aware that opening a connection is not explicitly needed but in case you are using transaction it's needed. If I do not Open the connection then I get the error "The underlying provider failed to open". Also, this is just a general example. Some other test might be using an insert so transaction would be needed. — Kumar Vaibhav, Oct 11 '12 at 17:03

Spevy · Accepted Answer · 2012-10-11T09:42:25.233

1

Try flipping your using clauses, and opening your connection before you start the transaction, and use the context to open the transaction. You also need to commit the transaction if your code is making updates.

using(var context = new XYZContext())
  {   
      context.Database.Connection.Open();
      using (TransactionScope s = context.Connection.BeginTransaction())
      {

          Int32 count = (from xyz in context.XYZs
                           where xyz.Name == "SameName"
                           select xyz.Name).Count();
          Assert.AreEqual(count, 1);
          s.Commit()
        }
   }

edited Oct 11 '12 at 09:42

answered Oct 11 '12 at 09:33

Spevy

1,135
6
19

This solution works with the connection opening statement commented out. Also, I wanted to use DbTransaction instead of TransactionScope. – Kumar Vaibhav Oct 11 '12 at 17:15

Using transactions with linq to entities

1 Answers1