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

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);

Use .AsNoTracking() - it tells DbEntities not to cache retrieved rows

using (var conn = new DbEntities() { CommandTimeout = 600*100})
{
     var dtable = conn.DailyResults
                .AsNoTracking()      // <<<<<<<<<<<<<<
                .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. :)
     }
}

Using DataReader is a step backward unless there is a way to use it within LINQ. I thought we were trying to get away from ADO.

The solution suggested above works, but it's truly ugly. Here is my code:

int iTake = 40000;
int iSkip = 0;
int iLoop;
ent.CommandTimeout = 6000;
while (true)
{
  iLoop = 0;
  IQueryable<viewClaimsBInfo> iInfo = (from q in ent.viewClaimsBInfo
                                       where q.WorkDate >= dtStart &&
                                         q.WorkDate <= dtEnd
                                       orderby q.WorkDate
                                       select q)
                                      .Skip(iSkip).Take(iTake);
  foreach (viewClaimsBInfo qInfo in iInfo)
  {
    iLoop++;
    if (lstClerk.Contains(qInfo.Clerk.Substring(0, 3)))
    {
          /// Various processing....
    }
  }
  if (iLoop < iTake)
    break;
  iSkip += iTake;
}

You can see that I have to check for having run out of records because the foreach loop will end at 40,000 records. Not good.

Updated 6/10/2011: Even this does not work. At 2,000,000 records or so, I get an out-of-memory exception. It is also excruciatingly slow. When I modified it to use OleDB, it ran in about 15 seconds (as opposed to 10+ minutes) and didn't run out of memory. Does anyone have a LINQ solution that works and runs quickly?

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.