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?

I would implement an own stream class, which holds the original stream and the reader, forwarding aall stream-methods to the base stream and closing both base stream and the reader on close/dispose. — Steeeve, Oct 23 '21 at 07:44
@Steeeve I was thinking of implementing a custom result for the webapi controller, however that required coupling some unrelated assemblies. Your solution sounds actually better. I'll try that — Jcl, Oct 24 '21 at 11:49

score 1 · Accepted Answer · answered Oct 25 '21 at 09:41

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

answered Oct 25 '21 at 09:41

Jcl

27,696
5
61
92

1

You are missing two `?` operators in the `Close` method ;) – Steeeve Oct 25 '21 at 16:41
@Steeeve yeah, caught those with some tests, but didn't update the gist. I'll do now :-) – Jcl Oct 25 '21 at 17:18
I assume you could specify [CommandBehavior.CloseConnection](https://learn.microsoft.com/en-us/dotnet/api/system.data.commandbehavior?view=net-6.0) and include only the reader. – marsze Jul 26 '22 at 09:33

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

1 Answers1