Use QueryUnbufferedAsync for streaming IAsyncEnumerable results

[Copilot]

Dapper added QueryUnbufferedAsync returning IAsyncEnumerable<T> in version 2.1+, eliminating the need to buffer results when using ToAsyncEnumerable().

Changes

• Updated ListImpl: Changed to return IAsyncEnumerable<T> using Dapper's QueryUnbufferedAsync to stream results directly from the database
• Updated ToAsyncEnumerable: Now directly returns ListImpl for true streaming (no wrapper needed)
• Updated ListAsync: Buffers results from the async enumerable into a List<T>
• Removed resolved TODOs: Deleted comments about waiting for Dapper IAsyncEnumerable support
• Applied to both Query<T> and QueryIndex<T>: Same pattern applied consistently across both implementations

Before/After

Before:

async IAsyncEnumerable<T> IQuery<T>.ToAsyncEnumerable(CancellationToken cancellationToken)
{
    // TODO: Once Dapper supports IAsyncEnumerable we can replace this call by a non-buffered one
    foreach (var item in await ListImpl(cancellationToken))  // Buffers all results
    {
        yield return item;
    }
}

internal async Task<IEnumerable<T>> ListImpl(CancellationToken cancellationToken)
{
    // ... uses QueryAsync with CommandFlags.Buffered
}

After:

IAsyncEnumerable<T> IQuery<T>.ToAsyncEnumerable(CancellationToken cancellationToken)
{
    return ListImpl(cancellationToken);  // Directly returns the stream
}

internal async IAsyncEnumerable<T> ListImpl([EnumeratorCancellation] CancellationToken cancellationToken)
{
    // ... uses QueryUnbufferedAsync for streaming
}

async Task<IEnumerable<T>> IQuery<T>.ListAsync(CancellationToken cancellationToken)
{
    var results = new List<T>();
    await foreach (var item in ListImpl(cancellationToken))
    {
        results.Add(item);
    }
    return results;
}

The new implementation streams results row-by-row using connection.QueryUnbufferedAsync<T>().WithCancellation(cancellationToken), reducing memory footprint for large result sets. This eliminates code duplication and follows the original TODO guidance to "return it by default, and buffer it in ListAsync instead."

• Fixes sebastienros/yessql#589

Original prompt

---

This section details on the original issue you should resolve

<issue_title>Use QueryUnbufferedAsync in DefaultQuery ToAsyncEnumerable</issue_title> <issue_description>I noticed that DefaultQuery still has a TODO here about updating it when Dapper supports IAsyncEnumerable.

Since this PR was merged last year, it seems like it should support everything for this TODO to be resolved, or am I wrong with this assumption?</issue_description>

<agent_instructions>LitstImpl can now return IAsyncEnumerable. Include other required packages for TFMs that require them.</agent_instructions>

Comments on the Issue (you are @copilot in this section)

@sebastienros Maybe? Thanks for submitting a PR if you can.

• Fixes sebastienros/yessql#589

---

✨ Let Copilot coding agent set things up for you — coding agent works faster and does higher quality work when set up for your repo.