Entity Framework query outofmemoryexception

Question

I am new to the .NET Entity Framework and am trying iterate over the records in a table with several million rows. Here is the basic code:

// select the records from the database
var records = from data in dataContext.Messages
                select data;

// iterate over the messages
foreach (var record in records)
{
    // do nothing
}

While iterating over the data I get an 'outofmemoryexception'. Is there some way that I can alter my query or manage the memory of the ObjectQuery instance?

Answer 1

I suspect the problem is that Entity Framework is trying to cache / track all this data in your object context, which eventually causes the OutOfMemory Exception if the data set is huge.

You can turn tracking off manually to avoid this:

dataContext.Messages.MergeOption = System.Data.Objects.MergeOption.NoTracking;

The memory allocated that you are currently seeing is within the data context - this memory will eventually get garbage collected once you dispose the context, so alternatively you could materialize smaller batches of rows inside a using block or manually dispose the object context to reclaim the memory between each batch.