System.OutOfMemoryException - when Entity Framework is querying a too big data of Varbinary type

c# entity-framework out-of-memory querying varbinary

Question

I'm attempting to ask avarbinary a file-containing column (1,2 Gb).

Use of Entity Framework is mine. Look below:

Test database

CREATE TABLE [dbo].[BIGDATA]
(
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [BIGDATA] [varbinary](max) NULL, 

    CONSTRAINT [PK_BIGDATA] PRIMARY KEY CLUSTERED ([id] ASC) 
) ON [PRIMARY]

Data for testing (any file with 1 Gb)

INSERT INTO [dbo].[BIGDATA]([BIGDATA])
VALUES
   ((SELECT BulkColumn FROM OPENROWSET(BULK N'C:\BigTest.txt', SINGLE_BLOB) AS Document))

File download via controller

public FileResult Download()
{
        try
        {
            var context = new Models.ELOGTESTEEntities();

            var idArquivo = Convert.ToInt32(1);

            // The problem is here, when trying send command to SQL Server to read register
            var arquivo = (from item in context.BIGDATA
                           where item.id.Equals(idArquivo)
                           select item).Single();
            var mimeType = ".txt";              

            byte[] bytes = System.Text.Encoding.GetEncoding("iso-8859-8").GetBytes("BigTest.txt");
            return File(arquivo.BIGDATA1, mimeType, System.Text.Encoding.UTF8.GetString(bytes));
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

I can perform standard SQL Server querying usingSelect * From BigData .

But I receive the following exception in Entity Framework (or a command with ADO):

System.OutOfMemoryException

Does anyone know how to solve this issue?

1
3
7/7/2014 2:43:16 PM

Accepted Answer

That much information, wow. I really believe that you should utilize the trusty SqlDataReader rather than EF to obtain this data.

I discovered a unique implementation of streaming a read from a sizable varbinary column due to your.net 4.0 constraint. With the exception of checking the code for.net 4.5 shortcuts, I can't claim any responsibility for this:

http://www.syntaxwarriors.com/2013/stream-varbinary-data-to-and-from-mssql-using-csharp/

As the original URL might not be permanent, moderators, please let me know whether something similar should be copied and pasted into the answer.

In case the URL changes, the code from the link is provided below:

Usage:

// reading and returning data to the client
VarbinaryStream filestream = new VarbinaryStream(
                                DbContext.Database.Connection.ConnectionString, 
                                "FileContents", 
                                "Content", 
                                "ID", 
                                (int)filepost.ID, 
                                true);

// Do what you want with the stream here.

The key:

public class VarbinaryStream : Stream
{
    private SqlConnection _Connection;

    private string  _TableName;
    private string  _BinaryColumn;
    private string  _KeyColumn;
    private int     _KeyValue;

    private long    _Offset;

    private SqlDataReader _SQLReader;
    private long _SQLReadPosition;

    private bool _AllowedToRead = false;

    public VarbinaryStream(
        string ConnectionString,
        string TableName,
        string BinaryColumn,
        string KeyColumn,
        int KeyValue,
        bool AllowRead = false)
    {
        // create own connection with the connection string.
        _Connection = new SqlConnection(ConnectionString);

        _TableName = TableName;
        _BinaryColumn = BinaryColumn;
        _KeyColumn = KeyColumn;
        _KeyValue = KeyValue;


        // only query the database for a result if we are going to be reading, otherwise skip.
        _AllowedToRead = AllowRead;
        if (_AllowedToRead == true)
        {
            try
            {
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                SqlCommand cmd = new SqlCommand(
                                @"SELECT TOP 1 [" + _BinaryColumn + @"]
                                FROM [dbo].[" + _TableName + @"]
                                WHERE [" + _KeyColumn + "] = @id",
                            _Connection);

                cmd.Parameters.Add(new SqlParameter("@id", _KeyValue));

                _SQLReader = cmd.ExecuteReader(
                    CommandBehavior.SequentialAccess |
                    CommandBehavior.SingleResult |
                    CommandBehavior.SingleRow |
                    CommandBehavior.CloseConnection);

                _SQLReader.Read();
            }
            catch (Exception e)
            {
                // log errors here
            }
        }
    }

    // this method will be called as part of the Stream ímplementation when we try to write to our VarbinaryStream class.
    public override void Write(byte[] buffer, int index, int count)
    {
        try
        {
            if (_Connection.State != ConnectionState.Open)
                _Connection.Open();

            if (_Offset == 0)
            {
                // for the first write we just send the bytes to the Column
                SqlCommand cmd = new SqlCommand(
                                            @"UPDATE [dbo].[" + _TableName + @"]
                                                SET [" + _BinaryColumn + @"] = @firstchunk 
                                            WHERE [" + _KeyColumn + "] = @id",
                                        _Connection);

                cmd.Parameters.Add(new SqlParameter("@firstchunk", buffer));
                cmd.Parameters.Add(new SqlParameter("@id", _KeyValue));

                cmd.ExecuteNonQuery();

                _Offset = count;
            }
            else
            {
                // for all updates after the first one we use the TSQL command .WRITE() to append the data in the database
                SqlCommand cmd = new SqlCommand(
                                        @"UPDATE [dbo].[" + _TableName + @"]
                                            SET [" + _BinaryColumn + @"].WRITE(@chunk, NULL, @length)
                                        WHERE [" + _KeyColumn + "] = @id",
                                     _Connection);

                cmd.Parameters.Add(new SqlParameter("@chunk", buffer));
                cmd.Parameters.Add(new SqlParameter("@length", count));
                cmd.Parameters.Add(new SqlParameter("@id", _KeyValue));

                cmd.ExecuteNonQuery();

                _Offset += count;
            }
        }
        catch (Exception e)
        {
            // log errors here
        }
    }

    // this method will be called as part of the Stream ímplementation when we try to read from our VarbinaryStream class.
    public override int Read(byte[] buffer, int offset, int count)
    {
        try
        {
            long bytesRead = _SQLReader.GetBytes(0, _SQLReadPosition, buffer, offset, count);
            _SQLReadPosition += bytesRead;
            return (int)bytesRead;
        }
        catch (Exception e)
        {
            // log errors here
        }
        return -1;
    }
    public override bool CanRead
    {
        get { return _AllowedToRead; }
    }

    #region unimplemented methods
    public override bool CanSeek
    {
        get { return false; }
    }

    public override bool CanWrite
    {
        get { return true; }
    }

    public override void Flush()
    {
        throw new NotImplementedException();
    }

    public override long Length
    {
        get { throw new NotImplementedException(); }
    }

    public override long Position
    {
        get
        {
            throw new NotImplementedException();
        }
        set
        {
            throw new NotImplementedException();
        }
    }
    public override long Seek(long offset, SeekOrigin origin)
    {
        throw new NotImplementedException();
    }

    public override void SetLength(long value)
    {
        throw new NotImplementedException();
    }
    #endregion unimplemented methods
}
2
7/24/2015 8:27:30 PM

Popular Answer

Incorporate the EF "AsNoTracking()" option when loading the data!

Sample:\sMyContext. MyTable.AsNoTracking(). Where (x ====x)



Related Questions





Related

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