Only allows 5 attempts at key duplication Entityframework? a 6 badge in Savechange()

Question

Key duplication in write with Entityframework in competition with many users only allows 5 attempts and 6 badge in Savechange() (example 1).

If it is with Sqlconnection it does not give any problem (example 2).

Example 1:

Public Sub AdicionaAux(ByRef Tentativas As Integer)
        Dim inBlnRepete As Boolean = False
        Try
            Using ctx As New BD.Dinamica.Aplicacao
                Using trans As DbContextTransaction = ctx.Database.BeginTransaction(IsolationLevel.RepeatableRead)
                    Try
                        Dim t As tbArtigos = ctx.tbArtigos.FirstOrDefault
                        ctx.tbArtigos.Attach(t)
                        ctx.Entry(t).State = EntityState.Added
                        ctx.SaveChanges()
                    Catch ex As Exception
                        trans.Rollback()
                        Throw
                    End Try
                End Using
            End Using
        Catch
            inBlnRepete = True 'RepeteFuncaoPorConcorrencia(ex, Tentativas)
            If inBlnRepete And Tentativas < 5 Then
                AdicionaAux(Tentativas)
            Else
                Throw
            End If
        End Try
    End Sub

Example 2:

 Public Sub AdicionaAux(ByRef Tentativas As Integer)
        Try
            Dim appServidorSQL As String = ConfigurationManager.AppSettings("ServidorSQL")
            Dim appInstanciaSQL As String = ConfigurationManager.AppSettings("InstanciaSQL")
            Dim appBDEmpresa As String = ConfigurationManager.AppSettings("BDEmpresa")
            Using connection As New SqlConnection("Server=" & appServidorSQL & "\" & appInstanciaSQL & ";User ID=sa;Initial Catalog=" & appBDEmpresa & ";")
                connection.Open()
                Dim command As SqlCommand = connection.CreateCommand()
                Dim transaction As SqlTransaction
                transaction = connection.BeginTransaction("SampleTransaction")
                command.Connection = connection
                command.Transaction = transaction
                Try
                    command.CommandText = _
                      "Insert into tbDestinos (Codigo, Descricao, Ativo, Sistema, DataCriacao, UtilizadorCriacao) VALUES ('POR', 'Description',1,1,getdate(),'xxx')"
                    command.ExecuteNonQuery()
                    transaction.Commit()
                Catch ex As Exception
                    transaction.Rollback()
                    Throw
                End Try
            End Using
        Catch
            AdicionaAux(Tentativas)
        End Try
    End Sub

Answer 1

The solution is here:

https://www.codeproject.com/Tips/758469/Implementing-Connection-Resiliency-with-Entity-Fra

public class PharylonExecutionStrategy : DbExecutionStrategy
    {
        /// <summary>
        /// The default retry limit is 5, which means that the total amount of time spent 
        /// between retries is 26 seconds plus the random factor.
        /// </summary>
        public PharylonExecutionStrategy()
        {
        }

        /// <summary>
        /// Creates a new instance of "PharylonExecutionStrategy" with the specified limits for
        /// number of retries and the delay between retries.
        /// </summary>
        /// <param name="maxRetryCount"> The maximum number of retry attempts. </param>
        /// <param name="maxDelay"> The maximum delay in milliseconds between retries. </param>
        public PharylonExecutionStrategy(int maxRetryCount, TimeSpan maxDelay)
            : base(maxRetryCount, maxDelay)
        {
        }

        protected override bool ShouldRetryOn(Exception ex)
        {
            bool retry = false;

            SqlException sqlException = ex as SqlException;
            if (sqlException != null)
            {
                int[] errorsToRetry =
                {
                    1205,  //Deadlock
                    -2,    //Timeout
                    2601  //primary key violation. Normally you wouldn't want to retry these, 
                          //but some procs in my database can cause it, because it's a crappy 
                          //legacy junkpile.
                };
                if (sqlException.Errors.Cast<SqlError>().Any(x => errorsToRetry.Contains(x.Number)))
                {
                    retry = true;
                }
                else
                {
                    //Add some error logging on this line for errors we aren't retrying.
                    //Make sure you record the Number property of sqlError. 
                    //If you see an error pop up that you want to retry, you can look in 
                    //your log and add that number to the list above.
                }
            }
            if (ex is TimeoutException)
            {
                retry = true;
            }
            return retry;
        }
    } 

Answer 2

The opening of the transactional scope is wrong. You do not open the scope from the connection to the database: open from the own . NET, because object handling is not a purely database issue.

The right thing would be something like this:

Public Sub AdicionaAux(ByRef Tentativas As Integer)
    Dim inBlnRepete As Boolean = False
    Try
        Using ctx As New BD.Dinamica.Aplicacao
            'Mude aqui
            Using trans As New TransactionScope(TransactionScopeAsyncFlowOption.Enabled)
                'Não use Try com contexto. Intercepte exceções nos eventos OnException 
                'do Controller ou SaveChanges e SaveChangesAsync do contexto.
                'Try
                Dim t As tbArtigos = ctx.tbArtigos.FirstOrDefault
                'Não entendi por que você está selecionando o registro e fazendo Attach nele.
                'Apenas a seleção já faz o registro ser observado, então estou comentando esta linha.
                'ctx.tbArtigos.Attach(t)
                ctx.Entry(t).State = EntityState.Added
                ctx.SaveChanges()
                trans.Complete()
                'Catch ex As Exception
                '    trans.Rollback()
                '    Throw
                'End Try
            End Using
        End Using
    'Considere retirar este Catch também
    Catch
        inBlnRepete = True 'RepeteFuncaoPorConcorrencia(ex, Tentativas)
        If inBlnRepete And Tentativas < 5 Then
            AdicionaAux(Tentativas)
        Else
            Throw
        End If
    End Try
End Sub

The Entity Framework is not a 100% dedicated SQL ORM, or a type of relational technology involving SQL: it is a framework agnostic. In addition, the support to multithreading is not safe natively: it takes an extra effort of the programmer for critical regions.