Alternative to the Transactionscope

Question

I’m using Entity Framework (version 4.4) in a project Aspnet MVC with .NET Framework a little old (version 4.0) and SQL Server.

I do not have access to the server where the application is hosted, and from time to time it undergoes some migrations to other servers, so I was instructed to avoid using techniques that make it necessary to have some extra resource (which is not by default).

I am developing a functionality in the application that needs a transaction, but by default the EF closes the connection in the call of the SaveChanges(), and thus receive a exception when trying to make any new request through my object Entities receive the following Exception:

The underlying provider failed in Open.

I then tried to instantiate my context at different times for each change, but the same error persists.

using(TransactionScope ts = new System.Transactions.TransactionScope())
{
    var teste1 = new Teste();
    using (var context = new MyEntities())
    {
        teste1.Nome = "Teste1";
        context.Add(teste1);
        context.SaveChanges();
    }

    // Até aqui tudo está funcionando bem!

    using (var context = new MyEntities())
    {
        //Erro nessa linha!
        var usuario = context.User.SingleOrDefault(u => u.UserId == ViewSessionContext.UserId);

        if(usuario != null)
        {
           var teste2 = new FilhoTeste();
           teste2.Nome = "FilhoTeste";
           teste2.TesteId = teste1.Id;
           teste2.UserId = usuario.Id;
           teste2.Add(teste2);
           context.SaveChanges();
        }
    }

    ts.Complete();

}

It would be possible to use a transaction without having to enable the DTC?

I also tried to force the connection open, but I received the same error!

Research sources:

• https://stackoverflow.com/questions/2475008/mssql-error-the-underlying-provider-failed-on-open
• https://stackoverflow.com/questions/794707/why-doesnt-transactionscope-work-with-entity-framework
• https://stackoverflow.com/questions/794364/how-do-i-use-transactionscope-in-c

Answer 1

There is no need to open two contexts within a transaction scope. Do this using the same context:

using (var ts = new System.Transactions.TransactionScope())
{
    using (var context = new MyEntities())
    {
        var teste1 = context.Teste.SingleOrDefault(t => t.Id = 1);
        teste1.Nome = "Teste1";
        context.Entry(teste1).State = EntityState.Modified;

        var teste2 = context.Teste.SingleOrDefault(t => t.Id = 2);
        teste2.Nome = "Teste2";
        context.Entry(teste2).State = EntityState.Modified;

        context.SaveChanges();
    }

    ts.Complete();
}

Reasoning is analogous when there is dependence between objects:

using (var ts = new System.Transactions.TransactionScope())
{
    var teste1 = new Teste();
    using (var context = new MyEntities())
    {
        teste1.Nome = "Teste1";
        context.Add(teste1);
        context.SaveChanges();

        var usuario = context.User.SingleOrDefault(u => u.UserId == ViewSessionContext.UserId);

        if(usuario != null)
        {
           var teste2 = new FilhoTeste();
           teste2.Nome = "FilhoTeste";
           teste2.TesteId = teste1.Id;
           teste2.UserId = usuario.Id;
           teste2.Add(teste2);

           context.SaveChanges();
        }
    }

    ts.Complete();    
}