[Feature] Async transaction API

[MaceWindu]

Hi, guys. For next release of linq2db we plan to add async transaction API support, which requires support from underlying providers. Npgsql and mysqlconnector already provide such API and it would be nice if you can add such API too.

Unfortunatelly ADO.NET doesn't have such API right now: Async transaction API request for .net core: https://github.com/dotnet/corefx/issues/10975 Async transaction API request for ef core: https://github.com/aspnet/EntityFrameworkCore/issues/9015

[senseibaka]

This shouldn't be too hard to implement -- technically this driver executes everything async, and the synchronous methods wrap the paradigm.

I'm hoping to get some free time to work on the driver "soon" (tm).

The issue you linked hasn't prescribed any method signatures, do you have any suggestions?

[MaceWindu]

Sure.

// AseTransaction
public Task CommitAsync()
public Task CommitAsync(CancellationToken cancellationToken)
public Task RollbackAsync()
public Task RollbackAsync(CancellationToken cancellationToken)

// AseConnection
public Task<AseTransaction> BeginTransactionAsync()
public Task<AseTransaction> BeginTransactionAsync(CancellationToken cancellationToken)
public Task<AseTransaction> BeginTransactionAsync(IsolationLevel isolationLevel)
public Task<AseTransaction> BeginTransactionAsync(IsolationLevel isolationLevel, CancellationToken cancellationToken)

npgsql transaction methods (no BeginTransaction, as npgsql don't need it): https://www.fuget.org/packages/Npgsql/4.0.4/lib/netstandard2.0/Npgsql.dll/Npgsql/NpgsqlTransaction mysqlconnector BeginTransactionAsync: https://www.fuget.org/packages/MySqlConnector/0.49.3/lib/netstandard2.0/MySqlConnector.dll/MySql.Data.MySqlClient/MySqlConnection mysqlconnector Transaction methods: https://www.fuget.org/packages/MySqlConnector/0.49.3/lib/netstandard2.0/MySqlConnector.dll/MySql.Data.MySqlClient/MySqlTransaction

[MaceWindu]

Also there is a good .net core API proposal here https://github.com/dotnet/corefx/issues/35012 with more methods

[bbarry]

Heads up, this shipped in .net core 3.0: https://github.com/dotnet/runtime/issues/28596

This is probably a good thing to look into for #145