Is it possible to disable the enlistment to ambient transactions in Entity Framework Core 8 globally?

Question

Short version

We want to disable the automatic enlistment to ambient transactions (System.Transactions.TransactionScope) for a whole Entity Framework Core 8 DbContext. The DbContext should never get enlisted to an already ongoing ambient transaction.

We don't want to explicitly create a new TransactionScope with TransactionScopeOption.Suppress everywhere in code where we access the DbContext and therefore we would like to disable it globally. We want to abstract away this implementation detail and reduce code complexity. Developers shouldn't care about this when accessing the DbContext.

Longer version with more context

We have two different Entity Framework Core DbContexts which connect to two different databases. One of the two databases is used read-only and therefore we don't need (distributed) transactions over both databases. For this database we would like to disable the enlistment to ambient transactions.

We have a decorator pattern in place where a TransactionScope is created. Inside the decoratee, both databases can be used and by default, both DbContexts enlist to the ambient transaction. We want to disable the enlistment for the read-only database.

What we've tried

1. For ADO.NET this is possible by specifying the Enlist=false property in the connection string. But this doesn't seem to work for entity framework since EF doesn't consider this connection string property.

2. We tried to create an DbCommandInterceptor and override the ReaderExecuting method and setting the transaction of the connection to null:

   public override InterceptionResult<DbDataReader> ReaderExecuting(
       DbCommand command,
       CommandEventData eventData,
       InterceptionResult<DbDataReader> result)
   {
       command.Connection?.EnlistTransaction(null);
       return base.ReaderExecuting(command, eventData, result);
   }
   

   This throws an exception that the connection has a transaction enlisted which must be completed first.

3. We considered creating a new TransactionScope with TransactionScopeOption.Suppress inside an interceptor. But we aren't sure how to correctly handle the disposing of the TransactionScope inside the interceptor. This also feels hacky and we don't want to introduce resource leaks with an incorrect implementation.

Answer 1

Thanks to the hints in the comments by @Svyatoslav Danyliv, I got it working with the following steps:

1. Create a class which derives from SqlServerConnection to disable ambient transactions at Entity Framework level:

   internal class NoAmbientTransactionSqlServerConnection(
     RelationalConnectionDependencies dependencies)
       : SqlServerConnection(dependencies)
   {
       protected override bool SupportsAmbientTransactions => false;
   }
   

2. When registering the DbContext, replace the IRelationalConnection with this custom implementation by using the DbContextOptionsBuilder:

   options.ReplaceService<IRelationalConnection, NoAmbientTransactionSqlServerConnection>();
   

3. This is still not enough, since the SqlServerConnection base class uses the DbConnection from the SQL client internally. This DbConnection would still enlist to the ambient transaction. Therefore, add Enlist=false to the connection string to disable the enlisting at SQL client level too.

Small disadvantage is that the SqlServerConnection is an internal Entity Framework Core API which raises a warning when using it and it must be used with caution and can be changed or removed without notice.