ADO.NET Entities throws OutOfMemoryException. How to prevent that

Question

The underlying table has a int, guid and Filestream column in SQL Server 2008 R2. The filestream shows up as byte[]. What I observe is that the memory consumption keeps on increasing. What should I do?

MyEntities bh = new MyEntities ();
foreach (var s in bh.TaskGraphs)
{
    try
    {
        using (var x = new MemoryStream(s.TaskGraph1))
        {
            //var t = TaskGraph.Load(x);
            //Validate(t);
        }
    }
    catch (Exception e)
    {                
    }
}

Here's the memory usage pattern

enter image description here

I now observed that doing bh.TaskGraphs.Select(p => new { p.TaskGraph1, p.StreamId } makes the exception go away. Is it due to the child table linked to this table?

BTW, each BLOB is ~ 3MB

even if i comment out the function calls within using the error still happens — R.D, Feb 28 '11 at 17:59
Are you using entity framework? Please tell us a bit about the amount of data involved. — Daniel Hilgarth, Feb 28 '11 at 18:05
@daniel-hilgarth yes this is with entity framework. Details added to question — R.D, Feb 28 '11 at 19:04

score 1 · Accepted Answer · edited Mar 01 '11 at 09:23

Entity framework loads entity in an entity container and container has an entity set which contains local copy of entity which was fetched from database. Every entity has a reference of it's corresponding entity set and entire entity set will stay in memory and entity set will be keep on growing as you will load it from database.

You must divide your table into two parts using one to one relationship with table containing primary key and other attributes and another table containing just primary key and blob.

Next in your for loop while you are iterating, you should load your blob entity via a new context and process your byte array and destroy your context at the end of for loop. Or you can also detach an object from entity container that will make it free to e garbage collection.

In your existing code you can detach your entity that will free the memory it is holding.

MyEntities bh = new MyEntities ();
foreach (var s in bh.TaskGraphs)
{
    try
    {
        using (var x = new MemoryStream(s.TaskGraph1))
        {
            //var t = TaskGraph.Load(x);
            //Validate(t);
            bn.Detach(t);
        }
    }
    catch (Exception e)
    {                
    }
}

seems to be working. Thanks. Also tried the equivalent bh.TaskGraphs.MergeOption = System.Data.Objects.MergeOption.NoTracking; — R.D, Feb 28 '11 at 20:16

score 0 · Answer 2 · answered Feb 28 '11 at 17:57

It's hard to say without more info, but I suppose, the following is at least part of the problem:

The table TaskGraphs has a lot of rows or the Filestream column contains a lot of date
TaskGraph.Load and/or Validate keep a reference to that data, so it will not be garbage collected.
The object context MyEntities keeps the Filestream columns in the memory for a certain period of time, before releasing it again.

AlaaL · Answer 3 · 2011-02-28T18:16:37.727

0

What i think is TaskGraph table has a lot of records, so when you call bh.TaskGraphs it loads the entire table into memory and the binary data in it bigger than memory available. You should try to load only records you want from database by using stored procedure so another way. but first try this:

using(MyEntities bh = new MyEntities())
{
    your code.....
}

edited Feb 28 '11 at 18:16

answered Feb 28 '11 at 18:10

AlaaL

333
9
28

score 0 · Answer 4 · edited May 23 '17 at 12:13

0

I don't think there is an easy solution for this. See this related question: Handling BLOBs in Entity Framework 4.0 in a stream-fashion

edited May 23 '17 at 12:13

Community

1
1

answered Feb 28 '11 at 18:41

Simon Mourier

132,049
21
248
298

ADO.NET Entities throws OutOfMemoryException. How to prevent that

4 Answers4