1

Closed

Supporting Azure Federated SQL Database: Cannot Not Have Transaction Automatically Wrapped Around Database.ExecuteSqlCommand(commandText)

description

Hi there

As a heads up...

For a while now we've successfully been using EF 5.0 with an Azure Federated SQL Database on a greenfield project (not production ready).

In order to do so, the first command that needs to be run is the "USE FEDERATION" statement and then subsequent commands on the same open connection.

This is relatively trivial to do:
        private static void ExecuteCommand(this System.Data.Entity.DbContext ctx, string commandText)
        {
            var conn = ((IObjectContextAdapter) ctx).ObjectContext.Connection;
            conn.Open();
            ctx.Database.ExecuteSqlCommand(commandText);
        }
In the above example, the 'commandText' being passed in is "USE FEDERATION ROOT WITH RESET" via an extension method and then a lamda expression is used on the DbContext object to get the desired result. This has been working well.

I decided to dump in EF 6.0 Alpha 3 and have a play with the new built-in support for transient faults. Oh dear...

With no code change we are suddenly getting an error from SQL Database:
.
System.Data.SqlClient.SqlException: "USE FEDERATION ROOT statement not allowed within multi-statement transaction."
.

It appears as though EF 6 is automatically wrapping a transaction around the "ExecuteSqlCommand()".

We've tried a couple of different ways to turn off transactions. Neither works:
        private static void ExecuteCommand(this System.Data.Entity.DbContext ctx, string commandText)
        {
            var conn = ((IObjectContextAdapter) ctx).ObjectContext.Connection;
            conn.Open();
            ctx.Database.UseTransaction(null);
            ctx.Database.ExecuteSqlCommand(commandText);
        }
        private void SetFederationScope()
        {
            using (new TransactionScope(TransactionScopeOption.Suppress))
            {
                Context.UseFederationRoot();
            }
        }
Whilst I have your attention, of course it would be great to get first class support for federated databases such as something like but I'm not holding my breath:
    [FederatedEntity(FederationKey = "Id")]
    public class UserProfile
    {
        public int Id { get; set; }
        public string DisplayName { get; set; }
        public string EmailAddress { get; set; }
        public DateTime? DOB { get; set; }
    }

    [NonFederatedEntity]
    public class Region
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
In the least we need a way to turn off transactions in this scenario. Also, I remember reading something a few weeks back about EF 6 DbContext now supporting the use of an already open connection? Wondering if it's possible to open the connection and run the federation statement and then pass the connection to DbConntext...? Hmmmm..... I might play a little more... That could have merit. :)
Closed Mar 29, 2013 at 10:29 PM by AndriySvyryd

comments

aylwyn wrote Mar 24, 2013 at 4:10 PM

Ok.... As a further FYI.... No dice....

I created a DbConnection object using Database.DefaultConnectionFactory.CreateConnection("<connection string>"), successfully opened the connection and ran the "USE FEDERATION..." statement, checked the connection was still open and passed that into the constructor for DbContext.

Looking all good, until I then tried to use my DbContext object thus:
        Context.UserProfile.FirstOrDefault(p => p.EmailAddress.ToLower() == emailAddress.ToLower())
Got the following error:
.
"This operation requires a connection to the 'master' database. Unable to create a connection to the 'master' database because the original database connection has been opened and credentials have been removed from the connection string. Supply an unopened connection."
.
That's me out for the count...

maumar wrote Mar 29, 2013 at 7:08 AM

Hi aylwyn,
You could also try running the "USE FEDERATION..." via statement ADO.NET using the connection from context.Database.Connection, after creating the context:

using (var context = new MyContext())
{
var connection = (SqlConnection)context.Database.Connection;
connection.Open();
var command = new SqlCommand();
command.Connection = connection;
command.CommandText = "USE FEDERATION...";
command.ExecuteNonQuery();

// do your normal EF work here
}

This should by-pass the transaction since it's handled outside EF.

Nilesh_Gajare wrote Nov 21, 2013 at 4:33 AM

hi,
try using
using (var ctx = new UsersContext())
        {
            string federationCmdText = @"USE FEDERATION tenant_federation(id = 1) WITH RESET, FILTERING=ON";
            if (ctx.Database.Connection.State != ConnectionState.Open)
                ctx.Database.Connection.Open();               
            ctx.Database.ExecuteSqlCommand(System.Data.Entity.TransactionalBehavior.DoNotEnsureTransaction,federationCmdText);
        }
it works!!!