ADO.NET Entities throws OutOfMemoryException. How to prevent that

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

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

---

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)
    {                
    }
}

---

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

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

---

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.....
}

---

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