Custom Execution Strategy for Connection Resiliency With Entity Framework 6 and MS-SQL Server

.net c# entity-framework entity-framework-6 sql-server

Question

With EF6 and MS-SQL Server, I have already created one application.

Everywhere I wanted to add, update, or remove data from the table in my application, I typed the code as follows:

Code:

using (DemoEntities objContext = GetDemoEntities())
{
    using (TransactionScope objTransaction = new TransactionScope())
    {

        Demo1(objContext);

        Demo2(objContext);

        // Commit the changes in the database.
        objTransaction.Complete();
    }
}

public void Demo1(DemoEntities objContext)
{
    Demo1 objDemo1 = new Demo1();
    objDemo1.Title = "ABC";

    objContext.Demo1.Add(objDemo1);

    objContext.SaveChanges();   
}

public void Demo2(DemoEntities objContext)
{
    Demo2 objDemo2 = new Demo2();
    objDemo2.Title = "ABC";

    objContext.Demo2.Add(objDemo2);

    objContext.SaveChanges();   
}

My application is operating on one server in the AWS, while the database is running on a different server.

My programme is functioning OK, however every 2-3 times I get a weak error like the one below.

System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.ComponentModel.Win32Exception: Access is denied

After immediately making another request, I did not get the aforementioned issue and the request was successful.

After some research on Google, I came up with the idea for the Connection Robustness, which I applied in my programme and it successfully retries the question at certain intervals.

But when I use my Custom Transactions, like in the code above, it doesn't work. This is how the mistake is thrown.

System.InvalidOperationException: The configured execution strategy 'MYExecutionStrategy' does not support user initiated transactions. See http://go.microsoft.com/fwlink/?LinkId=309381 for additional information.

I set up the execution strategy as follows:

public class MYExecutionStrategy : 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 MYExecutionStrategy()
    {
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="maxRetryCount"></param>
    /// <param name="maxDelay"></param>
    public MYExecutionStrategy(int maxRetryCount, TimeSpan maxDelay)
        : base(maxRetryCount, maxDelay)
    {
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="exception"></param>
    /// <returns></returns>
    protected override bool ShouldRetryOn(Exception exception)
    {
        bool bRetry = false;

        SqlException objSqlException = exception as SqlException;

        if (objSqlException != null)
        {
            List<int> lstErrorNumbersToRetry = new List<int>()
            {
                5 // SQL Server is down or not reachable
            };

            if (objSqlException.Errors.Cast<SqlError>().Any(A => lstErrorNumbersToRetry.Contains(A.Number)))
            {
                bRetry = true;
            }
        }

        return bRetry;
    }
}

DBConfiguration similarly:

/// <summary>
/// 
/// </summary>
public class MYConfiguration : DbConfiguration
{
    /// <summary>
    /// 
    /// </summary>
    public MYConfiguration()
    {
        SetExecutionStrategy("System.Data.SqlClient", () => new MYExecutionStrategy(3, TimeSpan.FromSeconds(1)));            
    }
}

Questions:

  1. How can I utilise the Connection Resiliency with the Custom Transaction in Entity Framework 6?
  2. The namespace or class for DatabaseFacade is nowhere to be found.
1
2
8/8/2017 9:03:46 AM

Accepted Answer

ZZZ_tmp
0
8/16/2017 8:30:51 AM

Popular Answer

If a connection fails during the second SaveChanges() in a custom transaction, the first SaveChanges() will also be rolled back. How would you suggest doing it again? Not at all. Because of this, EF retry only allows one SaveChanges() call per transaction.

One way ahead is to substitute a single SaveChanges() in the calling method for a transaction and eliminate the SaveChanges() from Demo1() and Demo2().

You could also manage the retry in the calling procedure by catching the exception there.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow