Simpleroleprovider causing remote transaction inside transactionscope

Question

I am in the process of upgrading asp.net membership to the new simplemembership provider in MVC4. This is an Azure/Sql Azure app which runs fine on localhost but fails when deployed. I have code in a transaction as follows:

            TransactionOptions toptions = new TransactionOptions();
            toptions.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
            using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required, toptions))
            {
                try
                {
                   ... do a bunch of database stuff in a single dbContext ...

                   var roleprov = (SimpleRoleProvider)Roles.Provider;
                   string[] roles = roleprov.GetRolesForUser(Username);
                   // above line fails with The transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D024)
                 }
             }

I am using this technique to populate the Roles classes. The stack trace seems to indicate that it is indeed trying to fire off a sub-transaction to complete that call. The simplemembership tables are in a different db. How can I retrieve role info from the role provider inside the context of a separate transaction?

Answer 1

The problem is that GetRolesForUser causes a new connection to open to a second database, and that in turn picks up that it is in a TransactionScope. In turn this (MSDN - System.Transactions Integration with SQL Server) then promotes to the DTC. You could try a few options:

Get roles before the transaction starts

You could retrieve string[] roles outside your TransactionScope. Is there a reason you need to get them inside the scope? Given that you say:

How can I retrieve role info from the role provider inside the context of a separate transaction

it sounds like you could get the role info before the TransactionScope and have no problems.

Turn off transactions on the simple membership connection string

You can tell a connection string not to take part in transactions by putting "enlist=false" (see SqlConnection.ConnectionString) in the connection string, so this might be one option for you if you never need transactions on the database you use for Simple Membership.

Try opening the Simple Membership connection before the transaction

For SimpleRoleProvider it creates it's database object, and then opens it the first time it uses it. But, it doesn't close it until .... Scratch that, the connection is opened on each call to GetRolesForUser so you are out of luck. I was thinking you could call GetRolesForUser once before TransactionScope is opened, and then again inside the scope using the already open connection - you can't.

Play with the IObjectContextAdapter

Disclaimer: I can't promise this will work as I can't test with your setup.

You can play tricks to prevent promotion with two connection strings by opening the non-transaction connection string outside the transaction scope first, and then the transaction shouldn't be promoted. This can also be used if you cause the same connection to Close and then Open inside the same transaction scope (which would otherwise cause promotion).

You could try this with your context, and see if that stopped the GetRolesForUser promoting the transaction, but I doubt that would work as GetRolesForUser causes the connection to open if it isn't already. As I can't test in your scenario, I will include it in case it helps.

using (var db = new ExampleContext())
{
    var adapter = db as System.Data.Entity.Infrastructure.IObjectContextAdapter;
    using (var conn = adapter.ObjectContext.Connection)
    {
        conn.Open();
        using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
        {
            // perform operations
            db.SaveChanges();
            // perform more operations
            db.SaveChanges();
            // perform even more operations
            db.SaveChanges();

            //  If you don't complete, the transaction won't commit and you will lose the changes
            scope.Complete();
        }
    }
}