Unable to use SQLite in presence of ambient transactions

[mdonoughe]

Since EntityFrameworkCore 2.1 gained support for ambient transactions with some providers, it is no longer possible to use EntiteFrameworkCore.Sqlite when ambient transactions are present.

In EntityFrameworkCore 2.0 an exception would be thrown due to a warning that ambient transactions were not supported, but if you configured your warnings it was possible to just ignore the warning and the ambient transaction, obviously not participating in the transaction.

EntityFrameworkCore 2.1 added support for ambient transactions, but not for Sqlite (#13825). The same as before, it recognizes that EntityFrameworkCore.Sqlite does not support ambient transactions, and gives a warning that can be ignored, but if you try to ignore the warning it goes on and tries to use distributed transactions anyway, leading to a NotSupportedException.

Exception message: System.NotSupportedException: Specified method is not supported.
Stack trace:
   at System.Data.Common.DbConnection.EnlistTransaction(Transaction transaction)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.HandleAmbientTransactions()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Sqlite.Storage.Internal.SqliteRelationalConnection.Open(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(DbContext _, ValueTuple`2 parameters)
   at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)

Steps to reproduce

https://github.com/mdonoughe/ef15413

Further technical details

EF Core version: 2.1.8, 2.2.3
Database Provider: Microsoft.EntityFrameworkCore.Sqlite
Operating system: Windows 10
IDE: Visual Studio 2017 15.9

[ajcvickers]

Discussion from triage:

• This is a bug; SQLite should be opted out.
• We discussed changing to not explicitly enlist in the ambient transaction when an open connection is used with an ambient transaction that was created before the connection was opened.
  • This would align with the ADO.NET behavior. That is, it would require an explicit call to enlist in the transaction.
  • This is clean and consistent
  • However, it is a breaking change and it diverges from the EF6 behavior
  • Decision: for now we will not make this change, and just fix this as a bug.