Linq: Delete and Insert same Primary Key values within TransactionScope

Question

I want to replace existing records in the DB with new records in one transaction. Using TransactionScope, I have

using ( var scope = new TransactionScope())
{
     db.Tasks.DeleteAllOnSubmit(oldTasks);
     db.Tasks.SubmitChanges();

     db.Tasks.InsertAllOnSubmit(newTasks);
     db.Tasks.SubmitChanges();

     scope.Complete();
}

My program threw

System.InvalidOperationException: Cannot add an entity that already exists.

After some trial and error, I found the culprit lies in the the fact that there isn't any other execution instructions between the delete and the insert. If I insert other code between the first SubmitChanges() and InsertAllOnSubmit(), everything works fine. Can anyone explain why is this happening? It is very concerning.

I tried another one to update the objects:

IEnumerable<Task> tasks = ( ... some long query that involves multi tables )
.AsEnumerable()
.Select( i => 
{
    i.Task.Duration += i.LastLegDuration;
    return i.Task;
}
db.SubmitChanges();

This didn't work neither. db didn't pick up any changes to Tasks.

EDIT:

This behavior doesn't seem to have anything to do with Transactions. At the end, I adopted the grossly inefficient Update:

newTasks.ForEach( t =>
{
     Task attached = db.Tasks.Single( i => ... use primary id to look up ... );
     attached.Duration = ...;
     ... more updates, Property by Property ...
}
db.SubmitChanges();

Answer 1

Instead of inserting and deleting or making multiple queries, you can try to update multiple rows in one pass by selecting a list of Id's to update and checking if the list contains each item.

Also, make sure you mark your transaction as complete to indicate to transaction manager that the state across all resources is consistent, and the transaction can be committed.

Dictionary<int,int> taskIdsWithDuration = getIdsOfTasksToUpdate(); //fetch a dictionary keyed on id's from your long query and values storing the corresponding *LastLegDuration*
using (var scope = new TransactionScope(TransactionScopeOption.Required))
{
    var tasksToUpdate = db.Tasks.Where(x => taskIdsWithDuration.Keys.Contains(x.id));
    foreach (var task in tasksToUpdate)
    {
        task.duration1 += taskIdsWithDuration[task.id];
    }        

    db.SaveChanges();
    scope.Complete();
}         

Depending on your scenario, you can invert the search in the case that your table is extremely large and the number of items to update is reasonably small, to leverage indexing. Your existing update query should work fine if this is the case, so I doubt you'll need to invert it.