How to share a connection between EF DbContext and AspNet Membership to avoid transactions escalating to DTC

Question

I have an ASP.NET MVC3 application that uses an EF 4.1 DbContext, database-first data layer. The EDMX approach works fine as I tend to make changes to my data model before adapting the application to them. The application works fine with the special EF connection string that includes metadata references.

However, there's one fly in the ointment. The application also uses ASP.NET membership and roles which require a standard connection string. I have several use cases that involve both the membership tables and other (EF managed) tables. As the two use separate connection strings, transactions that involve both need DTS to handle them. I don't want to go that route if I can help it, I'd rather all parts of the application simply use the same connection.

Getting EF to run with a plain connection string however is eluding me. Can anyone tell me how it is done, please?

Answer 1

You have several options here. (I know this is long, but please try to read the whole thing). It would help if you can give an actual scenario where you need such a transaction.

First, you are working on a false assumption that if both EF and Membership have the same connection string, it will use a common connection. This may be true sometimes, but is not guaranteed. Connection pooling tries to use the same connection for a given string, but if a connection is already in use it will create a second connection (or reuse an existing second connection already in the pool). So this line of reasoning will get you in trouble at some point.

One of the problems that Membership is designed to solve is to have a pluggable provider interface, so you can swap out membership providers and move to a different one (such as going from Sql to ActiveDirectory), without having to modify your application (or having to modify it much).

More tightly integrating these functions means throwing that benefit away. Maybe that's acceptable, but you should realize that going along these paths essentially tightly couples your data model to the specific Membership provider schemas. A few years ago, that didn't seem like it would be a problem as the membership system hadn't changed in years... but lately, MS and others have been introducing new Membership systems like SimpleMembership and Universal Providers which have different schemas.

So, if we're removing one of the primary features of Membership, why even continue to use it? Well, there are still some benefits from Membership. The primary one being that it provides an out of box full implementation of a user management library, including secure password encryption/hashing and features like question and answer authentication. That's not something to sneeze at, as doing a secure, bug-free membership system from scratch is not trivial (even though it would seem so at first).

So, one option is to implement your own MembershipProvider based on an existing one (like SqlMembershipProvider. Microsoft provides the source for these). Then you can simply override the schema to match whatever you want, but keep all the other features like password encryption and what not. Just fit them into your own schema. That makes them fit your data model a lot better.

However, even if you choose to use the standard membership provider, then there are some things you can do.

First, you can simply map the membership tables into your Entity Framework model. Just drag and drop them onto your designer, or add them in Code First. However, if you do this, you should only use them as read-only, and you should not create foreign key relationships between the membership tables and your tables. Instead, just do manual joins in your EF query (which is more work, but safer) and treat them as stand-alone tables.

Ok, so what about situations where you need to update or delete data from the membership tables as part of a query? Frankly, if you're using the standard membership tables I see almost no reasons this should ever have to happen.

The Membership tables are pretty simple and have very little actual data in them you should need as part of any statements in your app. Unless you're using the Profile provider, which I never do. If you need to map the membership tables, I suggest creating your own table of data rather than using the ProfileProvider.

The only reason I see where you may want to enlist a transaction is when creating a new user. However, since this is a one-time event, then a DT may not be such a terrible thing. However, there may not always be a DTC available to you... so in those cases, the best you can do is use a try-catch block to deal with exceptions.

The alternative is to completely throw away Membership and create your own IPrincipal and IIdentity implementations and simply write your own user management (I would still use the SqlManagementProvider source as a basis for this, however, as it's a good implementation).

Then, since user management is not part of a separate subsystem, you can safely use it for updates and deletes without worrying about what the other subsystem might be doing.

TL;DR

If you can't accept a DT, then either change your workflows, change your code to work with a try-catch-finally statement (though this won't guarantee rollback in case the app code dies suddenly, like a power outage), or use a custom IPrincipal and IIdentity implementation.

Answer 2

I discovered an answer here: https://stackoverflow.com/a/3408209/1169670. Adding "Enlist=false" to the ASP.NET Membership system's connection string stopped the escalation to DTC.

However, this approach simply prevents the membership system enlisting in the transaction. That was sufficient for my requirements, but it may not be in every case.