Transaction with multiple queries

2

I was viewing this question , and to try to answer I decided to run some tests where I could see the following problem:

When performing multiple database queries via Entity Framework within a single TransactionScope I get the following error:

  

The underlying server failed in Open

I went after answers and found this , where the author says:

"If you are using the Entity Framework with transactions, Entity Framework automatically opens and closes a connection to each database call. So when you are using transactions, you are trying to spread a transaction over multiple connections. This raises to MSDTC. " - Free Translation.

Soon I imagined that I could check the state of the connection and reopen it, in a certain part of my code (before a second query), as follows:

if (context.Database.Connection.State != ConnectionState.Open)
{
    context.Database.Connection.Open();
}

But unfortunately the result did not go as expected, because a new error was displayed when trying to open the connection:

  

Network access for Distributed Transaction Manager (MSDTC) has been disabled. Enable DTC network access in the MSDTC security configuration using the Component Services Administrative tool.

Why is this feature necessary? Is this feature on the client or server side? When deploying my application to the official server will I have to enable something there? (My application server is separate from the database server)

Is there a workaround? (Perform multiple actions in a single transaction)

Code snippet:

using (MeuEntities context = new MeuEntities())
{
    using (TransactionScope scope = new TransactionScope())
    {
        int ordemAlternativa = 1;

        if (questaoAlternativa.RespostaCorreta == true)
        {
            var alternativasDaQuestao = context.QuestaoAlternativa.Where(qa => qa.QuestaoId == questaoAlternativa.QuestaoId);
            if (alternativasQuestao != null && alternativasQuestao.Count() > 0)
            {
                foreach (var item in alternativasQuestao)
                {
                    item.RespostaCorreta = false;
                    context.Entry(item).State = EntityState.Modified;
                }

                ordemAlternativa = alternativasQuestao.Max(qa => qa.Prioridade == null ? 1 : (qa.Prioridade + 1));
            }
        }

        questaoAlternativa.Prioridade = ordemAlternativa;
        context.QuestaoAlternativa.Add(questaoAlternativa);

        // Código adicional para reabrir conexão
        if (context.Database.Connection.State != ConnectionState.Open)
        {
            context.Database.Connection.Open();
        }

        // Erro ao realizar consulta
        int prioridadeArquivo = context.QuestaoAlternativaImagem.DefaultIfEmpty().Max(qai => qai.Prioridade == null ? 0 : qai.Prioridade);

        for (int i = 0; i < Request.Files.Count; i++)
        {
            prioridadeArquivo++;

            byte[] fileData = new byte[Request.Files[i].InputStream.Length];
            Request.Files[i].InputStream.Read(fileData, 0, Convert.ToInt32(Request.Files[i].InputStream.Length));
            QuestaoAlternativaImagem questaoAlternativaImagem = new QuestaoAlternativaImagem() { NomeArquivo = Path.GetFileName(Request.Files[i].FileName), Descricao = Path.GetFileName(Request.Files[i].FileName), Prioridade = prioridadeArquivo, Imagem = fileData, QuestaoAlternativaId = questaoAlternativa.QuestaoAlternativaId, Guid = Guid.NewGuid() };
            context.QuestaoAlternativaImagem.Add(questaoAlternativaImagem);
        }

        context.SaveChanges();
        scope.Complete();
    }
}
    
asked by anonymous 08.06.2016 / 22:06

1 answer

1

Actually the problem is not this. You just did not enable support to the transaction coordinator whose steps are here .

The code is almost entirely right. You just have to take this out:

    // Código adicional para reabrir conexão
    if (context.Database.Connection.State != ConnectionState.Open)
    {
        context.Database.Connection.Open();
    }
    
08.06.2016 / 22:49