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

Answer 1

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();
}