OutOfMemoryException in WCF Data Service

I am trying to expose some entities via WCF Data Service. My tables contain huge amount of data and queries can easily return 50,000 records. I've an excel add-in which connects to this service and helps users to import data from service into their excel sheets.

My client code is something like this:

开发者_如何学JAVA

UTF8Encoding utf8Encoding = new UTF8Encoding();
Entities plDataEntities = new Entities(new Uri(@"http://localhost/AdoNetDataService.svc/"));

int recordCount = 0;
var apples = (from a mada in plDataEntities.Apples select a).Take(50 * 1000);
using (var fileStream = new FileStream(@"C:\Apples.txt", FileMode.Create))
{
   foreach (Apple apple in apples)
   {
      var description = getAppleDescription(apples);

      byte[] bytes = utf8Encoding.GetBytes(description);

      fileStream.Write(bytes, 0, bytes.Length);
      fileStream.Flush();
      recordCount++;
   }
}

I was thinking that service will serve this data as it reads it from Db but looks like some buffering going on and I am getting a memory exception.

I don't have any specific configuration for memory management and I could not find anything helpful about memory optimization on ado.net data services. Any help on how to optimize memory will be appreciated. Also I am open to suggestion to follow a different path so feel free to make suggestions.

---

You can do client side or server side paging to work around this issue:

1> You can do client side paging - Instead of Take(50 * 1000), you can change that to Take(1000) or something and write a loop until all the entities are loaded. That way you are getting smaller chunks, but the flipside is that you are making more round-trips to the server.

2> You can do server side paging - in this case, the server will give you a set of entities and the next link uri to fetch the next set of entities. Keep querying until there is no next link specified and at that point, you have downloaded all the entities in the client.

Hope this helps. Thanks Pratik