How to Return and Consume an IAsyncEnumerable with SqlDataReader

iasyncenumerable' exists in both
select iasyncenumerable
iasyncenumerable parallel
iasyncenumerable ef core
iasyncenumerable tolistasync
the source iqueryable doesn't implement iasyncenumerable
cosmos db iasyncenumerable
asp.net core iasyncenumerable

Please see the below two methods. The first returns an IAsyncEnumerable. The second tries to consume it.

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Threading;
using System.Threading.Tasks;

public static class SqlUtility
{
    public static async IAsyncEnumerable<IDataRecord> GetRecordsAsync(
        string connectionString, SqlParameter[] parameters, string commandText,
        [EnumeratorCancellation]CancellationToken cancellationToken)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
            using (SqlCommand command = new SqlCommand(commandText, connection))
            {
                command.Parameters.AddRange(parameters);
                using (var reader = await command.ExecuteReaderAsync()
                    .ConfigureAwait(false))
                {
                    while (await reader.ReadAsync().ConfigureAwait(false))
                    {
                        yield return reader;
                    }
                }
            }
        }
    }

    public static async Task Example()
    {
        const string connectionString =
            "Server=localhost;Database=[Redacted];Integrated Security=true";
        SqlParameter[] parameters = new SqlParameter[]
        {
            new SqlParameter("VideoID", SqlDbType.Int) { Value = 1000 }
        };
        const string commandText = "select * from Video where VideoID=@VideoID";
        IAsyncEnumerable<IDataRecord> records = GetRecordsAsync(connectionString,
            parameters, commandText, CancellationToken.None);
        IDataRecord firstRecord = await records.FirstAsync().ConfigureAwait(false);
        object videoID = firstRecord["VideoID"]; //Should be 1000.
        // Instead, I get this exception:
        // "Invalid attempt to call MetaData when reader is closed."
    }
}

When the code tries to read the resultant IDataReader (at object videoID = firstRecord["VideoID"];), I get this exception:

Invalid attempt to call MetaData when reader is closed.

This is because SqlDataReader is disposed. Can someone supply a recommended method for enumerating SqlDataReader in an asynchronous way so that each resultant record is available to the calling method? Thank you.


In this scenario, LINQ is not your friend, as FirstAsync is going to close the iterator before it returns the result, which isn't what ADO.NET expects; basically: don't use LINQ here, or at least: not in this way. You might be able to use something like Select to perform the projection while the sequence is still open, or it may be easier to just offload all the work here to a tool like Dapper. Or, to do it manually:

await foreach (var record in records)
{
    // TODO: process record
    // (perhaps "break"), because you only want the first
}

IAsyncEnumerable In C# 8, But We Already Have Async Enumerable Right? So a common trap to fall into might be that you want to use a return type of Task<IEnumerable<T>� Building C# 8.0 The next major version of C# is C# 8.0. It’s been in the works for quite some time, even as we built and shipped the minor releases C# 7.1, 7.2 and 7.3, and I’m quite excited about the new capabilities it will bring.


You can avoid this by not returning an object that depends on the connection still being open. For example, if you only need the VideoID, then just return that (I'm assuming it's an int):

public static async IAsyncEnumerable<int> GetRecordsAsync(string connectionString, SqlParameter[] parameters, string commandText, [EnumeratorCancellation]CancellationToken cancellationToken)
{
    ...
                    yield return reader["VideoID"];
    ...
}

Or project into your own class:

public class MyRecord {
    public int VideoId { get; set; }
}

public static async IAsyncEnumerable<MyRecord> GetRecordsAsync(string connectionString, SqlParameter[] parameters, string commandText, [EnumeratorCancellation]CancellationToken cancellationToken)
{
    ...
                    yield return new MyRecord {
                        VideoId = reader["VideoID"]
                    }
    ...
}

Or do what Marc suggested and use a foreach and break after the first one, which would look like this in your case:

IAsyncEnumerable<IDataRecord> records = GetRecordsAsync(connectionString, parameters, commandText, CancellationToken.None);
object videoID;
await foreach (var record in records)
{
    videoID = record["VideoID"];
    break;
}

Add a fast and convenient deserialization API to ADO.NET � Issue , Alternatively an API consumer would use a factory to create a "fast Async support ( IAsyncEnumerable or other suitable mechanisms). @roji FWIW, what I mean is that when you consume an IEnumerable<T> , the object returned by So maybe it's just an internal SqlDataReader fix but given how the� Stack Overflow | The World’s Largest Online Community for Developers


When you expose an open DataReader, the reponsibility of closing it along with the underlying Connection belongs now to the caller, so you should not dispose anything. Instead you should use the DbCommand.ExecuteReaderAsync overload that accepts a CommandBehavior argument, and pass the CommandBehavior.CloseConnection value:

When the command is executed, the associated Connection object is closed when the associated DataReader object is closed.

Then you can just hope that the caller will play by the rules and call the DataReader.Close method promptly, and will not let the connection open until the object is garbage collected. For this reason exposing an open DataReader should be considered an extreme performance optimization technique, that should be used sparingly.

Btw you would have the same problem if you returned an IEnumerable<IDataRecord> instead of an IAsyncEnumerable<IDataRecord>.

Asynchronous Programming, Using SqlDataReader's new async methods in .NET 4.5 (Part 1) When calling an async method, a task is returned. When the await operator� consume the data from a source that works in slower manner than you can consume it. Just a few examples ?. How will it work? Well, imagine you are the source of data. You write an async method and because you want the lazy approach (because when you are lazy, your code should be lazy too, right? Hahaha, OMG! Haha, yeah, weak jokes are my


Before C# 8.0 Async Streams Come Out, ExecuteReader()) { while (reader. Read()) { var row = GetRowValues(reader); yield return row; // Produce an item. } } } } void ConsumeRows() { var rowStream = GetRows(); foreach (var row in rowStream) ProcessRow(row); // Consume an item Async; IAsyncEnumerable<Row> ProduceRows() => // <-- new interface new� No, you can't currently use async with an iterator block. As svick says, you would need something like IAsyncEnumerable to do that.. If you have the return value Task<IEnumerable<SomeClass>> it means that the function returns a single Task object that, once completed, will provide you with a fully formed IEnumerable (no room for Task asynchrony in this enumerable).


Questions tagged [iasyncenumerable], How to Return and Consume an IAsyncEnumerable with SqlDataReader. Please see the below two methods. The first returns an IAsyncEnumerable. tl;dr Iterators as implemented with yield are a blocking construct, so as of right now await and yield are incompatible.. Long Because iterating over an IEnumerable is a blocking operation, calling a method marked as async will still execute it in a blocking manner, since it has to wait for that operation to finish.


Will the Database connection be closed if we yield the datareader , Currently you're returning an IEnumerable<IDataRecord> , a data structure When you use yield return the compiler will create a new nested� How to use Generic with SqlDataReader. 2641 views September 2018 c# 1 . DC. How to Return and Consume an IAsyncEnumerable with SqlDataReader November 2019 .