title: Streaming varbinary(max) data using Entity Framework 6
body: <p>I have a SQL Server 2012 table that contains file records. Each record has a <code>varbinary(max)</code> column <code>BlobData</code> 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:</p>
  
  <ul>
  <li>sequential reading of a <code>BlobData</code> row in chunks into a buffer;</li>
  <li>sequential overwriting of a<code>BlobData</code> row in chunks using a buffer.</li>
  </ul>
  
  <p>Using plain ADO.NET, a straightforward way to achieve this is by using <code>SqlDataReader</code> and <code>UPDATE .WRITE()</code>:</p>
  
  <pre><code>// 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)) &gt; 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
  </code></pre>
  
  <p>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 <code>EntityDataReader.GetBytes()</code> which, to quote <a href="http://msdn.microsoft.com/en-us/library/system.data.entity.core.entityclient.entitydatareader.getbytes%28v=vs.113%29.aspx" rel="noreferrer">MSDN</a>, »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«.</p>
  
  <ol>
  <li><p>My understanding is that <code>EntityDataReader.GetBytes()</code> is similar to <code>SqlDataReader.GetBytes()</code> and should offer roughly similar performance – am I correct or are there other considerations?</p></li>
  <li><p>Is there a way in EF6 to perform an operation similar to <code>UPDATE .WRITE(buffer, offset, count)</code> on a <code>varbinary(max)</code> column?</p></li>
  </ol>
  
  <p>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. </p>
  
  <p>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.</p>