In ASP.Net Core, is it possible to start streaming JSON results?

So I know this question is old, but this is very much possible in Asp.Net Core 2.2 (probably even from earlier versions, ever since IEnumerable<T> was supported as a return result on an action).

While I'm not entirely familiar with postgres and DataReader, the functionality is there to get it streaming the result to the client. Appending to a list, and returning the result in its entirety takes up a lot of memory depending on the size of the result, and streaming helps us avoid that.

Here is an example of an action, that returns an IEnumerable<string> that is streamed to the client (it is sent in chunks until everything has been delivered using the Transfer-Encoding: chunked header).

[HttpGet]
public IEnumerable<string> Get()
{
    return GetStringsFor(10000);
}

private static readonly Random random = new Random();
private IEnumerable<string> GetStringsFor(int amount)
{
    const string chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
    while (amount-- > 0)
    {
        yield return new string(Enumerable.Repeat(chars, random.Next(1000)).Select(s => s[random.Next(s.Length)]).ToArray());
    }
}

This will ensure that not everything is loaded into memory, but sent on demand. You would be able to implement something similar in your case when you're reading to data into memory, because that is one time where the system could just start sending the result instead.

private IEnumerable<BearingTemperature> ReadTemperatures(SqlDataReader reader)
{
    if (reader.HasRows)
    {
        var bt = new BearingTemperature();

        while (reader.Read())
        {
            bt.Time = reader.GetDateTime(1);
            bt.Turbine = reader.GetInt32(0);
            bt.Value = reader.GetDouble(2);

            yield return bt;
        }
    }

    yield break;
}

[HttpGet("{turbine:int}")]
public IEnumerable<BearingTemperature> GetBearingTemperature(int turbine)
{
    using (var connection = Database.GetConnection())
    {
        <snip>

        var reader = command.ExecuteReader();
        return ReadTemperatures(reader);
    }
}

Considering that your database is going to execute the query and return the entire result set, it's not possible for you to stream a partial result set (though you can google streaming database for other offerings). What you could do instead is use a paging technique combined with ajax to retrieve slices of the total result set and compose them together on the client to keep the responsiveness high and create the illusion of streaming query results.

You'll want to look at OFFSET and LIMIT clauses

On your api, you'd include parameters for offset and limit to allow the client to step through and retrieve the result set in whatever size chunks it wants, you can play with it to determine what seems responsive enough. Then on your client, you'll need a loop over an ajax call to your api, probably using jquery, and keep looping page after page adding the results to the bound collection on the client or create ui elements, or whatever, until the results come back empty.

Alternatively, if showing the whole 10k records at once isn't necessary, you could simply page the results and provide an interface to step through the pages. One that I've used for such a purpose is from Sakura on git hub: PagedList