Streaming varbinary(max) data using Entity Framework 6

.net c# entity-framework entity-framework-6 sql-server


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

10/4/2014 6:39:26 PM

Popular Answer

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);
2/16/2018 12:22:15 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow