Entity Framework with SQL Azure Federation

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 ;-)