Support Async Transaction APIs

[caleblloyd]

@divega opened this to CoreFX a while ago: https://github.com/dotnet/corefx/issues/10975 but it doesn't look like there has been any movement.

Our ADO.NET provider, MySqlConnector has BeginTransactionAsync, CommitAsync, and RollbackAsync

We like to use them in Pomelo.EntityFrameworkCore.MySql to avoid running Sync-Over-Async I/O and cut down on thread pool lockups.

EF does not lend itself well to supporting these Async methods. We have to override every single method that begins, commits, or rolls back a transaction in our provider.

I think that EF should add a class that allows for the provider to define whether or not it supports Async transaction APIs, and use those instead of the Sync ones if it does.

[divega]

Thanks @caleblloyd for the information. I think we agree that the long term solution would be for the base ADO.NET classes to add these capabilities. Can you please make a comment in dotnet/corefx#10975 detailing what your MySQL provider could use?

Maybe there is something we can do on EF Core in the shorter term. We will discuss it in triage.

[caleblloyd]

If #9014 gets fixed, then more transaction related code is going to get into EFCore.Relational and I think that it's important to allow providers to use Async transaction codepaths.

I opened a PR in #9017 with my idea about what this would look like as a solution for just EF. Basically we move the async APIs that we want ADO.NET DbConnection and DbTransaction to their EFCore.Relational counterparts and make them virtual so the provider can override with a real async implementation.