IAsyncEnumerable and database queries

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;
}