Configuring resiliency settings Entity Framework 6.02

I'm trying to configure resiliency settings for EF6.02. I have an application which in one point of execution writes a log entry to database. The application is not depending on the SQL-server so if the server does not respond, I want the application to abandon the INSERT query (through SaveChanges of the DbContext) and continue execution immediately.

Using default settings, the debug log outputs ten

"A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll"

After the ten tries, it throws an exception and my code continues. But I want just one try and for example 2 s command timeout. According to documentation on MSDN, default resiliency method for SQL server is:

DefaultSqlExecutionStrategy: this is an internal execution strategy that is used by default. This strategy does not retry at all, however, it will wrap any exceptions that could be transient to inform users that they might want to enable connection resiliency.

As the documentation mentions, this strategy does not retry at all. But still I have ten retries. I have tried to create a class which inherits DbConfiguration but I have not found any documentation on how to change this.

Can anyone help me reduce the number of retries?

UPDATE: Below is code based on suggestions

using System;
using System.Data.Entity;
using System.Data.Entity.SqlServer;
using System.Data.Entity.Infrastructure;
using System.Runtime.Remoting.Messaging;

namespace MyDbLayer
{
    public class MyConfiguration : DbConfiguration
    {
        public MyConfiguration ()
        {

          this.SetExecutionStrategy("System.Data.SqlClient", () => SuspendExecutionStrategy
          ? (IDbExecutionStrategy)new DefaultExecutionStrategy()
          : new SqlAzureExecutionStrategy());
        }

        public static bool SuspendExecutionStrategy
        {
            get
            {
                return (bool?)CallContext.LogicalGetData("SuspendExecutionStrategy") ?? false;
            }
            set
            {
                CallContext.LogicalSetData("SuspendExecutionStrategy", value);
            }
        } 
    }
}

And code writing to SQL

try
    {
        using (MyEntities context = new MyEntities ())
        {
            Log logEntry = new Log();
            logEntry.TS = DateTime.Now;

            MyConfiguration.SuspendExecutionStrategy = true;
            context.Log.Add(logEntry);
            context.SaveChanges();
        }
    }
    catch (Exception ex)
    {
        logger.Warn("Connection error with database server.", ex);
    }
    finally
    {
        //Enable retries again...
        MyConfiguration.SuspendExecutionStrategy = false;
    }

Answers


Did you try execution strategy suspension?

Your own DB configuration would be like this one:

public class MyConfiguration : DbConfiguration 
{ 
    public MyConfiguration() 
    { 
        this.SetExecutionStrategy("System.Data.SqlClient", () => SuspendExecutionStrategy 
          ? (IDbExecutionStrategy)new DefaultExecutionStrategy() 
          : new SqlAzureExecutionStrategy()); 
    } 

    public static bool SuspendExecutionStrategy 
    { 
        get 
        { 
            return (bool?)CallContext.LogicalGetData("SuspendExecutionStrategy") ?? false; 
        } 
        set 
        { 
            CallContext.LogicalSetData("SuspendExecutionStrategy", value); 
        } 
    } 
} 

then you may define non-retriable command like this:

public static void ExecWithoutRetry(System.Action action)
{
    var restoreExecutionStrategyState = EbgDbConfiguration.SuspendExecutionStrategy;
    try
    {
        MyConfiguration.SuspendExecutionStrategy = true;
        action();
    }
    catch (Exception)
    {
        // ignore any exception if we want to
    }
    finally
    {
        MyConfiguration.SuspendExecutionStrategy = restoreExecutionStrategyState;
    }
}

and finally your regular DB code with retriable and non-retriable commands may look like this:

using (var db = new MyContext())
{
    ExecWithoutRetry(() => db.WriteEvent("My event without retries"));
    db.DoAnyOperationWithRetryStrategy();
}

I found it. I just needed to add "Connection Timeout=X" where X is seconds to the connection string and everything worked without needing to modify ExecutionStrategies etc.

Adding this before executing query solved it

contextInstance.Database.Connection.ConnectionString = context.Database.Connection.ConnectionString + ";Connection Timeout=2;";

Need Your Help

Add registry key to HKCU RunOnce for every user from Visual Studio setup project

c# registry windows-installer

I need to install an application that will run once for any user that logs in who has a profile on a Windows installation. I don't need it to run for new users. I am using a Visual Studio 2010 Setup

Using google custom search to find the number of indexed urls

php google-api google-custom-search

I am looking for a good way of finding how many pages from my site are indexed in google. I have been looking at the google custom search api. Below is the code they give in the sample documents.