title: Read huge table with LINQ to SQL: Running out of memory vs slow paging
body: <p>I have a huge table which I need to read through on a certain order and compute some aggregate statistics. The table already has a clustered index for the correct order so getting the records themselves is pretty fast. I'm trying to use LINQ to SQL to simplify the code that I need to write. The problem is that I don't want to load all the objects into memory, since the DataContext seems to keep them around -- yet trying to page them results in horrible performance problems.</p>
  
  <p>Here's the breakdown. Original attempt was this:</p>
  
  <pre><code>var logs = 
      (from record in dataContext.someTable 
       where [index is appropriate]
       select record);
  
  foreach( linqEntity l in logs )
  {
      // Do stuff with data from l
  }
  </code></pre>
  
  <p>This is pretty fast, and streams at a good rate, but the problem is that the memory use of the application keeps going up never stops. My guess is that the LINQ to SQL entities are being kept around in memory and not being disposed properly. So after reading <a href="https://stackoverflow.com/questions/2727591/out-of-memory-when-creating-a-lot-of-objects-c-sharp">Out of memory when creating a lot of objects C#</a> , I tried the following approach. This seems to be the common <code>Skip</code>/<code>Take</code> paradigm that many people use, with the added feature of saving memory.</p>
  
  <p>Note that <code>_conn</code> is created beforehand, and a temporary data context is created for each query, resulting in the associated entities being garbage collected.</p>
  
  <pre><code>int skipAmount = 0;
  bool finished = false;
  
  while (!finished)
  {
      // Trick to allow for automatic garbage collection while iterating through the DB
      using (var tempDataContext = new MyDataContext(_conn) {CommandTimeout = 600})
      {               
          var query =
              (from record in tempDataContext.someTable
               where [index is appropriate]
               select record);
  
          List&lt;workerLog&gt; logs = query.Skip(skipAmount).Take(BatchSize).ToList();
          if (logs.Count == 0)
          {
              finished = true;
              continue;
          }
  
          foreach( linqEntity l in logs )
          {
              // Do stuff with data from l
          }
  
          skipAmount += logs.Count;
      }
  }
  </code></pre>
  
  <p>Now I have the desired behavior that memory usage doesn't increase at all as I am streaming through the data. Yet, I have a far worse problem: each <code>Skip</code> is causing the data to load more and more slowly as the underlying query seems to actually cause the server to go through all the data for all previous pages. While running the query each page takes longer and longer to load, and I can tell that this is turning into a quadratic operation. This problem has appeared in the following posts:</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/5261717/linq-skip-problem">LINQ Skip() Problem</a></li>
  <li><a href="https://stackoverflow.com/questions/8202907/linq2sql-select-orders-and-skip-take">LINQ2SQL select orders and skip/take</a></li>
  </ul>
  
  <p>I can't seem to find a way to do this with LINQ that allows me to have limited memory use by paging data, and yet still have each page load in constant time. Is there a way to do this properly? <strong>My hunch is that there might be some way to tell the DataContext to explicitly forget about the object in the first approach above, but I can't find out how to do that.</strong></p>