Make a get api that returns a BLOB .net core (EF)

Question

I have a database with a table that has an id and a BLOB. What i want is to get the blob and essentially stream it to the client, and not retrieve it in memory. That is because the blob can be really big (100s of MB).

I have almost achieved this using SQLdataReader. Doing this, when i make a get request, it starts downloading immediately, and there is no overhead on memory. This was using .net core 2.2 However, when I switched to .net core 3.1, now it doesn't work, and the memory gets filled up with 100s of megabytes. I would also like to achieve the same result using entity framework, but till now i haven't found any good online resources.

Here is the code I have up until now.

[HttpGet]
    public  IEnumerable<Byte[]> GetStreaming()
    {

        var header = Request.Headers["Accept-Encoding"];
        System.Console.WriteLine(header);

        string connectionString = "Data Source=myPc\\SQLEXPRESS;Initial Catalog=dbo;Persist Security Info=True;User ID=sa;Password=admin";

        //----------------------
        SqlConnection connection = new SqlConnection(connectionString);

        connection.Open();

        SqlCommand command = new SqlCommand("exec getBlob", connection);

        SqlTransaction tran = connection.BeginTransaction(IsolationLevel.ReadCommitted);
        command.Transaction = tran;
        byte[] buffer = new byte[5000000];
        long position = 0;
        long bytesRead = 5000000;
        using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
        {
            reader.Read();
            reader.GetBytes(0, position, null, 0, 5000000);
            while (bytesRead == 5000000)
            {
                // Get the pointer for file
                bytesRead = reader.GetBytes(0, position, buffer, 0, 5000000);
                position += bytesRead;
                yield return buffer;

                // Create the SqlFileStream

            }

        }

    }

I've tried just doing it like this

        public  IEnumerable<byte[]> DownloadFile()
    {

        yield return  context.t_noFs.Where(u => u.Id == 1)
            .Select(u => u.dat).
            FirstOrDefault();
    }

and while this does return a value, it doesn't have the desired effect and fills the memory up.

Stanislav Dontsov · Accepted Answer · 2020-01-31 10:45:54Z

2

Best practice in your case would be to use streams.

[HttpGet]
public  FileStreamResult GetStreaming()
{
    var header = Request.Headers["Accept-Encoding"];
    System.Console.WriteLine(header);

    string connectionString = "Data Source=myPc\\SQLEXPRESS;Initial Catalog=dbo;Persist Security Info=True;User ID=sa;Password=admin";

    //----------------------
    SqlConnection connection = new SqlConnection(connectionString);

    connection.Open();

    SqlCommand command = new SqlCommand("exec getBlob", connection);

    SqlTransaction tran = connection.BeginTransaction(IsolationLevel.ReadCommitted);
    command.Transaction = tran;
    SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess)
    var blobStream = reader.GetStream(0);

    return new FileStreamResult(blobStream, "application/x-binary")
    {
         FileDownloadName = "your file name"
    };
}

edited Jan 31, 2020 at 10:45

answered Jan 31, 2020 at 10:08

Stanislav Dontsov

1,74915 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

wonder Over a year ago

im getting an internal server error with this. Is there some other information that I didn't provide that could be the culprit to this problem? does this require the latest versions of .net core and EF?

Stanislav Dontsov Over a year ago

What is the exact exception you are getting?

wonder Over a year ago

It said that it couldn't access disposed object. I removed the using () from around the SqlDataReader and now it downloaded the file

Stanislav Dontsov Over a year ago

Might be, I didn't test it it's just an example of how it should be used. But I will update it.

David Browne - Microsoft Over a year ago

You need to delay Dispose() on the SqlDataReader and SqlConnection until the end of the request. Add Response.RegisterForDispose(reader); and Response.RegisterForDispose(connection); Disposing them early will cause an error, and not disposing them will cause a connection leak.

|

Simon MᶜKenzie · Accepted Answer · 2025-10-26 08:41:40Z

This is an IQueryable-integrated implementation of Stanislav's answer, including David Browne's delayed Dispose() suggestion, implemented as an extension method:

public static async Task<Stream?> FirstOrDefaultAsStreamAsync(this IQueryable<byte[]> query,  HttpResponse response)
{
    var command = query.CreateDbCommand();

    await command.Connection!.OpenAsync();
    // Note: Choose an appropriate level of transaction isolation for your use-case
    var transaction = await command.Connection.BeginTransactionAsync(IsolationLevel.ReadCommitted);
    command.Transaction = transaction;

    var reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess);

    if (!await reader.ReadAsync())
    {
        await reader.DisposeAsync();
        await command.Connection.CloseAsync();

        return null;
    }

    response.RegisterForDisposeAsync(command.Connection);
    response.RegisterForDisposeAsync(reader);

    return reader.GetStream(0);
}

Example usage:

var stream = await context.Images
    .Where(t => t.Id == id)
    .Select(t => t.ImageBytes)
    .FirstOrDefaultAsStreamAsync(Response);

Collectives™ on Stack Overflow

Make a get api that returns a BLOB .net core (EF)

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related