Consider making DbDataReader implement IAsyncEnumerable

[roji]

DbDataReader is used to stream rows from a database. Although it does implement IEnumerable (of DbDataRecord), that API is old and rarely-used (it's non-generic, sync-only and quite inefficient).

Consider making DbDataReader implement IAsyncEnumerable, to provide a modern and efficient API for going through the rows. This would simply be a layer implemented on top of DbDataReader's existing async operations (e.g. ReadAsync()), so it wouldn't require any sort of change from providers.

[davidfowl]

/cc @MadsTorgersen @stephentoub

[divega]

System.Data Triage: Moving to future since we are not doing this in 2.1.

If the timing of the introduction of IAsyncEnumerable is right, this could be the sole way we leverage ValueTask/IValueTaskSource for streaming rows in DbDataReader. Otherwise we may want to still have a ValueTask<bool> alternative for ReadAsync.

We should also understand if there are inherent inefficiencies in implementing IAsyncEnumerable at this layer, e.g. it seems we would be allocating a DbDataRecord (or similar) for each row, which in the most commonly used pattern for DbDataReader isn't necessary.

[julealgon]

@roji isn't this still desirable?

[roji]

@julealgon I'm not completely sure. It has been a very long time since I originally opened this, there has been almost no interest (5 upvotes), and the new API would be far from trivial (IAsyncEnumerable of what exactly? If of DbDataRecord, that ancient type probably needs to be extended to support new things; if we also allocate a new one per row, that's a lot of allocations that don't happen using DbDataReader today, etc. etc.).

So I think it's OK to leave this closed for now until new interest/needs come up around it.