Streaming varbinary(max) data using Entity Framework 6

Question

I have a SQL Server 2012 table that contains file records. Each record has a varbinary(max) column BlobData that represents the data stored in the file – data size can be over 1 GB and cannot fit into RAM, so I don't want it backed by a byte array. I would like to implement two streaming operations:

• sequential reading of a BlobData row in chunks into a buffer;
• sequential overwriting of aBlobData row in chunks using a buffer.

Using plain ADO.NET, a straightforward way to achieve this is by using SqlDataReader and UPDATE .WRITE():

// 1. Sequentially read varbinary(max) into a buffer
using (SqlDataReader reader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{   
    while (reader.Read())
    {
        byte[] buffer = new byte[size];

        while ((count = reader.GetBytes(0, offset, buffer, 0, buffer.Length)) > 0)
        {
            DoStuffWithBuffer(buffer);
            offset += count;
        }
    }
}

// 2. Sequentially overwrite varbinary(max) from a buffer - iterate:
UPDATE [File] SET [BlobData].WRITE(@buffer, @offset, @count)
WHERE [FileID]=@id

From what I know, such operations weren't within the scope of EF in the past and my best bet would be to simply stick to ADO.NET. However, I have noticed that EF6 has some new methods such as EntityDataReader.GetBytes() which, to quote MSDN, »reads a stream of bytes from the specified column, starting at location indicated by dataIndex , into the buffer, starting at the location indicated by bufferIndex«.

1. My understanding is that EntityDataReader.GetBytes() is similar to SqlDataReader.GetBytes() and should offer roughly similar performance – am I correct or are there other considerations?

2. Is there a way in EF6 to perform an operation similar to UPDATE .WRITE(buffer, offset, count) on a varbinary(max) column?

Other parts of my solution use EF6, so for the sake of consistency I would ideally like to implement all this using EF6 as well.

In the light of new EF6 features, can/should I implement this functionality using EF6, or should I stick to ADO.NET as has been suggested in the past? I'm using .NET 4.0.

Answer 1

A bit late maybe - but context.Database.ExecuteSqlCommand accepts streams as parameters, i.e

var id = 1234;
var blobStream = await someSource.ReadAsStreamAsync(); // obtain some stream
db.Database.ExecuteSqlCommand($"UPDATE MyTable set BlobColumn=@p0 
                                where Id=@p1", blobStream, id);