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?

Possible duplicate of Entity framework large data set, out of memory exception — Michael Freidgeim
– Michael Freidgeim, Commented Jun 3, 2016 at 1:32

BrokenGlass · Accepted Answer · 2011-07-26 20:56:45Z

4

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.

edited Jul 26, 2011 at 20:56

answered Jul 26, 2011 at 20:49

BrokenGlass

161k28 gold badges297 silver badges339 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mdeangelo272 Over a year ago

Setting the MergeOption to NoTracking fixed the issue. Thank you.

jairhumberto Over a year ago

where do I set this configuration? In which class?

roken · Accepted Answer · 2011-07-26 20:39:47Z

0

As for altering your query you can add a where clause to pare down records coming back:

http://msdn.microsoft.com/en-us/library/bb311043.aspx

answered Jul 26, 2011 at 20:39

roken

4,0242 gold badges23 silver badges32 bronze badges

Collectives™ on Stack Overflow

Entity Framework query outofmemoryexception

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related