Understanding SQL Azure Federations No-MARS Support and Entity Framework

In a previous blog posting here I ran through a typical first blocker when using the code-first feature of Entity Framework (EF) with SQL Azure Federations. That first blocker was understanding the correct procedure to submit the USE FEDERATION statement before executing your LINQ statements.

This blog posting will continue with the blockers and provide one key takeaway, that is Multiple Active Results Sets (MARS) is not supported in SQL Azure Federations. In fact one cannot even submit the USE FEDERATION statement against a federated database without receiving a SqlException. In this blog we build from the code presented earlier and provide a couple of samples to illustrate a couple of important points.

The USE FEDERATION statement is not supported on a connection which has multiple active result sets (MARS) enabled.

Where in Entity Framework Is MARs Used?

MARs is used in by the Entity Framework provider for the following operations to load related objects. If you are using EF with SQL Azure Federations, you should avoid the following as they dependent upon the MARs capability of SQL Server.

Lazy loading
LoadProperty() to explicitly load related objects as specified by navigation properties
Load() to explicitly load a collection of related objects

Lazy Loading

The snippet below highlights a common scenario in which someone is looking at an Order (ShowOrder) and then decides to drill further down into the OrderDetails (ShowDetails). A couple of points worth mentioning. We are going on the assumption that MARs is disabled by setting the multipleactiveresultsets property of the connection string to false for all the samples. And, the sample from the previous post was augmented to include an OrderDetail collection for the Order, the implementation of which is shown at the bottom for completeness only.

                var orders =  from x in dc.Orders
                              select x;

                foreach (Order order in orders)
                {
                    bool examine = ShowOrder(order);
                    if (examine)

ShowDetails(order.OrderDetails);

In this example, lets propose that Lazy loading is enabled, the default in EF 4.0 and beyond. In the highlighted text above, there is an EntityCommandExecutionException thrown, risen from the Order.OrderDetails navigation property because MARs is a requirement from such an operation.

There is already an open DataReader associated with this Command which must be closed first.

Fortunately we can disable lazy loading whether using the designer or code. In the code bits below you will see that this was achieved by setting the LazyLoadingEnabled property on the Configuration property for the DbContext to false. But if we do so, we get a NullReferenceException when accessing the OrderDetails collection because the navigation properties, in this case the OrderDetails, are empty/null.

                dc.Configuration.LazyLoadingEnabled = false;

                var orders = from x in dc.Orders
                             select x;

                foreach (Order order in orders)
                {
                    bool examine = ShowOrder(order);
                    if (examine)

ShowDetails(order.OrderDetails);

Object reference not set to an instance of an object.

LoadProperty() and Load()

The following code sample shows a possible alternative, that as you would have guessed, fails because there is already and open DataReader associated with the retrieval of the orders. It presents the same exact problem associated with lazy loading, explicit loading of related objects requires two active result sets, i.e. MARs.

                var orders = from x in dc.Orders
                             select x;

                foreach (Order order in orders)
                {
                    bool examine = ShowOrder(order);
                    if (examine)
                    {

dc.Entry(order).Collection(o => o.OrderDetails).Load();

                        ShowDetails(order.OrderDetails);
                    }
                }

Alternatives

Call ToList() or ToArray() before the foreach so that the DataReader is closed for retrieval of the order details. This technique requires that lazy loading is enabled at the context.

                var orders = (from x in dc.Orders
                              select x).ToList();

                foreach (Order order in orders)
                {
                    bool examine = ShowOrder(order);
                    if (examine)
                    {
                        ShowDetails(order.OrderDetails);
                    }
                }

Use eager loading via the Include method of the query results but this means retrieval of all values up front.

                var orders = from x in dc.Orders.Include(o => o.OrderDetails)
                             select x;

                foreach (Order order in orders)
                {
                    bool examine = ShowOrder(order);
                    if (examine)
                    {
                        ShowDetails(order.OrderDetails);
                    }
                }

Conclusion

In this blog we took home one take away about MARs and SQL Azure Federations and looked at those implications to your Entity Framework client code. In the next post I will continue onward with another set of samples to guide you with SQL Azure Federations.

Code Sample for Completeness

Included below are the code augments made from the previous posting. The EF code first snippet includes a new class called OrderDetail. Not the navigation properties added to both classes.

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

        public virtual ICollection<OrderDetail> OrderDetails { get; set; }
    }

    public class OrderDetail
    {
        public long order_id { get; set; }

        [Key, Column(Order = 1)]
        public long order_detail_id { get; set; }

        [Key, Column(Order = 2)]
        public long customer_id { get; set; }

        public long product_id { get; set; }

        public Int16 order_qty { get; set; }

        public decimal unit_price { get; set; }

        [ForeignKey("customer_id,order_id")]
        public virtual Order Orders { get; set; }
    }

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

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

An update to the underlying SQL Azure tables.

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

CREATE TABLE OrderDetails
(
    order_id bigint not null,
    order_detail_id bigint not null,
    customer_id bigint not null,
    product_id bigint not null,
    order_qty smallint not null,
    unit_price money not null,
    primary key (order_detail_id, customer_id)
) FEDERATED ON (range_id = customer_id)
GO

ALTER TABLE OrderDetails
ADD CONSTRAINT FK_OrderDetails_Sales FOREIGN KEY (order_id, customer_id)
REFERENCES Orders (order_id, customer_id)
GO

-- Insert 160 orders (2 order details per order) into the federated tables
DECLARE @order_id int
DECLARE @customer_id int
DECLARE @order_detail_id int

SET @customer_id = 1
SET @order_detail_id = 1
SET @order_id = @customer_id

WHILE @customer_id < 81
BEGIN
    INSERT INTO Orders VALUES (@order_id, @customer_id, 10, getdate())
    INSERT INTO OrderDetails VALUES (@order_id, @order_detail_id, @customer_id, 1, 1, 10)
    SET @order_detail_id = @order_detail_id + 1
    INSERT INTO OrderDetails VALUES (@order_id, @order_detail_id, @customer_id, 2, 2, 20)

    SET @order_id = @order_id + 1
    SET @order_detail_id = @order_detail_id + 1
    INSERT INTO Orders VALUES (@order_id, @customer_id, 20, getdate())
    INSERT INTO OrderDetails VALUES (@order_id, @order_detail_id, @customer_id, 1, 1, 10)
    SET @order_detail_id = @order_detail_id + 1
    INSERT INTO OrderDetails VALUES (@order_id, @order_detail_id, @customer_id, 2, 2, 20)

    SET @order_id = @order_id + 1
    SET @order_detail_id = @order_detail_id + 1
    SET @customer_id = @customer_id + 1
END
GO