Alternative to .ToList() to return HUGE quantity of data?

Question

I've got a query that returns more than 212Milion rows. I tried to use .ToList() but it obviously goes in OutOfMemoryException. What is the best alternative for my case?

I tried also to use Skip(fetchedrows).Take(1000).ToList() but performance really slow down on skipping phase and query never end.

Answer 1

If possible, return an enumerable. That way you can keep on scrolling forward (and forward only) through the excessive amounts of rows without the need of placing it in memory at once, which will most likely never work. If implemented correctly (it also depends on your data source), you can read and process one row at a time, with virtually no memory in use.

There are many techniques to do this, but the easiest way I often use is yield return, which will generate its own state machine.

Entity Framework nowadays does stream the results itself. The only thing you should not do is call ToList() or similar methods which would load all rows from the database in memory. Just iterate over the results as if they were regular collection. (For EF6 and older you can use the AsStreaming() extension method.) If that doesn't work for you, you can always revert to a DataReader where you read and return row by row.