SalesBuy
1-855-856-7678
Technical SupportSupport
Transactions with Entity Framework in Federated Databases
Transactions with Entity Framework in Federated Databases
In an Entity Framework application, transaction scope must be set after the USE FEDERATION command has been executed.
Authors: James Podgorski and Rick Saling
Background
This case follows the same pattern as the preceding topics.
Correct Usage
The following code snippet contains the correction in which the connection was explicitly opened and the USE FEDERATION command submitted outside the transaction scope. This feels atypical for Entity Framework developers because MSDN books online references the techniques used in the previous code sample. Nevertheless this is necessary to ensure that the USE FEDERATION command has rerouted the connection to the appropriate federated member before an active transaction is established on the connection.
using (SalesEntities dc = new SalesEntities(ConnStr))
{
((IObjectContextAdapter)dc).ObjectContext.Connection.Open();
dc.Database.ExecuteSqlCommand(@"USE FEDERATION FED_1 (range_id=0) WITH FILTERING=OFF, RESET");
using (TransactionScope scope = new TransactionScope (TransactionScopeOption.RequiresNew, options))
{
Order order = (from x in dc.Orders
select x).FirstOrDefault();
order.total_cost = order.total_cost + 1;
dc.SaveChanges();
scope.Complete();
}
Incorrect Usage
Caution: The following code is an illustration of the improper way to utilize the System.Transactions.TransactionScope class with SQL Azure Federations to implicitly scope the EF Code-First queries submitted to the database server.A SqlException is thrown upon execution of the text dc.Database.ExecuteSqlCommand(…) and submittal of the USE FEDERATION command to the SQL Azure. This is because the USE FEDERATION command cannot be executed on a connection with an active transaction. Thus, transactions must be initiated after the USE FEDERATION command has rerouted the connection to the appropriate federated member. In the preceding section, the USE FEDERATION command was scoped outside of the transaction to correct the problem.
using (TransactionScope scope = new TransactionScope (TransactionScopeOption.RequiresNew, options))
{
using (SalesEntities dc = new SalesEntities(ConnStr))
{
((IObjectContextAdapter)dc).ObjectContext.Connection.Open();
dc.Database.ExecuteSqlCommand(@"USE FEDERATION FED_1 (range_id=0) WITH FILTERING=OFF, RESET");
Order order = (from x in dc.Orders
select x).FirstOrDefault();
order.total_cost = order.total_cost + 1;
dc.SaveChanges();
scope.Complete();
}
}
This code will throw the following exception.
System.Transactions.TransactionInDoubtException: The transaction is in doubt. System.Data.SqlClient.SqlException: The server failed to resume the transaction. Desc:60800000001. The transaction active in this session has been committed or aborted by another session.
