2

Can anyone update me on this topic?

I want to support both SQL Server and Oracle in my application.

Is it possible to have the following code (in BL) working for both SQL Server and Oracle without escalating/spanning to distributed transactions (DTC) ?

 // dbcontext is created before, same dbcontext will be used by both repositories
 using (var ts = new TransactionScope())
 {
    // create order - make use of dbcontext, possibly to call SaveChanges here
    orderRepository.CreateOrder(order);

    // update inventory - make use of same dbcontext, possibly to call SaveChanges here
    inventoryRepository.UpdateInventory(inventory);

    ts.Complete();
 }

As of today, end of August 2013, I understand that it works for SQL Server 2008+ ... but what about Oracle? I found this thread... it looks like for Oracle is promoting to distributed transactions but is still not clear to me.

Does anyone have experience with writing apps to support both SQL Server and Oracle with Entity Framework to enlighten me?

Thanks!

Update: Finally I noticed EF6 comes with Improved Transaction Support. This, in addition to Remus' recommendations could be the solution for me.

Learner
  • 3,297
  • 4
  • 37
  • 62
  • Or at least, maybe there is someone to tell me another ORM/Oracle Data Provider which I could use to achieve my goal? – Learner Aug 29 '13 at 09:15

2 Answers2

1

Nope, DTC is needed for distributed transactions - and something spanning 2 different database technologies like this is a distributed transaction. Sorry!

Kieren Johnstone
  • 41,277
  • 16
  • 94
  • 144
1

First: never use var ts = new TransactionScope(). Is the one liner that kills your app. Always use the explicit constructor that let you specify the isolation level. See using new TransactionScope() Considered Harmful.

Now about your question: the logic not to promote two connections in the same scope into DTC relies heavily on the driver/providers cooperating to inform the System.Transactions that the two distinct connections are capable of managing the distributed transaction just fine on their own because the resource managers involved is the same. SqlClient post SQL Server 2008 is a driver that is capable of doing this logic. The Oracle driver you use is not (and I'm not aware of any version that is, btw).

Ultimately is really really really basic: if you do not want a DTC, do not create one! Make sure you use exactly one connection in the scope. It is clearly arguable that you do not need two connections. In other words, get rid of the two separate repositories in your data model. Use only one repository for Orders, Inventory and what else what not. You are shooting yourself in the foot with them and you're asking for pixie dust solutions.

Update: Oracle driver 12c r1:

"Transaction and connection association: ODP.NET connections, by default, detach from transactions only when connection objects are closed or transaction objects are disposed"

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Hi Remus... I am very glad you came along, you helped me long back before as well. I already got crazy around this stuff... So Oracle probably is not able to solve that. But then I am really not sure how I could have my BL to look like to achieve my goal. Do you mind giving me like 5mins into chat please? – Learner Aug 29 '13 at 13:22
  • I have a project with own business objects (used in BL) and EF with its own POCOs. I want to insert multiple related business objects, but I have to call 'SaveChanges' multiple times inside one transaction in order to insert at a later point objects with foreign keys auto-generating ids of previously inserted objects. Calling SaveChanges multiple times I think it escalates for Oracle, yes? (Note: I cannot use navigation properties, because I don't have POCOs in BL) – Learner Aug 29 '13 at 13:33
  • AFAIK calling .SaveChanges multiple time should not be an issue. The problem arises from the fact that you use two different EF contexts: `orderRepository` and `inventoryRepository`. – Remus Rusanu Aug 29 '13 at 13:42
  • Ah, no, dbContext is the same... Could you please re-check the thread link from my question? They say the same there which is surprising for me... But maybe I understood wrong? – Learner Aug 29 '13 at 13:44
  • Another case is when I want to execute a stored proc inside a transaction scope in combination with some other dbContext.SaveChanges. This is also something which I don't understand if it escalates in Oracle or not. – Learner Aug 29 '13 at 13:49
  • Sry, that is going beyond my (poor) understanding of EF. But I can tell you that, as far as I know, DTC escalation at System.Transactions scope is a sure sign of 2 connections. – Remus Rusanu Aug 29 '13 at 14:02
  • I am also new, to both EF and Oracle... I found also this link: http://www.digitallycreated.net/Blog/48/entity-framework-transactionscope-and-msdtc Maybe is not so bad to open the connection explicitly after all – Learner Aug 29 '13 at 14:32
  • Per your recommendation I'll try to manipulate the underlying connection of EF's dbContext (including its transaction) to run the BL code... will try to stay away from TransactionScope for now... let's see what results will bring. – Learner Aug 29 '13 at 15:09
  • I will accept your answer as soon as I find a way to control EF's connection and transaction to make sure I have only one opened as you recommended, THANKS! – Learner Aug 30 '13 at 09:23