IEnumerable for QueryAsync

[shaunbowe]

The behavior for Query and QueryAsync are different. Query returns an IEnumerable and does not execute the query until the results are enumerated. QueryAsync returns IAsyncReader and the query is executed immediately.

Is it possible to return IEnumerable instead of IAsyncReader for the QueryAsync method?

[pleb]

@shaunbowe, the reason for this is history. At the time I implemented the Async methods the IAsyncEnumerable method and c# 8 syntax wasn't available based on our support targets.

I'll take your word for the immediate execution of the query, as I haven't tested this. I will say, however, the point of the Query/QueryAsync APIs is to stream the results from the DB as one iterates through the result set. On this point, the two APIs are the same.

It might be time to add support for IAsyncEnumerable, though now there's the problem of where to place it, as the API name QueryAsync is taken. Maybe QueryEnumerableAsync. Yuk, but I can't see a way around this. Another option is to roll v7 and add a breaking change of replacing IAsyncReader with IAsyncEnumerable. I could, of course, include the AsyncReader and modify it work with IAsyncEnumerable, which would at least give people an easy path to upgrade existing code.

[shaunbowe]

I kind of figured that was the reason. If it was my project I would add it as QueryEnumerableAsync in v6 and change it to QueryAsync in v7. I'd be more than happy to submit a PR or help on this if we can agree on a strategy. Thanks for the response.

[pleb]

The problem with supporting this in v6 is that it targets

<TargetFrameworks>net40;net45;netstandard2.0</TargetFrameworks>

and none of those support IAsyncEnumerable

[iraSenthil]

@pleb Do you think it is possible to write a wrapper on top of existing petapoco async api to make IAsyncEnumerable work? perhaps using IAsyncReader?

[shaunbowe]

We don't necessarily need IAsyncEnumerable. It seems Dapper has not yet converted to IAsyncEnumerable yet either. They do however support IEnumerable.

https://github.com/StackExchange/Dapper/blob/6ec3804f2c44f2bf6b757dc3522bf009cc64b27d/Dapper/SqlMapper.Async.cs#L939

I am willing to take a shot at converting this/adding this feature but would like some guidance about what version to create this in and how to proceed.

[pleb]

@shaunbowe the Dapper one is a workaround, and I have my doubts as to whether it is a true async version.

@iraSenthil

I created this for you (I was using net5 and the lastest c# version)

public class AsyncEnumerableHelper<T> : IAsyncEnumerable<T>
{
    private readonly IAsyncReader<T> _source;

    public AsyncEnumerableHelper(IAsyncReader<T> source)
        => _source = source;

    public IAsyncEnumerator<T> GetAsyncEnumerator(CancellationToken cancellationToken = new CancellationToken())
        => new AsyncEnumeratorHelper<T>(_source);
}

public class AsyncEnumeratorHelper<T> : IAsyncEnumerator<T>
{
    private readonly IAsyncReader<T> _source;

    public AsyncEnumeratorHelper(IAsyncReader<T> source)
        => _source = source;

    public ValueTask DisposeAsync()
    {
        _source.Dispose();
        return ValueTask.CompletedTask;
    }

    public ValueTask<bool> MoveNextAsync()
        => new(_source.ReadAsync());

    public T Current => _source.Poco;
}

public static class AsyncReaderExtensions
{
    public static AsyncEnumerableHelper<T> ConvertToAsyncIterator<T>(this Task<IAsyncReader<T>> source)
        => new(source.Result);
}

and it seems to work

    [Fact]
    public async void QueryAsyncReaderTest_ForPocoGivenSql_ShouldReturnValidPocoCollection()
    {
        AddOrders(12);
        var pd = PocoData.ForType(typeof(Order), DB.DefaultMapper);
        var sql = new Sql($"WHERE {DB.Provider.EscapeSqlIdentifier(pd.Columns.Values.First(c => c.PropertyInfo.Name == "Status").ColumnName)} = @0", OrderStatus.Pending);

        var results = new List<Order>();

        await foreach (var order in DB.QueryAsync<Order>(sql).ConvertToAsyncIterator())
            results.Add(order);

        results.Count.ShouldBe(3);

        results.ForEach(o =>
        {
            o.PoNumber.ShouldStartWith("PO");
            o.Status.ShouldBeOneOf(Enum.GetValues(typeof(OrderStatus)).Cast<OrderStatus>().ToArray());
            o.PersonId.ShouldNotBe(Guid.Empty);
            o.CreatedOn.ShouldBeLessThanOrEqualTo(new DateTime(1990, 1, 1, 0, 0, 0, DateTimeKind.Utc));
            o.CreatedBy.ShouldStartWith("Harry");
        });
    }

the magic part

        await foreach (var order in DB.QueryAsync<Order>(sql).ConvertToAsyncIterator())
            results.Add(order);

[iraSenthil]

@pleb Thank you very much. I will check it out. I would like to buy a beer. I don't see anyways to do that. Do you have patreon link or something similar?

[pleb]

@pleb Thank you very much. I will check it out. I would like to buy a beer. I don't see anyways to do that. Do you have patreon link or something similar?

Thanks, @iraSenthil, but there's really no need. Happy to help