Memory consumption of EF

Question

Good morning!

Actually I'm playing around with EF atm a little bit and I need your guys help:
Following scenario: I have a table with a lot of data in it. If I'm querying this table through EF, all the records get load into memory.

eg.

var counter = default(int);
using (var myEntities = new MyEntities())
{
    foreach (var record in myEntities.MySpecialTable)
    {
        counter++;
    }
}

So, I run through all of the records of MySpecialTable and count counter up. When I'm having a look at the Taskmanager, or anything else which shows me the memory-consumption of my app, it tells me: 400Mb. (because of the data)
If I run throught the table another time, memory consumption will get doubled.

I already tried to call the GC, but it won't work.

Why do all of the records of each run get stored somewhere in the memory (and are not released)? Where do they get stored? Is there any way to make EF-queries behave like a DataReader? Or is there any other ORM which is as elegant as EF?

edit:
no, i'm not doing a count like that ... this is just for showing the iteration :)

Answer 1

First of all, I hope you're not actually doing a count like that; the Count method is far more efficient. But presuming this is just demo code to show the memory issue:

Change the MergeOption property of the ObjectQuery to NoTracking. This tells the Entity Framework that you have no intention of modifying the entities, and hence it needn't bother keeping track of their original state.