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?
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.
As for altering your query you can add a where clause to pare down records coming back:
