Returning stream from an open DbConnection on WebApi: close connection when ending

Question

I'm reading a varbinary(max) field from a SQL database as a Stream... and I want to return that stream as a file.

I'm using EF Core, however EF Core mapping requires (or I don't know better) to map varbinary fields to byte arrays, and that means reading the whole field into memory, then creating a stream out of it to return from a WebApi controller.

So I'm wondering how can I return the stream correctly while being able to close the data reader. I know that ASP.NET Controllers, when returning streams as files (with FileStreamResult) do close the stream at the end, but in this case, I'd also need to close the connection (that I manually open) after the stream has been sent, and I'm not sure how to handle this.

The code would be equivalent to this (I've redacted the code to simplify it, there might be typos):

public async Task<IActionResult> GetFile(Guid fileId)
{
    var query = @$"SELECT FileBinary
                      FROM {SchemaName}.{TableName} 
                      WHERE {GetSqlColumnName(nameof(Id))} = @id";

    var idParameter = new SqlParameter("id", fileId) { DbType = DbType.Guid };
    await using var command = DbContext.Database.GetDbConnection().CreateCommand();
    command.CommandText = query;
    command.CommandType = CommandType.Text;
    command.Parameters.Add(idParameter);
    // DbContext would be the EF context
    await DbContext.Database.OpenConnectionAsync().ConfigureAwait(false);
    await using var reader = await command.ExecuteReaderAsync(CommandBehavior.SingleRow).ConfigureAwait(false);
    await reader.ReadAsync();
    var stream = reader.GetStream(0);
    return new FileStreamResult(stream, "application/pdf");
}

This code seems to work ok and the actual file seems to be streamed from the data reader directly to the http client... however on this code, the open database connection would keep open there (and if I'm not wrong, I heard that the dbcontext on EF, would reuse that connection from the pool, but if it was already open, it won't try to close it by itself, thus leaving an "always open" connection there). I might be wrong on this (and if I am, then this would solve it and there wouldn't be a problem).

In case it is actually left open (which I don't want), would there be any way to close it after the stream has been totally sent?

Answer 1

Taking @Steeeve 's idea in this comment, I created a wrapper around a Stream that includes both the data reader and the connection, and closes/disposes them when the Stream is closed/disposed.

Since the code is long, I've created a gist on github, although it's pretty straightforward code.

It seems to work fine