Transaction Scope with Entity

Question

I have a windows form application with .NET 4 and Entity Framework for data layer I need one method with transaction, but making simple tests I couldn't make it work

In BLL:

public int Insert(List<Estrutura> lista)
{
    using (TransactionScope scope = new TransactionScope())
    {
            id = this._dal.Insert(lista);
    }
}

In DAL:

public int Insert(List<Estrutura> lista)
{
   using (Entities ctx = new Entities (ConnectionType.Custom))
   {
     ctx.AddToEstrutura(lista);
     ctx.SaveChanges(); //<---exception is thrown here
   }
}

"The underlying provider failed on Open."

Anyone have any ideas?

PROBLEM RESOLVED - MY SOLUTION

I solved my problem doing some changes. In one of my DAL I use a Bulk Insert and others Entity. The problem transaction was occurring by the fact that the bulk of the transaction (transaction sql) do not understand a transaction scope So I separated the Entity in DAL and used the sql transaction in its running some trivial. ExecuteScalar ();

I believe that is not the most elegant way to do this, but solved my problem transaction.

Here is the code of my DAL

   using (SqlConnection sourceConnection = new SqlConnection(Utils.ConnectionString()))
   {
        sourceConnection.Open();
        using (SqlTransaction transaction = sourceConnection.BeginTransaction())
        {
            StringBuilder query = new StringBuilder();
            query.Append("INSERT INTO...");
            SqlCommand command = new SqlCommand(query.ToString(), sourceConnection, transaction);
            using (SqlBulkCopy bulk = new SqlBulkCopy(sourceConnection, SqlBulkCopyOptions.KeepNulls, transaction))
            {                           
                bulk.BulkCopyTimeout = int.MaxValue;
                bulk.DestinationTableName = "TABLE_NAME";
                bulk.WriteToServer(myDataTable);

                StringBuilder updateQuery = new StringBuilder();
                //another simple insert or update can be performed here
                updateQuery.Append("UPDATE... ");
                command.CommandText = updateQuery.ToString();
                command.Parameters.Clear();
                command.Parameters.AddWithValue("@SOME_PARAM", DateTime.Now);
                command.ExecuteNonQuery();
                transaction.Commit();
            }
        }
    }

thanks for the help

possible duplicate of [The underlying provider failed on Open](http://stackoverflow.com/questions/2475008/the-underlying-provider-failed-on-open) Has some good suggestions relating to connections/transactions/DTC — Eoin Campbell, May 22 '12 at 13:42
You are using an anti-pattern here. Treat the ObjectContext as a unit-of-work. — usr, May 22 '12 at 20:22

SPFiredrake · Answer 1 · 2012-05-22T20:06:57.910

According to the all-mighty Google, it seems that EF will open/close connections with each call to a database. Since it's doing that, it will treat the transaction as using multiple connections (using a distributed transaction). The way to get around this is to open and close the connection manually when using it.

Here's the information on the distributed transactions issue.

Here's how to manually open and close the connection.

A small code sample:

public int Insert(List<Estrutura> lista)
{
    using (TransactionScope scope = new TransactionScope())
    {
        using (Entities ctx = new Entities (ConnectionType.Custom))
        {
            ctx.Connection.Open()

            id = this._dal.Insert(ctx, lista);
        }
    }
}

public int Insert(Entities ctx, List<Estrutura> lista)
{
     ctx.AddToEstrutura(lista);
     ctx.SaveChanges();
}

daryal · Answer 2 · 2012-05-22T14:14:44.307

-1

Instead of employing TransactionScope, it is better to employ UnitOfWork pattern while working with entity framework. please refer to: unit of work pattern

and also;

unit of work and persistance ignorance

edited May 22 '12 at 14:14

answered May 22 '12 at 13:41

daryal

14,643
4
38
54

The `UnitOfWork` pattern is not a replacement solution for `TransactionScope` – Eoin Campbell May 22 '12 at 13:43
@EoinCampbell it is not a replacement, but you can employ UnitOfWork pattern like a transactionscope; can you clarify why do you think so? – daryal May 22 '12 at 13:45
Well for starters he instantiates his `TransactionScope` in his application layer and not in his DAL so it's not clear if his code is indicative of reality or if that TS possibly encompasses other functionality. Secondly, the `UOW` pattern is only of use if you've designed your DAL to support it i.e. Individual Repositories capable of taking an ObjectContext instance during construction. So your suggesting a pretty drastic overhaul instead of solving the problem he's posted. And finally but probably most important... You can't roll back a UnitOfWork unless your using... Transactions – Eoin Campbell May 22 '12 at 14:00
You may be right there is no direct answer. On the otherhand; UnitOfWork pattern is directly used in transaction management. Whether entity framework or nhibernate; the definition of unitofwork pattern by martin fowler states it is to be used for transaction management. m. fowler states: "maintains a list of objects affected by a business transaction and coordinates the writing out of changes and the resolution of concurrency problems.". – daryal May 22 '12 at 14:11
Also, you stated that you can not rollback a unitofwork unless using transactions; well it depends since you can not rollback the database changes when you commit transaction scope. The same rule applies for unit of work. In the unit of work scope, you can choose not to apply changes to db. Nhibernate applies UnitOfWork pattern using ITransaction interface, which clearly states it is for transaction management. The other parts of the comments about the not being a direct answer, you are right anyway. – daryal May 22 '12 at 14:13
just to note, if the case of EF, if you have more than one change in your unit of work, and don`t use a transaction, you might end up in an inconsistent state, if (when) a failure occurs. – Menahem Jan 30 '13 at 09:22
@Menahem can you clarify? AFAIK if there is any failure no changes will be reflected in DB? – daryal Jan 30 '13 at 09:55
`SaveChanges()` opens a `TransactionScope` inside it, so any changes that are not in done by it (i.e. if you call it in a wrapper class that does other changes too.) are not atomic. – Menahem Feb 04 '13 at 12:18

Transaction Scope with Entity

2 Answers2