Transaction with multiple queries

Question

I was visualizing that question, and to try to answer I decided to perform some tests where I could see the following problem:

When conducting multiple queries to the Database via Entity Framework within a single TransactionScope I get the following error:

The underlying server failed in Open

I went for answers and found that one, where the author says:

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

I soon imagined that I could at a certain point in my code (before a second query) check the status of the connection and reopen it, as follows:

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

But unfortunately the result did not come out as expected as a new error was displayed while trying to open the connection:

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

Why this resource is needed?
This feature is 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 separated from the database server)

Is there an alternative solution? (Perform multiple actions in a single transaction)

Excerpt from the code:

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

Answer 1

Actually this is not the problem. You just haven’t enabled support for the transaction coordinator, whose steps are here.

The code is almost all right. Just take this:

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