Use TransactionScope with OracleManagedDataAccess throwing System.PlatformNotSupportedException: 'Operation is not supported on this platform.'

Question

I can reproduce this successfully by trying opening 2 connections in one same TransactionScope (even the first one is closed before opening the next), like this:

 var connectionString = "some connection";
 using (var t = new TransactionScope())
 {
    using (var con1 = new OracleConnection(connectionString))
    {
       con1.Open();
    }                    
    using (var con2 = new OracleConnection(connectionString))
    {
       con2.Open();//exception thrown at here
    }
 }  

Actually I'm trying to take benefit of TransactionScope to implement some kind of Ambient transaction for my repositories (which each opens its own connection using one same connection string). The code above is simplified as much as possible to help reproduce the exception.

I'm not so sure if I'm doing something wrong or TransactionScope is not supported by OracleManagedDataAccess at least in .NET Standard. My project targets .NET Standard 2.0 (lib) and .NET Core (app) 2.2, the OracleManagedDataAccess is installed via nuget (of course targeted .NET Standard) and has version 2.19.3.

Here is the stacks trace of the exception posted above:

at OracleInternal.MTS.MTSRMManager.CCPEnlistDistributedTxnToSysTxn(OracleConnectionImpl connImpl, Transaction txn, MTSTxnRM txnRM, MTSTxnBranch txnBranch)
at OracleInternal.MTS.MTSRMManager.CCPEnlistTransaction(OracleConnectionImpl connImpl, Transaction transaction, MTSTxnRM txnRM, MTSTxnBranch txnBranch)
at OracleInternal.ConnectionPool.PoolManager`3.GetEnlisted(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, OracleConnection connRefForCriteria)
at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword, OracleConnection connRefForCriteria)
at Oracle.ManagedDataAccess.Client.OracleConnection.Open()

I have a feeling that this is a very tough issue depending almost on OracleManagedDataAccess. If I cannot use TransactionScope, there will be no easy way to implement Ambient transaction.

Answer 1

A single connection inside TransactionScope should work as expected. When you enlist two connections to a TransactionScope, you are trying to perform a distributed transaction, which is not supported in .NET Core:

As of version 2.1, the System.Transactions implementation in .NET Core does not include support for distributed transactions, therefore you cannot use TransactionScope or CommittableTransaction to coordinate transactions across multiple resource managers.

(from Microsoft Docs article on EF Core Using Transactions).

The rationale for not supporting distributed transactions is their reliance on MSDTC, which is specific to Windows. It doesn't align well with .NET Core roadmap of being a cross-paltform framework.

EDIT: an alternative approach

In general, a decision to rely on distributed transactions should be taken with extra caution, as these transactions easily become scalability bottlenecks. In modern architectures distributed transactions are generally discouraged.

In your case, since all your resources are connections to the same database, you don't actually need the distributed transactions. I would suggest implementing (an ambient) Unit of Work.

For example, you may have an instance of unit-of-work per logical/business operation in your application. An instance of unit-of-work would buffer all requested modifications to the database (use your own data structures to represent the modifications). In the end of the operation, the unit-of-work will be committed. The commit operation would include:

• opening DB connection and DB transaction
• performing all buffered modification using the single DB connection
• committing the DB transaction and closing DB connection

As another example, Entity Framework Core implements the Unit of Work pattern in DBContext class.