EntityFramework - Sum() Causing OutOfMemoryException

Question

I have an MVC application, which uses EntityFramework to connect to MS SQL Server 2012. I am trying to retrieve the total file size of all documents stored in the Documents table.

I am using the following code:

this.Documents.Sum(x => x.FileSize);

Where Documents is of type EntityCollection<Document>. The operation is taking over 1.6 minutes to complete, at which point it crashes with

Exception of type 'System.OutOfMemoryException' was thrown.

Surely this is just the same as doing SELECT SUM(FileSize) FROM Documents which takes just a few milliseconds to complete when executed directly via SQL Server Management Studio.

For what it's worth, the total size is 816mb, and includes 1,661 documents, so it's not exactly massive.

Have you attached sql profiler and examined the query that's being sent? — Kevin Holditch, Aug 04 '14 at 15:36
Are you maybe doing a ToList() on this.Documents somewhere before? Because if it's already in memory then the Sum will be executed client side... — Andrei Dvoynos, Aug 04 '14 at 15:36
if its only the sum try this.Documents.Select(c => c.FileSize).Sum() instead so you dont fetch too much data — JFM, Aug 04 '14 at 15:37
Or you can try this.Documents.Select(c => c.Sum(i => i.FileSize)) — Marek, Aug 04 '14 at 15:40

ChaosPandion · Answer 1 · 2014-08-05T00:51:34.903

1

It seems clear to me that EntityCollection<Document> is lazily loading every Document in the database. If your Document entity has blob data that would explain everything. Assuming you have an instance of DbContext, I would retrieve an instance of DbSet<Document> so you can write something like this:

var result = (from d in documents
              select new { Size = d.FileSize }).Sum(x => x.Size);

edited Aug 05 '14 at 00:51

answered Aug 04 '14 at 15:42

ChaosPandion

77,506
18
119
157

score 0 · Answer 2 · edited May 23 '17 at 11:49

If you want to execute your Sum() query on the database, make sure your Documents property is of IQueryable<Document> instead of IEnumerable<Document>. The former will translate your LINQ query into SQL (i.e. it will be translated to SELECT SUM(FileSize)) while the latter will be LINQ-to-objects, which will be done in memory.

You can refer to this answer for more information: Returning IEnumerable<T> vs. IQueryable<T>

score 0 · Answer 3 · answered Aug 05 '14 at 08:12

The issue appears to be caused by lazy loading, as mentioned in the previous answers, however none of the suggested methods appear to work. However creating a new DB entity and querying directly appears to work fine:

MyEntities entities = new MyEntities();
decimal size = entities.Documents.Where(x => x.UniverseID == this.ID).Sum(x => x.FileSize);

I'm not sure what the difference is, but it seems to work!

EntityFramework - Sum() Causing OutOfMemoryException

3 Answers3