How to stream data directly from the database through a 3 tier architecture

Question

The problem: generating a CSV file that is too large be stored in memory in a 3 tier architecture, without the complexity of saving the file to storage.

The solution: I've managed to write something that will stream directly from the database into the user's browser, through two WebApi controllers (simulating two tiers in this case)

A request is made to the front end WebApi, which makes a HttpClient call to the back end WebApi:

[HttpGet]
[Route("getFrontEndWebApi")]
public async Task<HttpResponseMessage> GetFrontEndWebApi()
{
    var httpClient = new HttpClient();

    var dataApiStream = await httpClient.GetStreamAsync("getBackEndWebApi");

    var response = Request.CreateResponse();

    response.Content = new PushStreamContent(async (stream, content, context) =>
    {
        await dataApiStream.CopyToAsync(stream);
        dataApiStream.Close();
        stream.Close();
    });

    response.Content.Headers.ContentType = new MediaTypeHeaderValue("text/csv");
    response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
    {
        FileName = $"CrossingTheStreams.csv"
    };

    return response;
}

The back end WebApi compiles the CSV by iterating over an IEnumerable and writes it out to a stream:

[HttpGet]
[Route("getBackEndWebApi")]
public async Task<HttpResponseMessage> GetBackEndWebApi()
{
    var results = ReadFromTable();
    var response = Request.CreateResponse();

    response.Content = new PushStreamContent(async (stream, content, context) =>
    {
        using (var writer = new StreamWriter(stream))
        {
            foreach (var result in results)
            {
                await writer.WriteLineAsync($"{result.Id},{result.SomeBullshit}");
            }
        }
    });

    return response;
}

Some junk code simulates a forward only DataReader connection to a database etc.

private IEnumerable<FishyFish> ReadFromTable()
{
    var table = new DataTable();

    table.Columns.Add("Id", typeof(int));
    table.Columns.Add("SomethingElse", typeof(string));

    for (int i = 0; i < 1000000; i++)
    {
        table.Rows.Add(new object[] { i, Guid.NewGuid().ToString() });
    }

    using (var reader = table.CreateDataReader())
    {
        do
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    yield return new FishyFish()
                    {
                        Id = reader.GetInt32(0),
                        SomeValue = reader.GetString(1)
                    };
                }
            }
        } while (reader.NextResult());
    }
}

public class FishyFish
{
    public int Id { get; set; }
    public string SomeValue { get; set; }
}

When you hit a breakpoint on yield return you can see that results have made it to the browser before the foreach has iterated over every value, thus streaming straight to the browser without compiling the file in memory.

Can this be done more sensibly? Are there any obvious flaws in this code?

Update for ASP.NET Core 2021-01-19

PushStreamContent doesn't exist in ASP.NET Core. It seems you have access to the Response Stream via HttpContext.Response.Body, which certainly has the potential for abuse, but there's nothing stopping you from writing your own implementation of PushStreamContentResult,

public class PushStreamContentResult : IActionResult
{
    private readonly Func<Stream, Task> _onStreamAvailable;

    public PushStreamContentResult(Func<Stream, Task> onStreamAvailable)
    {
        _onStreamAvailable = onStreamAvailable ?? throw new ArgumentNullException(nameof(onStreamAvailable));
    }

    public Task ExecuteResultAsync(ActionContext context)
    {
        return _onStreamAvailable(context.HttpContext.Response.Body);
    }
}

However I prefer a more strongly typed approach:

public class CsvResult<T> : IActionResult
    where T : class
{
    private readonly string _fileName;
    private readonly IEnumerable<T> _records;

    public CsvResult(string fileName, IEnumerable<T> records)
    {
        if (string.IsNullOrWhiteSpace(fileName))
        {
            throw new ArgumentException($"'{nameof(fileName)}' cannot be null or whitespace.", nameof(fileName));
        }

        if (fileName.IndexOfAny(Path.GetInvalidFileNameChars()) > -1)
        {
            throw new ArgumentException($"'{nameof(fileName)}' contains invalid characters.", nameof(fileName));
        }

        if (records == null)
        {
            throw new ArgumentNullException(nameof(records));
        }

        _fileName = fileName;
        _records = records;
    }

    public async Task ExecuteResultAsync(ActionContext context)
    {
        context.HttpContext.Response.StatusCode = StatusCodes.Status200OK;
        context.HttpContext.Response.Headers.Add(HeaderNames.ContentType, "text/csv");
        context.HttpContext.Response.Headers.Add(
            HeaderNames.ContentDisposition,
            new ContentDispositionHeaderValue("attachment")
            {
                FileName = _fileName
            }.ToString());

        await using (var stream = context.HttpContext.Response.Body)
        await using (var writer = new StreamWriter(stream))
        {
            
            foreach (var record in _records)
            {
                await writer.WriteLineAsync(record.ToString());
                await writer.FlushAsync();
            }
        }
    }
}