SQL Azure Federations with Entity Framework Code-First

A number of our SQL Azure Federation product evaluation customers have been inquiring about the typical how-to’s for using Entity Framework with this new SQL Azure capability. After providing guidance to a number of these customers it was apparent that I could deliver a jumpstart of sorts for those common scenarios and questions so as to broaden the community reach and awareness. My intent is to release a series of postings with examples and various layers of depth as I tick off some of the common points customers are facing when using Entity Framework with SQL Azure Federations.

In this blog I will provide a slight boost for those wanting to use the Code First feature of Entity Framework (EF) with SQL Azure Federations. The sample I demonstrate in this posting is a basic quintessential ‘hello world’ example for Code First, while basic it does overcome the first potential blocker, that is connecting to federation members by executing the ‘USE FEDERATION’ statement before executing LINQ query against the backend SQL Azure federated database. It also provides a primer for those interested in SQL Azure Federations.

Background Information

Below is a quick summary of the USE FEDERATION statement, for those familiar with the concepts simply jump below to the sample code.

Connecting to Federation Members

SQL Azure Federations has the concept of a Federation Member, which is a system managed database that contains a subset of the data in the scaled-out federation cluster. The data within a federation member is defined by a distribution key and partitioning scheme. The federation distribution key can be of data type int, bigint, uniqueidentifier or varbinary(n). Currently only range partitioning schemes are permitted, but in the future it is anticipated that hash and other schemes will be made available. Connecting to individual federation members is established by the ‘USE FEDERATION’ command as submitted to the SQL Azure Gateway Services layer as denoted by the DDL snippet below. You can find more information about the SQL Azure Architecture here.

USE FEDERATION ROOT
WITH RESET

USE FEDERATION federation_name (distribution_name = value)
WITH FILTERING={ON|OFF}, RESET

The ROOT keyword redirects the connection to the federation root database if run within a federation member. The WITH FILTERING=ON|OFF is devised to set the connection scope, either to the full range of a federation member or to a specific distribution key value. The WITH RESET keyword is mandatory for version 1 of SQL Azure Federations and provides an explicit reset of the connection.

Interesting considerations and FAQs about the USE FEDERATION statement are:

1. The USE FEDERATION statement must be the only statement in a batch but can be executed at any time on an active connection.

2. The USE FEDERATION statement enables the client to switch between federation members and thus promotes efficient connection pooling with all client libraries.

3. Execution of the USE FEDERATION statement is analogous to running sp_reset_connection on a connection as they are retrieved from the connection pool, all connection settings are reset to default.

Sample – Code First Against a Federated Member

The sample is composed of two sections. Firstly the creation of the database objects including the federated database, federated tables and federated members. Data will be inserted for demonstration purposes. Secondly using the code first feature of Entity Framework, create a code first model and associated queries against the database.

Step1 – Create Database Objects

First create a federated database using the ‘CREATE FEDERATION’ DDL statement. In this sample below the federation ‘FED_1’ was created with a distribution key named ‘range_id’ of type BIGINT with a range type distribution scheme.

-- Create the federation named FED_1. 
-- Federate on a bigint with a distribution key named range_id
CREATE FEDERATION FED_1 (range_id BIGINT RANGE)
GO

Connect to the first and only federated member available at this time and create a federated table called Orders with a distribution key named customer_id. Note that the primary key is made up of the order_id and customer_id, thus conforming to the stipulation that the distribution key must be included in any unique indexes such as a primary key definition. The order of the distribution key is not relevant.

-- Connect to the first and only federated member
USE FEDERATION FED_1 (range_id = 0)
WITH FILTERING = OFF, RESET
GO

-- Create the table in the first federated member, this will be a federated table.
-- The federated column in this case is col1.
CREATE TABLE Orders
(
    order_id bigint not null,
    customer_id bigint,
    total_cost money not null,
    order_date datetime not null,
    primary key (order_id, customer_id)
) FEDERATED ON (range_id = customer_id)
GO

-- Insert 160 values into the federated table
DECLARE @i int
SET @i = 0

WHILE @i < 80
BEGIN
    INSERT INTO Orders VALUES (@i, @i, 10, getdate())
    INSERT INTO Orders VALUES (@i+1, @i, 20, getdate())
    SET @i = @i + 1
END
GO

Split the federated database into eight federated members (eight federated members and 1 root fit well on the diagram below) with high range boundaries set every ten customer_ids. Given the data entered into the Orders federated table we will have twenty rows per federated member with each federated member containing the orders for ten customers or two rows per customer_id. In this case we are taking advantage of the multi-tenancy properties of federated tables by housing ten tenants per member database. The premise is that if any of the tenants becomes hot or the data usage high, one can simply split that individual federated member to provide more resources to that tenant when needed.

Note that during the SPLIT operation all non-federated tables (reference tables) are duplicated to the new federation members. The data for federated tables are copied to the federation member as defined by the boundary value within the SPLIT statement and the given distribution key on the table. Since the SPLIT operation is an asynchronous operation while the filtered data is copied from the original federation to the new destination federation member, we must wait for the SPLIT statement to complete before we execute the next SPLIT operation. Failure to wait will simply result in an error as shown below.

Msg 45007, Level 16, State 1, Line 1
ALTER FEDERATION SPLIT cannot be run while another federation operation is in progress on federation FED_1 and member with id 65728.

The WaitForFederationOperation stored procedure used in this script was written to wait till the SPLIT operation completed before execution of the subsequent SPLIT operation. The text to the stored procedure is included below the ALTER FEDERATION statements.

-- Create 8 federated members using the SPLIT command
-- Split must be run at the root database
USE FEDERATION ROOT WITH RESET
GO

-- range_low = -9223372036854775808, range_high = 10
ALTER FEDERATION FED_1 SPLIT AT (range_id=10)
GO

EXEC WaitForFederationOperations 'FED_1'
GO

-- range_low = 10, range_high = 20
ALTER FEDERATION FED_1 SPLIT AT (range_id=20)
GO

EXEC WaitForFederationOperations 'FED_1'
GO

-- range_low = 20, range_high = 30
ALTER FEDERATION FED_1 SPLIT AT (range_id=30)
GO

EXEC WaitForFederationOperations 'FED_1'
GO

-- range_low = 30, range_high = 40
ALTER FEDERATION FED_1 SPLIT AT (range_id=40)
GO

EXEC WaitForFederationOperations 'FED_1'
GO

-- range_low = 40, range_high = 50
ALTER FEDERATION FED_1 SPLIT AT (range_id=50)
GO

EXEC WaitForFederationOperations 'FED_1'
GO

-- range_low = 50, range_high = 60
ALTER FEDERATION FED_1 SPLIT AT (range_id=60)
GO

EXEC WaitForFederationOperations 'FED_1'
GO

-- range_low = 60, range_high = 70 && range_low = 80, range_high = null
ALTER FEDERATION FED_1 SPLIT AT (range_id=70)
GO

EXEC WaitForFederationOperations 'FED_1'
GO

Tip:Use the sys.dm_federation_operations dynamic management view to observe the current status for all federation operations such as SPLIT and DROP.

CREATE PROCEDURE WaitForFederationOperations
( @federation_name varchar(200) )
AS
DECLARE @i INT
SET @i = 1  

WHILE @i > 0
BEGIN  

 SELECT @i = COUNT(*) FROM SYS.dm_federation_operations
 WHERE federation_name = @federation_name

 WAITFOR DELAY '00:00:01'
END

Create an Orders table at the root, this table will not be federated and is used for demonstration purposes only. Typically one would not include a regular table at the root with the same name as a federated table.

USE FEDERATION ROOT WITH RESET
GO

-- Create the table at the root, this table will not be federated.
-- The table T1 was created at the root for demonstration purposes, typically one
-- would not include a regular table at the root with the same name as a federated table.
CREATE TABLE Orders
(
    order_id bigint not null,
    customer_id bigint,
    total_cost money not null,
    order_date datetime not null,
    primary key (order_id, customer_id)
)
GO

-- Insert negative values to clarify the demonstration
INSERT INTO Orders VALUES (-1, -1, -10, getdate())
INSERT INTO Orders VALUES (-2, -2, -10, getdate())
INSERT INTO Orders VALUES (-3, -3, -10, getdate())
GO

At this point we have eight federation members and a root database that make up the FED_1 federated database. We can view the federation members by the querying sys.federation_member_distributions when executed against the root of the federation.

SELECT * FROM sys.federation_member_distributions
GO

Step2 – Create Code First Objects

Entity Framework 4 allows us to develop with a code-first mentality, not only modeling objects as plain old classes but also forgoing the Visual Studio designer and associated XML mapping files. This code-first support is part of a separate download called ADO.NET Entity Framework 4.1 as found here. Included with EF4.1 is the simplified abstraction of the ObjectContext as surfaced by the DbContext API.

Earlier we created an Orders table as federated on the customer_id and included this column in the primary key. Thus we can model this table with the following code-first snippet. Note that the order for the primary key is specified using data annotations and the Key and Column attributes to define the composite key.

    public class Order
    {
        [Key, Column(Order = 1)]
        public long order_id { get; set; }
        [Key, Column(Order = 2)]
        public long customer_id { get; set; }
        public decimal total_cost { get; set; }
        public DateTime order_date { get; set; }
    }

The database context is created for retrieval of our orders from the federated members and is defined below as SalesEntities.

    public class SalesEntities: DbContext
    {
        public DbSet<Order> Orders { get; set; }

        public SalesEntities (string connStr)
            : base(connStr)
        {
        }
    }

Step – 3 T-SQL Query

The diagram below positions the intention of our sample, we wish to retrieve all orders (in this case two per customer) for the customer with a customer_id = 40. To do so we must first execute the USE FEDERATION statement on a given connection and follow that up with our LINQ query. In summary we want to execute the following against SQL Azure using code-first.

USE FEDERATION FED_1 (range_id=40) WITH FILTERING=ON, RESET
GO

SELECT * FROM Orders
GO

The following diagram details to execution sequence. We have a federated database with eight federated members and one root database. The members are distributed amongst multiple servers.  In this sample, matters were simplified by placing all federation members on servers named x, y and z. In reality these members would be spread out across more servers in the Azure data center. The first statement (USE FEDERATION) establishes a connection to federated member so that the queries are scoped to the tenant with customer_id = 40. The WITH FILTERING=ON clause scopes the connection to the federation key instance of 40 in the federation member with a range_low =40 and high_range=50 and it also specifies that queries are filtered with the given federated value of 40.

Step 4 – EF Code-First Query

We will first show two common code-first approaches that fail and the reason to why these queries fail, followed by the corrected code-first sample.

First Code-First Blunder

This query doesn’t work as expected and returns an incorrect result set because it plays into common misunderstanding. That is, for each execution EF checks the connection state and if connection is already open – it just uses it, if the connection is closed – it opens the connection then executes then brings the connection to the initial state before the execution (i.e. closes it).

So in this example we execute the appropriate USE FEDERATION statement by calling ExecuteSqlCommand, but neglect the fact that the connection is closed when the command completes and that this connection is thus sent back to the connection pool. When EF executes the LINQ statement, it pulls a new connection from the pool (it is reset) and submits the select statement to SQL Azure. In this case the select is made against the root database not the particular federated unit with customer_id = 40.  We can see that the query was executed against the root database by observing the negative values for order_id and customer_id.

using (SalesEntities dc = new SalesEntities(ConnStr))
{
    string federationCmdText = @"USE FEDERATION FED_1 (range_id=40) WITH FILTERING=ON, RESET";
    dc.Database.ExecuteSqlCommand(federationCmdText);

    IList<Order> orders = (from x in dc.Orders
                           select x).ToList<Order>();

     foreach (Order o in orders)
        Console.WriteLine("order_id: {0}, customer_id: {1}", o.order_id, o.customer_id);
}

Results:

order_id: -3, customer_id: -3

order_id: -2, customer_id: -2

order_id: -1, customer_id: -1

Second Code-First Blunder

Let’s change the snippet of code above to open the database connection (dc.DataBase.Connection.Open) prior to submitting the USE FEDERATION statement; in this way we would expect for EF to reuse the same connection for all statements and thereby overcome the prior misunderstanding. But, this query doesn’t work as expected. It plays into a nuance with the code-first implementation of DbContext and how it differs from that of ObjectContext. The Connection property of DbContext is of type SqlConnection while the Connection property of an ObjectContext is of type EntityConnection. The current code-first implementation does not check the underlying SqlConnection before attempting to open the EntityConnection, thus receive an exception because instances of EntityConnection cannot be created with an open DbConnection.

Corrected Code-First Query

In this corrected version we cast the DbContext to an IObjectContextAdapter so as to expose the ObjectContext property. From there we can simply access the EntityConnection on the context and open it prior to executing the USE FEDERATION command. When EF attempts to execute the LINQ query to retrieve the orders, it examines the EntityConnection and finds that it is already open, thus using this connection for the scope of the DbContext.

We can see from the results that we have successfully filtered on customer_id = 40 and retrieved the correct orders from the database.

using (SalesEntities dc = new SalesEntities(ConnStr))
{
    string federationCmdText = @"USE FEDERATION FED_1 (range_id=40) WITH FILTERING=ON, RESET";

    ((IObjectContextAdapter)dc).ObjectContext.Connection.Open();
    dc.Database.ExecuteSqlCommand(federationCmdText);

    IList<Order> orders = (from x in dc.Orders
                               select x).ToList<Order>();

    foreach (Order o in orders)
        Console.WriteLine("order_id: {0}, customer_id: {1}", o.order_id, o.customer_id);
}

Results:

order_id: 40, customer_id: 40

order_id: 41, customer_id: 40

Conclusion

In this blog we provided an overview of SQL Azure Federations and three snippets of EF code for the most basic query; one snippet returning incorrect results, another throwing an unexpected exception and a third corrected version. In the next blog posting I will demonstrate another common gotcha with SQL Azure Federations and Entity Framework.

Bookmark It

Hide Sites

(7 votes, average: 5.00 out of 5)