Support for cancellation token in db operations

[MargaretKrutikova]

It seems like there is no support for passing cancellation token in async operations against QueryContext. Do the native drivers have support for this? Would it be difficult to implement, what do you think?

[JordanMarr]

It would probably be easy to implement except that I am not yet sure where or how to pass a cancellation token to the underlying task.

[JordanMarr]

It looks like a CancellationToken can be passed to ExecuteNonQueryAsync and ExecuteReaderAsync. So I imagine this being implemented in QueryContext as a CancellationTokenSource option property and a WithCancellation method that initializes it to Some (new CancellationTokenSource()), and a Cancel method. (Same design as the transaction feature.)

QueryContext Dispose would also have to dispose the CancellationTokenSource property if one exists and reset it back to None.

Other things may need to also reset the CancellationTokenSource property to None, like possibly CommitTransaction and RollbackTransaction, and the query methods themselves after they have completed their work (ReadAsync, ReadOneAsync, GetReaderAsync, InsertAsync, UpdateAsync, DeleteAsync, CountAsync).

[MargaretKrutikova]

It's CancellationToken that you need to pass in cmd.ExecuteReaderAsync and cmd.ExecuteScalarAsync, CancellationTokenSource is created by the calling code. So I don't think we will need a property for CancellationTokenSource or a Cancel method anywhere, since the calling code would have access to the original cancellation token source and will be able to cancel.

[MargaretKrutikova]

I was thinking an overload that takes an extra parameter for CancellationToken:

member this.GetReaderAsync<'T, 'Reader when 'Reader :> DbDataReader> (query: SelectQuery<'T>) = ...

member this.GetReaderAsync<'T, 'Reader when 'Reader :> DbDataReader> 
       (query: SelectQuery<'T>, cancellationToken: CancellationToken) = ...

It seems to be the general pattern that dotnet libraries use to handle cancellation tokens, e.g. HttpClient and GetAsync, even ExecuteReaderAsync on the command. I do think it is a bit more difficult to use from F# where you would ideally want to pipe the query as the last parameter, but you can't use partial application here since methods with curried arguments cannot be overloaded, so you have to use tuples.

[JordanMarr]

That seems reasonable to add an overload that takes a tuple input. It would still be possible to use pipe operator if the cancel token was tupled along with the query.