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

Question

I am using ASP.Net Core WebAPI.

I have a method that retrieves 10000 results from the database at a time, but I notice that it takes 1.17s to "wait" and 0.3s for the actual transfer (based on Chrome's network graph).

With the results from the database (postgres) are iterated through the DataReader and converted into a struct, added to a list, and ultimately returned as a JsonResult.

I do not know what to expect exactly for options, but I would like to be able to start returning as soon as possible to make the total request lower. I am also doing this for the first time on this platform, so I may not be doing things the best way.

    [HttpGet("{turbine:int}")]
    public IActionResult GetBearingTemperature(int turbine)
    {
        using (var connection = Database.GetConnection())
        {
            connection.Open();

            int? page = GetPage();

            var command = connection.CreateCommand();

            if (page.HasValue)
            {
                command.CommandText = @"select turbine, timestamp, mainbearingtemperature from readings where turbine = :turbine limit 10000 offset :offset;";
                command.Parameters.AddWithValue("offset", NpgsqlTypes.NpgsqlDbType.Integer, page.Value * 10000);
            } else
            {
                command.CommandText = @"select turbine, timestamp, mainbearingtemperature from readings where turbine = :turbine limit 10000;";
            }

            command.Parameters.AddWithValue("turbine", NpgsqlTypes.NpgsqlDbType.Integer, 4, turbine);                

            var reader = command.ExecuteReader();

            var collection = new List<BearingTemperature>();

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

                    collection.Add(bt);
                }

                return new JsonResult(collection);
            }
            else
            {
                return new EmptyResult();
            }

        }
    }

    private int? GetPage()
    {
        if (Request.Query.ContainsKey("page"))
        {
            return int.Parse(Request.Query["page"]);
        }
        else return null;

    }

    struct BearingTemperature
    {
        public int Turbine;
        public DateTime Time;
        public double Value;
    }

Answer 1

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