Thursday, October 4, 2012

SQL Azure and Entity Framework

Recently I was asked by a friend “How to deal the Transient Fault handling framework against SQL Azure while using Entity Framework?”. How really?

Here are a bunch of resources that describe in detail what the Transient faults are, how to deal with them, and in particular how to use the TFHF (Transient Fault Handling Framework) along with Entity Framework:

http://blogs.msdn.com/b/appfabriccat/archive/2010/12/11/sql-azure-and-entity-framework-connection-fault-handling.aspx

http://blogs.msdn.com/b/appfabriccat/archive/2010/10/28/best-practices-for-handling-transient-conditions-in-sql-azure-client-applications.aspx

http://windowsazurecat.com/2010/10/best-practices-for-handling-transient-conditions-in-sql-azure-client-applications/

A concrete sample from the Windows Azure CAT (CAT states for Customer Advisory Team) team site:

// Define the order ID for the order we want.
int orderId = 43680;

// Create an EntityConnection.
EntityConnection conn = new EntityConnection("name=AdventureWorksEntities");

// Create a long-running context with the connection.
AdventureWorksEntities context = new AdventureWorksEntities(conn);

try
{
// Explicitly open the connection inside a retry-aware scope.
sqlAzureRetryPolicy.ExecuteAction(() =>
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
});

// Execute a query to return an order. Use a retry-aware scope for reliability.
SalesOrderHeader order = sqlAzureRetryPolicy.ExecuteAction<SalesOrderHeader>(() =>
{
return context.SalesOrderHeaders.Where("it.SalesOrderID = @orderId",
new ObjectParameter("orderId", orderId)).Execute(MergeOption.AppendOnly).First();
});

// Change the status of the order.
order.Status = 1;

// Delete the first item in the order.
context.DeleteObject(order.SalesOrderDetails.First());

// Save changes inside a retry-aware scope.
sqlAzureRetryPolicy.ExecuteAction(() => { context.SaveChanges(); });

SalesOrderDetail detail = new SalesOrderDetail
{
SalesOrderID = 1,
SalesOrderDetailID = 0,
OrderQty = 2,
ProductID = 750,
SpecialOfferID = 1,
UnitPrice = (
decimal)2171.2942,
UnitPriceDiscount = 0,
LineTotal = 0,
rowguid =
Guid.NewGuid(),
ModifiedDate =
DateTime.Now
};

order.SalesOrderDetails.Add(detail);

// Save changes again inside a retry-aware scope.
sqlAzureRetryPolicy.ExecuteAction(() => { context.SaveChanges(); });
}
finally
{
// Explicitly dispose of the context and the connection.
context.Dispose();
conn.Dispose();
}

Well, this is the raw source provided. To be hones, I would extract it / encapsulate in some more generalized way (for instance create some Extension methods to call for all CRUD operations; or even better – create my own DataService on top of the EF, so my code will never work with the bare boned EF context, but some contract instead.

1 comment:

Robert Moore said...

The problem with this approach is that you need to change wrap every database call in your application.

EF6 includes in-built support for retrying, but that's still in alpha.

I've created a library which allows you to configure Entity Framework to retry using the Fault Handling block without needing to change every database call - generally you will only need to change your config file and possibly one or two lines of code.

This allows you to use it for Entity Framework < v6 or Linq To Sql.

https://github.com/robdmoore/ReliableDbProvider