Extension method to asynchronously query a database and build a dynamic result set

BasicDynamicQuery.cs

public static async Task<IEnumerable<dynamic>> QueryAsync(this DbConnection db, string sql)
{
    var records = new List<dynamic>();

    using (var cmd = db.CreateCommand())
    {
        cmd.CommandText = sql;

        using (var reader = await cmd.ExecuteReaderAsync())
        {
            var columns = Enumerable.Range(0, reader.FieldCount)
                                    .ToDictionary(reader.GetName, index => index);

            while (await reader.ReadAsync())
            {
                dynamic record = new ExpandoObject();
                var dict = record as IDictionary<string, object>;

                foreach (var column in columns)
                {
                    dict[column.Key] = reader.GetValue(column.Value);
                }

                records.Add(record);
            }
        }
    }

    return records.ToArray();
}

// usage
using (var db = new SqlConnection(connectionString)) // any connection inheriting DbConnection will do
{
    db.Open();
    
    var results = await db.QueryAsync("SELECT * FROM Foo");
}