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