Storing large files in database causes An exceptio

问题:

I've built a small MVC4 web site for sharing files with the code first approach. The files are stored in my database as

public byte[] Content { get; set; }

Ive set

<security>
  <requestFiltering>
    <requestLimits maxAllowedContentLength="536870912" />
  </requestFiltering>
</security>

and

<httpRuntime targetFramework="4.5" executionTimeout="6000" maxRequestLength="524288"/>

To limit filesize to 500 Mb so the file is actually making it into my code (was stuck on this a bit first).

The actual problem is happening when I have received the file and try to save it to the database, I call the:

DbContext.SaveChanges()

And get this error:

An exception of type 'System.OutOfMemoryException' occurred in System.Data.Entity.dll but was not handled in user code

I'm guessing this is because I am hitting some sort of limit as how much data can be stored in a byte[] in the database (or how much memory my process is allowed to use).. Everything works when uploading smaller files.

The database server is an SQL 2008 R2 Standard server.

I would prefer to avoid storing the files on the disk for sake of simplicity.. what are my options?

EDIT: Using the suggestion of doing a straight up SQL query to insert the file got me past the first problem of getting the file into the database:

DbContext.Database.ExecuteSqlCommand(
    @"UPDATE FileContents
        SET Content = @data 
        WHERE ID = @id",
new SqlParameter("id", content.ID), new SqlParameter("data", bytearray));

But I am now getting the exact same error when trying to get the file out of the database instead. This causes the error now:

byte[] respdata = DbContext.Database.SqlQuery<Byte[]>
                            ("SELECT TOP 1 Content FROM FileContents WHERE ID = @id", 
                            new SqlParameter("id", filepost.File.ID)).Single();

Again, it is working for smaller files <100 MB but crashing on 200 Mb files.

Adding stacktrace to question as per a comment in the answers below:

System.OutOfMemoryException was unhandled by user code
HResult=-2147024882 Message=Exception of type 'System.OutOfMemoryException' was thrown. Source=mscorlib
StackTrace: at System.Object.MemberwiseClone() at System.Array.Clone() at System.Data.Common.CommandTrees.DbConstantExpression..ctor(TypeUsage resultType, Object value) at System.Data.Mapping.Update.Internal.UpdateCompiler.GenerateValueExpression(EdmProperty property, PropagatorResult value) at System.Data.Mapping.Update.Internal.UpdateCompiler.BuildSetClauses(DbExpressionBinding target, PropagatorResult row, PropagatorResult originalRow, TableChangeProcessor processor, Boolean insertMode, Dictionary2& outputIdentifiers, DbExpression& returning, Boolean& rowMustBeTouched) at System.Data.Mapping.Update.Internal.UpdateCompiler.BuildInsertCommand(PropagatorResult newRow, TableChangeProcessor processor) at System.Data.Mapping.Update.Internal.TableChangeProcessor.CompileCommands(ChangeNode changeNode, UpdateCompiler compiler) at System.Data.Mapping.Update.Internal.UpdateTranslator.<ProduceDynamicCommands>d__0.MoveNext() at System.Linq.Enumerable.<ConcatIterator>d__711.MoveNext() at System.Data.Mapping.Update.Internal.UpdateCommandOrderer..ctor(IEnumerable1 commands, UpdateTranslator translator) at System.Data.Mapping.Update.Internal.UpdateTranslator.ProduceCommands() at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache) at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options) at System.Data.Entity.Internal.InternalContext.SaveChanges() at System.Data.Entity.Internal.LazyInternalContext.SaveChanges() at System.Data.Entity.DbContext.SaveChanges() at FilePublicator2.Controllers.FileController.Upload(String qqfile) in d:\Projects\FilePublicator2\trunk\FilePublicator2\Controllers\FileController.cs:line 115 at lambda_method(Closure , ControllerBase , Object[] ) at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass42.<BeginInvokeSynchronousActionMethod>b__41() at System.Web.Mvc.Async.AsyncResultWrapper.<>c__DisplayClass81.b__7(IAsyncResult ) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.End() at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c_DisplayClass37.<>c_DisplayClass39.b_33() at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c_DisplayClass4f.b_49() InnerException:

Solution:

Here is a complete example of how this was solved:

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

回答1:

OutOfMemory has nothing to do with any framework or database. It means that the CLR couldn't allocate an object when asked to.

Probably, you are having the data in multiple buffers like one in an HttpPostedFile, one in an in-memory byte[], one in EF, ... This multiplies the amount of memory needed.

Switch to streaming reads from ASP.NET and streaming writes to ADO.NET. Tutorials are available. Search for "SQL Server UPDATE WRITE"

回答2:

You might try bypassing the Entity Framework and using ADO.Net (System.Data.SqlClient.SqlCommand, etc.) directly.