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

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. :)
  开发者_如何学JAVA   }
}

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?

---

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.

---

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

---

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?

---

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. :)
     }
}

---

I would suggest using SQL instead to modify this data.