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

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

Question

I already developed one application with the EF6 and MS-SQL Server.

Everywhere in my application I wrote the code like below where I needed to Insert, Update or delete the data from the table:

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 running on the one server and database is running on the another server in the AWS.

My application is working smoothly, But 2-3 times weakly I got the error like the 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

In first request I got the above error and after the immediate another request I does not get any error and request is successful.

After doing some Google I got the concept like the Connection Resiliency I implemented in my application and it works and retry the query for some specific times after some specific period.

But it fails in the case of the where I used my Custom Transactions like in the above code. It throws the error like this.

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 configured the Execution Strategy like this:

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

And DBConfiguration like this:

/// <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 use the Connection Resiliency in the Entity Framework 6 With the Custom Transaction.
  2. I was unable to find the DatabaseFacade class or namespace.
1
2
8/8/2017 9:03:46 AM

Accepted Answer

So after doing some RnD and reading around the WEB I found the Solution like this:

var str = MYExecutionStrategy(3, TimeSpan.FromSeconds(1));

    str.Execute(() =>
    {
        using (DemoEntities objContext = GetWDemoEntities ())
        {
            using (TransactionScope obj = new TransactionScope())
            {
                Demo1 objDemo1 = new Demo1();
                objDemo1.Title = "ABC";

                objContext.Demo1.Add(objDemo1);     
                objContext.SaveChanges(); // First SaveChanges() method called.

                Demo2 objDemo2 = new Demo2();
                objDemo2.Title = "ABC";

                objContext.Demo2.Add(objDemo2);     
                objContext.SaveChanges();// Second SaveChanges() method called.

                obj.Complete();
            }
        }
    }
0
8/16/2017 8:30:51 AM

Popular Answer

With a custom transaction if you get a connection failure on the second SaveChanges(), the first SaveChanges() will also be rolled back. How do you propose to retry that? You can't. That's why EF retry only supports a single SaveChanges() per transaction.

One way forward is to remove the SaveChanges() from Demo1() and Demo2(), and have a single SaveChanges() in the calling method instead of a transaction.

Another way is for you to catch the exception in the calling method and orchestrate the retry 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