为System.OutOfMemoryException - 当实体框架是查询VARBINARY类型的过大数据 [英] System.OutOfMemoryException - when Entity Framework is querying a too big data of Varbinary type
问题描述
我试图查询包含一个文件（1,2 GB）的一个 VARBINARY 列。
I'm trying to query a varbinary column that contain a file (1,2 Gb).
我使用实体框架。见下图：
I'm using Entity Framework. See below:
数据库来测试
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]
数据测试（有1 GB的文件）
Data to test (any file with 1 Gb)
INSERT INTO [dbo].[BIGDATA]([BIGDATA])
VALUES
((SELECT BulkColumn FROM OPENROWSET(BULK N'C:\BigTest.txt', SINGLE_BLOB) AS Document))
控制器下载文件
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;
}
}
我可以在SQL Server上正常查询与 SELECT * FROM BigData 。
不过，在实体框架（或使用ADO命令）我得到这个异常：
But, in Entity Framework (or command with ADO) I get this exception:
的System.OutOfMemoryException
System.OutOfMemoryException
是否有人知道如何解决这个问题？
Does someone know how fix this problem?
推荐答案
哇这是一个很大的数据。我真的觉得你需要不使用EF得到这个数据，而是用好'醇SqlDataReader的。
Wow that is a lot data. I really think you need to not use EF to get this data, but instead use the good 'ol SqlDataReader.
鉴于你的.NET 4.0的限制，我发现了一个自定义实现流媒体从一个巨大的VARBINARY列读。我不能接受任何信贷这个以外审查代码，并确保有没有在它.NET 4.5的快捷方式：
Given your .net 4.0 restriction, I found a custom implementation of streaming a read from a massive varbinary column. I can't take any credit for this other than reviewing the code and making sure there are not .net 4.5 shortcuts in it:
的 http://www.syntaxwarriors.com/2013/stream-varbinary-数据 - 和 - 从 - 使用 - CSHARP MSSQL-/
MODS的 - 让我知道如果这样的事情应该是复制/粘贴到答案。作为原始URL可能不会持久的。
Mods - let me know if something like this should be copy/pasted into the answer as the original URL may not be persistent.
编辑：
下面是从的情况下，该链接的代码URL消失：
Here is the code from the link in case the URL goes away:
用法：
// 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 code:
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
}
这篇关于为System.OutOfMemoryException - 当实体框架是查询VARBINARY类型的过大数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
