LINQ to SQL with too many records for memory

Question

A lot of the LINQ to SQL that I've been doing involves reading data from a table and then calling the ToList() extension method and using the data in memory. However, I now want to use LINQ to SQL to process more records that can fit in memory. This is the pattern that I've come up with so far:

int skip = 0;
IList<Record> records = new List<Record>();
do
{
    records = DBRecords.Skip(skip).Take(1000).Select(a => new Record
    {
         // Set values here...
    }).ToList();
    foreach (Record r in records)
    {
        yield return r;
    }
    skip += 1000;
} while (records.Count > 0);

This allows me to pull 1000 records at a time and return them in batches to the app. However, I know that there must be a better way of doing this?

Answer 1

Why can't you just handle the records one at a time in a streaming manner?

foreach (Record record in query)
{
    // Do stuff...
}

Also, what sort of calculations are you doing? Have you considered if it is possible to get the database to do the calculation for you, and just tell you the result? This might not be possible in your situation, but if it is, then it would be much faster.

Answer 2

Why do you need to convert to a List at all? The whole point of LINQ is to let it return data to you as you enumerate over the returned IEnumerable. It should internally take care of reading from the database a few records at a time.

Answer 3

linq is lazy evaluated, however ToList() will realize the whole result. You can probably just drop the ToList() and return the result of your query directly. Processing the result in the caller in a foreach block will do so lazily.

Answer 4

Seeing as you're trying to just return the IEnumerable, you can just return DBRecords - It will be evaluated lazily anyhow. One thing to be wary of if you do this though, is that your database access won't begin until you actually start evaluating the IEnumerable. Depending on your circumstances, this might make exception handling a little messier.