Is it possibile to use a single transaction (on EF) with two different contexts pointing different schemas?

Question

I'm currenly designing an application where I need to use two different database schemas (on the same instance): one as the application base, the other one to customize the application and the fields for every customer. Since I read something about Repository pattern and as I've understood is possible to use two different contexts without efficiency loose, I'm now asking if I can use a single database transaction between two schemas with Entity Framework, as I'm actually doing directly on the database (SQL Server 2008-2012). Sorry for my English an Thanks in advance!

Answer 1

If your connection strings are the same (which in your case will be as you have different schemas only for different contexts) then you are ok with this approach.

Basically you will have two different contexts that will be connected via the same connection string to the database and which will represent two different schemas.

using (var scope = new TransactionScope()) {
    using (var contextSO = new ContextSchemaOne()) {
        // Add, remove, change entities from context schema one
        ContextSchemaOne.SaveChanges;
    }
    using (var contextST = new ContextSchemaTwo()) {
        // Add, remove, change entities from context schema two
        ContextSchemaTwo.SaveChanges;
    }
    scope.Complete();
} 

I wasn't very successful in the past with this approach, and we switched to one context per database.

Further reading: Entity Framework: One Database, Multiple DbContexts. Is this a bad idea?

Maybe it's better to read something about unit of work before taking a decision about this.

You will have to do something like this: Preparing for multiple EF contexts on a unit of work - TransactionScope