What is the best way to use the .SaveChanges() method in ADO.Net Data Services?

Question

Does anyone have some good information on the usage of the .SaveChanges() method?

I am experiencing a variety of issues when attempting to use the .SaveChanges() method on my data context object. I am taking data from an existing data source, creating the appropriate EntityFramework/DataService objects, populating those created objects with data, adding those objects to the context and then saving that data by calling .SaveChanges.

The scenarios I've come up with (and the problems associated with them) are as such ... In each scenario I have a foreach loop that is taking data from rows in a DataTable and generating the objects, attaching them to the context as they go. (note: three objects a "member" and two "addresses" that are attached via a SetLink call) - basically this is a conversion tool to take data from one data store and massage it into a data store that is exposed by Data Services.

• Call .SaveChanges() without any parameters once at the end of the foreach loop (i.e. outside the loop)
  • OutOfMemory error about 1/3 of the way (30,000 out of 90,000 saves) - not sure how that is happening though as each save item is a seperate SQL call to the database, what is there to run out of memory on?
• Call .SaveChanges() without any parameters once per loop
  • This works, but takes absolutly forever (8 hours for 90,000 saves)
• Call .SaveChanges(SaveChangesOption.Batch) once at the end of the foreach loop
  • Same OutOfMemory error, but without any saves to the database
• Call .SaveChanges(SaveChangesOption.Batch) once per loop
  • 404 not found error
• Call .SaveChanges(SaveChangesOption.Batch) once per 10 loops
  • 400 Bad Request error (occassionally)
  • OutOfMemory after a number of itterations
• A number of random attempts to create the context once per loop, or have it as a variable at the start of the loop or have it as a private member variable that is available.
  • Differing results, unable to quantify, none really that good

What is the prefered method of calling .SaveChanges() from a client object when doing a large data load like this? Is there something I'm not getting about how .SaveChanges() works? Can anyone provide more details on how once should be utilizing this function and what (if any) are the limitations to saving data via Data Services? Are there any best practices around the .SaveChanges() method call? Is there any particularly good documentation on the .SaveChanges() method call?

Answer 1

I have no big experience in using EntityFramework (just some random experiment), have you tried calling .SaveChanges() every n iterations?

I mean something like this:

int i = 0;
foreach (var item in collection)
{
    // do something with your data
    if ((i++ % 10) == 0)
        context.SaveChanges();
}
context.SaveChanges();

I know it's ugly, but it's the first possible solution i came up with.

Answer 2

I am using EntityFramework on a small project also so I am very interested in the question also. Two quick questions: Have you tried to turn of the caching og the data objects in the datacontext? Have you tried to close the datacontext and created a new one during the loop to free up memory?

Regards

Kenneth