IAsyncEnumerable and database queries

Question

I have three controller methods returning IAsyncEnumerable of WeatherForecast. The first one #1 uses SqlConnection and yields results from an async reader. The second one #2 uses EF Core with the ability to use AsAsyncEnumerable extension. The third one #3 uses EF Core and ToListAsync method.

I think the downside of #1 and #2 is if I, for example, do something time-consuming inside while or for each then the database connection will be open till the end. In scenario #3 I'm able to iterate over the list with a closed connection and do something else.

But, I don't know if IAsyncEnumerable makes sense at all for database queries. Are there any memory and performance issues? If I use IAsyncEnumerable for returning let's say HTTP request from API, then once a response is returned it's not in memory and I'm able to return the next one and so on. But what about the database, where is the whole table if I select all rows (with IAsyncEnumerable or ToListAsync)?

Maybe it's not a question for StackOverflow and I'm missing something big here.

#1

[HttpGet("db", Name = "GetWeatherForecastAsyncEnumerableDatabase")]
public async IAsyncEnumerable<WeatherForecast> GetAsyncEnumerableDatabase()
{
    var connectionString = "";
    await using var connection = new SqlConnection(connectionString);

    string sql = "SELECT * FROM [dbo].[Table]";
    await using SqlCommand command = new SqlCommand(sql, connection);

    connection.Open();
    await using var dataReader = await command.ExecuteReaderAsync();
    while (await dataReader.ReadAsync())
    {
        yield return new WeatherForecast
        {
            Date = Convert.ToDateTime(dataReader["Date"]),
            Summary = Convert.ToString(dataReader["Summary"]),
            TemperatureC = Convert.ToInt32(dataReader["TemperatureC"])
        };
    }

    await connection.CloseAsync();
}

#2

[HttpGet("ef", Name = "GetWeatherForecastAsyncEnumerableEf")]
public async IAsyncEnumerable<WeatherForecast> GetAsyncEnumerableEf()
{
    await using var dbContext = _dbContextFactory.CreateDbContext();
    await foreach (var item in dbContext
        .Tables
        .AsNoTracking()
        .AsAsyncEnumerable())
    {
        yield return new WeatherForecast
        {
            Date = item.Date,
            Summary = item.Summary,
            TemperatureC = item.TemperatureC
        };
    }
}

#3

[HttpGet("eflist", Name = "GetWeatherForecastAsyncEnumerableEfList")]
public async Task<IEnumerable<WeatherForecast>> GetAsyncEnumerableEfList()
{
    await using var dbContext = _dbContextFactory.CreateDbContext();
    var result =  await dbContext
        .Tables
        .AsNoTracking()
        .Select(item => new WeatherForecast
        {
            Date = item.Date,
            Summary = item.Summary,
            TemperatureC = item.TemperatureC
        })
        .ToListAsync();

    return result;
}

Answer 1

Server-Side

If I only cared with the server I'd go with option 4 in .NET 6 :

• Use an injected DbContext, write a LINQ query and return the results as AsAsyncEnumerable() instead of ToListAsync()

public class WeatherForecastsController:ControllerBase
{
    WeatherDbContext _dbContext;
    public WeatherForecastsController(WeatherDbContext dbContext)
    {
        _dbContext=dbContext;
    }

    public async IAsyncEnumerable<WeatherForecast> GetAsync()
    {
        return _dbContext.Forecasts.AsNoTracking()
                         .Select(item => new WeatherForecast
                         {
                             Date = item.Date,
                             Summary = item.Summary,
                             TemperatureC = item.TemperatureC
                         })
                         .AsAsyncEnumerable();
    }
}

A new Controller instance is created for every request which mean the DbContext will be around for as long as the request is being processed.

The [FromServices] attribute can be used to inject a DbContext into the action method directly. The behavior doesn't really change, the DbContext is still scoped to the request :

public async IAsyncEnumerable<WeatherForecast> GetAsync([FromServices] WeatherContext dbContext)
{
    ...
}

ASP.NET Core will emit a JSON array but at least the elements will be sent to the caller as soon as they're available.

Client-Side

The client will still have to receive the entire JSON array before deserialization.

One way to handle this in .NET 6 is to use DeserializeAsyncEnumerable to parse the response stream and emit items as they come:

using var stream=await client.GetAsStreamAsync(...);
var forecasts= JsonSerializer.DeserializeAsyncEnumerable(stream, new JsonSerializerOptions
        {
            DefaultBufferSize = 128
        });
await foreach(var forecast in forecasts)
{
...
}

The default buffer size is 16KB so a smaller one is needed if we want to receive objects as soon as possible.

This is a parser-specific solution though.

Use a streaming JSON response

A common workaround to this problem is to use streaming JSON aka JSON per line, aka Newline Delimited JSON aka JSON-NL or whatever. All names refer to the same thing - sending a stream of unindented JSON objects separated by a newline. It's an old technique that many tried to hijack and present as their own

{ "Date": "2022-10-18", Summary = "Blah", "TemperatureC"=18.5 }
{ "Date": "2022-10-18", Summary = "Blah", "TemperatureC"=18.5 }
{ "Date": "2022-10-18", Summary = "Blah", "TemperatureC"=18.5 }

That's not valid JSON but many parsers can handle it. Even if a parser can't, we can simply read one line of text at a time and parse it.

Use a different protocol

Even streaming JSON responses is a workaround. HTTP doesn't allow server-side streaming in the first place. The server has to send all data even if the client only reads the first 3 items since there's no way to cancel the response.

It's more efficient to use a protocol that does allow streaming. ASP.NET Core 6 offers two options:

• Use ASP.NET Core SignalR with Server-side streaming to push data to clients over WebSockets
• A gRPC Service that uses server-side streaming

In both cases the server sends objects to clients as soon as they're available. Clients can cancel the stream as needed.

In a SignalR hub, the code could return an IAsyncEnumerable or a Channel:

public class AsyncEnumerableHub : Hub
{
    ...
    public async IAsyncEnumerable<WeatherForecast> GetForecasts()
    {
        return _dbContext.Forecasts.AsNoTracking()
                         .Select(item => new WeatherForecast
                         {
                             Date = item.Date,
                             Summary = item.Summary,
                             TemperatureC = item.TemperatureC
                         })
                         .AsAsyncEnumerable();
    }
}

In gRPC, the server method writes objects to a response stream :

public override async Task StreamingFromServer(ForecastRequest request,
    IServerStreamWriter<ForecastResponse> responseStream, ServerCallContext context)
{
    ...
    await foreach (var item in queryResults)
    {
        if (context.CancellationToken.IsCancellationRequested)
        {
            return;
        }
        await responseStream.WriteAsync(new ForecastResponse{Forecast=item});
    }
}