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 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

Answer 2

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 3

I'm having a similar problem. Here is my code:

    Public Sub SaveCapacities(ByRef colCapacities As Collection)

    Try
        ' Declare local variables / constants
        ' ---------------------------------------------------------------------
        Me.m_dscDatabaseContext.MergeOption = System.Data.Services.Client.MergeOption.OverwriteChanges

        ' Create entity objects for each of the changed capacities 
        ' ---------------------------------------------------------------------
        For Each clsCapacity As DataModel.Capacity In colCapacities

            Dim entCapacity = (From capacity As dsrRemoteDatabase.tCAPSCapacity _
                               In m_dscDatabaseContext.tCAPSCapacity _
                               Where capacity.RESOURCE_ID = clsCapacity.Resource _
                               And capacity.FACILITY = clsCapacity.Facility _
                               And capacity.WORK_WEEK = clsCapacity.WorkWeek _
                               Select capacity).Single

            entCapacity.EQUIPMENT_HOURS = clsCapacity.EquipmentCapacity
            entCapacity.AVAILABLE_EMPS = clsCapacity.AvailablePeople
            entCapacity.HOURS_PER_EMP = clsCapacity.HoursPerWeek

            ' Mark the entity object as updated.
            m_dscDatabaseContext.UpdateObject(entCapacity)


            ' Call SaveChanges to write the changes to the database
            m_dscDatabaseContext.SaveChanges()

        Next

    Catch ex As Exception
        ' Display the exception using the standard error handler
        ' -----------------------------------------------------------------
        Dim clsErrorMessageBox As ErrorMessageBox = New ErrorMessageBox(ex)
        clsErrorMessageBox.Show()

    End Try

End Sub

Every time I run this, the SaveChanges() call generates a 404 error. It has to be something incredibly simple that I'm missing, but I can't seem to find it. Any ideas?