Iterating through IQueryable with foreach results in an out of memory exception.

Question

I'm iterating through a smallish (~10GB) table with a foreach / IQueryable and LINQ-to-SQL. Looks something like this:

using (var conn = new DbEntities() { CommandTimeout = 600*100})
{
     var dtable = conn.DailyResults.Where(dr => dr.DailyTransactionTypeID == 1);
     foreach (var dailyResult in dtable)
     {
        //Math here, results stored in-memory, but this table is very small. 
        //At the very least compared to stuff I already have in memory. :)
     }
}

The Visual Studio debugger throws an out-of memory exception after a short while at the base of the foreach loop. I'm assuming that the rows of dtable are not being flushed. What to do?

Thanks in advance.

Answer 1

You call ~10GB smallish? you have a nice sense of humor!

You might consider loading rows in chunks, aka pagination.

conn.DailyResults.Where(dr => dr.DailyTransactionTypeID == 1).Skip(x).Take(y);

Answer 2

The IQueryable<DailyResult> dtable will attempt to load the entire query result into memory when enumerated... before any iterations of the foreach loop. It does not load one row during the iteration of the foreach loop. If you want that behavior, use DataReader.