Kalpesh Rajai Kalpesh Rajai - 2 years ago 205
C# Question

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

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.


Answer Source

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();
            }
        }
    }
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download