I currently use SQL Azure and Entity SQL in my application.
e.g.
Entities model = new Entities();
db_Item item = model.db_Item.First();
Now I want to use the Transient Fault Handling out of the Enterprise Library but there are no examples or solutions that I can find that would allow me to do something like override the Entities class, so I don't have to update my code in hundreds of places.
Could someone please provide more information on how this could be done?
Going through what I have so far.
The Entity Framework does not provide access to the connection open and section where the SQL is sent to the server, hence it is currently impossibile to provide retry logic around this area.
The EF team are aware of this shortfall and are planning on actually integrating retry logic into EF for possibily version 6.
As per Case #3 of [1] you can send a SQL command to the database on the OnContextCreated. This however means for EVERY single DB call you make to the DB, you will have to make 2. I wouldn't recommend this in hardly any situation unless you don't care about performance.
The only viable option so far is implementing retry logic in the form of the Enterprise Library Transient Fault Handling Application Block [2] around every call you make to the database. In existing applications this is extremely tedious.
When I get time I am looking further into the source code of EF to see if anything further can be done, while we wait for EF 6. I would keep an eye on [3]
Some hope, it is currently under review by the EF team. [4]
Update: 2013-11-14
Just thought I would update this post to let everyone know that EF6 has been released and supports connection resiliency out of the box. https://www.nuget.org/packages/EntityFramework/
No need for workarounds any more.
Update: 2013-03-23
EF 6 Alpha 3 released with Connection Resiliency - http://entityframework.codeplex.com/wikipage?title=Connection%20Resiliency%20Spec
Update: 2012-11-04
The EF team have officially announced it is planned for EF 6. [4]
[2] http://msdn.microsoft.com/en-us/library/hh680934(v=pandp.50).aspx
[3] http://entityframework.codeplex.com/wikipage?title=Roadmap
Thankfully it is pretty simple with the new Transient Fault Handling Application block. Everything you need can be found here:
and in video form:
http://channel9.msdn.com/Shows/Cloud+Cover/Episode-68-Throttling-in-SQL-Azure-with-Scott-Klein
An example from the above links:
using (NorthwindEntities dc = new NorthwindEntities())
{
RetryPolicy myPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(3);
Employee e1 = myPolicy.ExecuteAction<Employee>(() =>
(from x in dc.Employees
where x.LastName == "King"
select x).First());
}
As you can see, all you need to do is create a RetryPolicy and call its ExecuteAction function with a query wrapped in an action.
**EDIT
Example context overrides:
private RetryPolicy m_RetryPolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(....
public override int SaveChanges()
{
return m_RetryPolicy.ExecuteAction<int>(() =>
{
return base.SaveChanges();
});
}
// Pass anonymous query func in here
public T AutoRetryQuery<T>(Func<T> query)
{
return m_RetryPolicy.ExecuteAction<T>(query);
}
Since this seems one of the most popular questions on SO about Azure transient handling, I'll add this answer here.
Entity Framework does indeed have resiliency code built in (per Adam's answer)
BUT:
1) You must add code to activate it, manually
public class MyConfiguration : DbConfiguration
{
public MyConfiguration()
{
this.SetExecutionStrategy(
"System.Data.SqlClient",
() => new SqlAzureExecutionStrategy());
this.SetTransactionHandler(
SqlProviderServices.ProviderInvariantName,
() => new CommitFailureHandler());
}
...
The first method call activates retries, the second call sets a handler to avoid duplicate updates when retries happen.
Note: This class will be found and instantiated automatically, as discussed here: https://msdn.microsoft.com/en-us/library/jj680699(v=vs.113).aspx. Just make sure the class is in the same assembly as your DbContext class and has a public constructor with no parameters.
2) The built-in SqlAzureExecutionStrategy is not good enough. It doesn't cover all the transient errors. This is not surprising when you consider that the SQL Server team is working independently of Entity Framework, so they are unlikely to ever be completely in synch on what transient errors are possible. It's also difficult to figure that out yourself.
The solution we used, backed by a suggestion from another software company, is to create our own Execution Strategy, which retries every SqlException and TimeoutException, except for a few that we whitelist as not worth retrying (such as permission denied).
public class WhiteListSqlAzureExecutionStrategy : DbExecutionStrategy
{
public WhiteListSqlAzureExecutionStrategy()
{
}
protected override bool ShouldRetryOn(Exception exception)
{
var sqlException = exception as SqlException;
// If this is an SqlException then we want to always retry
// Unless the all the exception types are in the white list.
// With those errors there is no point in retrying.
if (sqlException != null)
{
var retry = false;
foreach (SqlError err in sqlException.Errors)
{
// Exception white list.
switch (err.Number)
{
// Primary Key violation
// https://msdn.microsoft.com/en-us/library/ms151757(v=sql.100).aspx
case 2627:
// Constraint violation
case 547:
// Invalid column name, We have seen this happen when the Snapshot helper runs for a column 'CreatedOn'
// This is not one of our columns and it appears to be using our execution strategy.
// An invalid column is also something that probably doesn't get resolved by retries.
case 207:
break;
// The server principal "username" is not able to access the database "dbname" under the current security context
// May occur when using restricted user - Entity Framework wants to access master for something
// probably not transient
case 916:
break;
// XXX permission denied on object. (XXX = select, etc)
// Should not occur if db access is correct, but occurred when using restricted user - EF accessing __MigrationHistory
case 229:
break;
// Invalid object name 'xxx'.
// Occurs at startup because Entity Framework looks for EdmMetadata, an old table
// (Perhaps only if it can't access __MigrationHistory?)
case 208:
break;
default:
retry = true;
break;
}
}
return retry;
}
if (exception is TimeoutException)
{
return true;
}
return false;
}
}
3) There used to be a kind of bug where EF would run the retries N^2 times instead of N, which made for much longer delays than you'd expect. (It's supposed to take up to about 26 seconds, but the bug made it take minutes.) However, this isn't so bad because in reality SQL Azure regularly has unavailability for more than a minute :( https://entityframework.codeplex.com/workitem/2849
4) If you haven't been doing so already, you really need to dispose of your DbContext after it's used. It seems this is the point that the CommitFailureHandler runs it's purging to tidy up the __TransactionHistory table; if you don't dispose, this table will grow forever (although see next point).
5) You should probably call ClearTransactionHistory somewhere in your startup or in a background thread, to clear any leftovers in __TransactionHistory.
I was also having same issue, In my case I am using LINQ-To-SQL(I believe same applies to Entity framework also)and this is what I am planning to do.
Abstract the Linq-to-sql queries using repository pattern and wrap the Linq-to-sql code which generates database request with the retry mechanism.
For that I would suggest to create a method called ExecuteWithRetry(Action action) and then you invoke this method like this ExecuteWithRetry(()=> { db_Item item = model.db_Item.First(); });
The method could be implemented this way
private void ExecuteWithRetry(Action action)
{
var retryStrategy = new Incremental(5, TimeSpan.FromSeconds(1),
TimeSpan.FromSeconds(2));
// Define your retry policy using the retry strategy and the Windows Azure storage
// transient fault detection strategy.
var retryPolicy =
new RetryPolicy<StorageTransientErrorDetectionStrategy>(retryStrategy);
// Receive notifications about retries.
retryPolicy.Retrying += (sender, args) =>
{
// Log details of the retry.
var msg = String.Format("Retry - Count:{0}, Delay:{1}, Exception:{2}",
args.CurrentRetryCount, args.Delay, args.LastException);
Trace.WriteLine(msg, "Information");
};
try
{
this.retryPolicy.ExecuteAction(action);
}
catch (Exception ex)
{
Trace.TraceError(ex.TraceInformation());
throw;
}
}
I am also going to try this approach, hoping that it will work
Actually you want to use extension methods provided by MS. Ref: http://msdn.microsoft.com/en-us/library/hh680906(v=pandp.50).aspx
"In addition, the SqlCommandExtensions and SqlConnectionExtensions classes provide a set of extension methods that enable .NET developers to open SQL Azure database connections and invoke the SQL commands. These extension methods are useful in the event that you are unable to adapt your code to take advantage of the ReliableSqlConnection class. For example, you may be using the Enterprise Library Data Access Application Block or Entity Framework that use SqlConnection instances internally. In this case, the extension methods help you add the retry capabilities offered by the Transient Fault Handling Application Block to the existing code without major re-work.
"
