Entity Framework Connections and Federations

When you use Federations to scale-out a SQL Database, tables are partitioned according to a specified key, and each partition is referred to as a Federation Member. When you issue a TSQL command to query or change rows in a table, you must first issue a USE FEDERATION command that specifies the Federation Member to be used.

In Entity Framework, when you use the Code First development methodology, you must ensure that the same database connection used to issue the USE FEDERATION command is also used to issue the query or change command. There are a number of pitfalls that may prevent this from happening. Windows Azure SQL Database Federations with Entity Framework Code-First provides sample code that creates a federated SQL Database, and which illustrates and deals with the following issues:

1. The USE FEDERATION statement can execute in a different TSQL batch than the other commands, resulting in the commands running against the wrong partition.
   
   
2. dbContext and its underlying ObjectContext use different connection types to access SQL Database, which can cause an exception to be thrown.
   
   

Multiple Active Record Sets (“MARS”) is not supported in SQL Database Federations. Certain Entity Framework database operations make use of MARS, and so they work with on-premise SQL Servers, but fail when run against SQL Database. Understanding Windows Azure SQL Database Federations No-MARS Support and Entity Framework provides an explanation, describes the operations that will fail, and suggests ways to circumvent this limitation.