Sqlite Exception: System.InvalidOperationException: SqliteConnection does not support nested transactions

[d79ima]

I have a somewhat special use case in my EFCore application. When a DBContext is newed up i need it to re-use a SqliteConnection object from my own application connection pool instead of just allocating a new SqliteConnection every time.

This use case causes the following issue with EFCore.BulkExtensions. For example if we look at the code in SqlBulkOperation.cs Insert<T> method:

var transaction = (SqliteTransaction)(context.Database.CurrentTransaction == null ? connection.BeginTransaction() : context.Database.CurrentTransaction.GetUnderlyingTransaction(tableInfo.BulkConfig));

The call to connection.BeginTransaction() allocates a Transaction object and associates it with the connection object from the connection pool. If the sqlite operation happens to error out for some reason, the connection.Transaction is never cleared out. So all subsequent Bulk operations that execute the above code, created a nested transaction because context.Database.CurrentTransaction == null, So a new transaction is attempted on connection.BeginTransaction() and this throws the exception in the title of this issue.

Could you please review and suggest a workaround?

Also, in case an exception is thrown after we called BeginTransaction(), shouldn't we do a RollBackTransaction()?

[d79ima]

Best solution i could come up with so far is to add a catch clause and rollback the transaction only if we are doing explicitCommit:

[borisdj]

Aside from nesting Bulk ops into a transaction scope, there is also Underlying-Conn/Tran. Note in ReadMe: Config also has DelegateFunc for setting Underlying-Connection/Transaction, e.g. in UnderlyingTest.

[d79ima]

Those might be reasonable workarounds, i would have to try them.

But I still think if the library creates its own transaction via connection.BeginTransaction() than it should be responsible for managing it. So if you are doing an explicit commit, should probably also do an explicit rollback and dispose. Is there any harm in including this extra code in a future version?

[borisdj]

I think your suggestion can be added, will consider it.