Support SetTransactionHandler method for Entity Framework 6.x.

[rikat-ms]

The issue

If we add SetTransactionHandler method in a derived class of DbConfiguration, it fails at SaveChanges method.

Expected:
A table named __TransactionHistory is created in PostgreSQL database, a record is inserted to the table and SaveChanges method completes. And then, added record is deleted at Dispose of the instance for the derived class of DbContext.

Actual:
The following error is returned and SaveChanges method fails.

Error message:
Nested/Concurrent transactions aren't supported.

Stack trace (snippet):
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlCommand.ExecuteNonQuery()
at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)
at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)
at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinTransaction(IEnumerable`1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext)
at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements, DbTransaction existingTransaction)
at System.Data.Entity.Infrastructure.TransactionContextInitializer`1.InitializeDatabase(TContext context)
at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action)
at System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization()
at System.Data.Entity.Infrastructure.CommitFailureHandler.BeganTransaction(DbConnection connection, BeginTransactionInterceptionContext interceptionContext)
at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.BeginTransaction(DbConnection connection, BeginTransactionInterceptionContext interceptionContext)
at System.Data.Entity.Infrastructure.DbExecutionStrategy.Execute[TResult](Func`1 operation)

Is this method (SetTransactionHandler) supported in EntityFramework6.Npgsql and Npgsql?

Steps to reproduce

1. Create a project (any type).
2. Install EntityFramework6.Npgsql and Npgsql to the project.
3. Add a class to the project and add your entity model. (derived class of DbContext)
4. Add a class to the project and add your code-base configuration for Entity Framework. (derived class of DbConfiguration)
5. Add some codes to run SaveChanges method.

*) For the step 3 and 5, you can follow the steps written in the following page.
https://docs.microsoft.com/en-us/ef/ef6/modeling/code-first/workflows/new-database
*) For the step 4, you can user the example written in the following page.
https://docs.microsoft.com/en-us/ef/ef6/fundamentals/configuring/code-based#example

Further technical details

Target Framework: 4.5.2
Entity Framework version: 6.2.0
EntityFramework6.Npgsql: 3.2.0
Npgsql version: 4.0.2
PostgreSQL version: 9.6
Visual Studio 2019 16.10.4
Operating system: Windows 10 20H2

Other details about my project setup:

Additional information

*) Expected behavior:
These are the steps when we use SQL Server as the destination at SaveChanges. method if the project has SetTransactionHandler.

1. Begin a transaction.
2. Try to insert a record to __TransactionHistory table but it fails because no table exists in the database.
3. Retry several times.
4. Create __TransactionHistory table after all attempts of insert fail.
5. Retry to insert a record to __TransactionHistory table and it completes successfully.
6. Run INSERT/UPDATE/DELETE depending on the steps happened in the application as it is expected by SaveChanges.
7. Commit the transaction.

After that, the record inserted into the __TransactionHistory table is deleted when the instance of DbContext used for SaveChanges method is disposed. Once __TransactionHistory table is created, the step 2 above completes successfully and then go to the step 6.

With EntityFramrework6.Npgsql and Npgsql, the exception occurs before creating __TransactionHistory table.

*) Table __TransactionHistory:
The table name "__TransactionHistory" is defined for this transaction handler in the EF6.
https://github.com/dotnet/ef6/blob/99c172bc465492fa37f9efa07900653975ec79b4/src/EntityFramework/Infrastructure/Transactions/TransactionContext.cs#L21

[roji]

@rikat-ms note that this provider is now pretty much archived and isn't received much attention any more.

[rikat-ms]

Thank you, @roji for the information on this provider. That's a shame... One of my customers encounters a issue like In-Doubt transaction and SetTransactionHandler can be a solution for it, but the issue I described has blocked it.
So, it would be helpful if someone would make clear that this provider currently supports SetTrasnsactionHandler or not (although it looks it doesn't support it...)

[Emill]

If you implement it and submit a PR, we could maybe add it. EF6 has been a bit stale now for the last years when Microsoft has put all efforts into EF Core instead. The same goes with this provider.