If you want to use
Entity Framework with
SQL Azure Federation, you should know some details... According to
this article, you have 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.
The way to do this:
1: using (var dc = new xxxxEntities())
2: {
3: ((IObjectContextAdapter)dc).ObjectContext.Connection.Open();
4: dc.Database.ExecuteSqlCommand(@"USE FEDERATION FederationName (FederationKeyName = FederationKey) WITH FILTERING = ON, RESET");
5: using (var scope = new TransactionScope (TransactionScopeOption.RequiresNew))
6: {
7: [...]
8: }
9: }
I have implemented it in a project and it works well with
Entity Framewok 5.0.0 but an exception is thrown with
Entity Framework 6.0.0-alpha3: "USE FEDERATION statement not allowed within multi-statement transaction."
I have created an issue on the Entity Framework Codeplex:
"EF 6 and SQL Azure Federations".
Entity Framework 6 is very important for SQL Azure because it will contain a system of connection resiliency like the
Transient Fault Handling Application Block can do. I hope that a solution will be found soon!
Moreover, like in
my previous article:
Don't
Repeat
Yourself!
If you want to use Entity Framework with SQL Azure Federation, take a look at this base repository class:
1: using System;
2: using System.Data.Entity;
3: using System.Data.Entity.Infrastructure;
4: using System.Transactions;
5:
6: namespace Admin.Persistence.Repositories.Impl
7: {
8: public class FederatedRepository
9: {
10: protected string FederationName { get; set; }
11: protected string FederationKeyName { get; set; }
12:
13: public FederatedRepository(string federationName, string federationKeyName)
14: {
15: FederationName = federationName;
16: FederationKeyName = federationKeyName;
17: }
18:
19: protected TK Execute<T, TK>(Func<T, TK> f, object federationKey) where T : DbContext, new()
20: {
21: TK result;
22:
23: using (var db = new T())
24: {
25: SetFederationScope(db, federationKey);
26:
27: using (var transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew))
28: {
29: result = f(db);
30: transactionScope.Complete();
31: }
32: }
33:
34: return result;
35: }
36:
37: private void SetFederationScope(DbContext dbContext, object federationKey)
38: {
39: var federationCmdText = string.Format(@"USE FEDERATION {0} ({1}={2}) WITH FILTERING=ON, RESET", FederationName, FederationKeyName, federationKey);
40: ((IObjectContextAdapter)dbContext).ObjectContext.Connection.Open();
41: dbContext.Database.ExecuteSqlCommand(federationCmdText);
42: }
43: }
44: }
The utilisation is the same than in my previous article ;-)
