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

Question

I have a query that returns more than 212 rows. I tried using .ToList() , but it obviously goes to OutOfMemoryException . What is the best alternative for my case?

I also tried using Skip(fetchedrows).Take(1000).ToList() , but performance really slows down in the skip phase and the request never ends.

Answer 1

If possible, return the enumerated. Thus, you can continue to scroll forward (and only forward) through an excessive number of lines without having to immediately put it into memory, which most likely will never work. If it runs correctly (it also depends on your data source), you can read and process one row at a time, with little or no memory usage.

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

Entity Framework is currently truly conveying results. The only thing you do not need to do is call ToList() or similar methods that will load all the rows from the database in memory. Just iterate over the results as if they were a regular collection. (For EF6 and older, you can use the AsStreaming() extension method.) If this does not work for you, you can always go back to a DataReader , where you read and return row by row.

Answer 2

From your question, it's not clear how you actually request the data. One way to reduce memory usage is to use the AsNoTracking method .

This is for objects that should not be modified and recorded.

This article shows memory gains and other benefits of using this extension method.