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();

TransactionScope is a wrapper for BEGIN TRANSACTION END TRANSACTION, so while submitting changes, changes is not really applied until you close the newly created transaction, and, consequently, will not really delete task items from tasks, but some of the tasks already exists in both old and new task lists, so you are trying to add the entity twice which still exists in a list — Alan Turing, Sep 15 '11 at 13:13
You are deleting a record and then inserting a new record with the same key? What's wrong with just updating it? — Christophe Geers, Sep 15 '11 at 13:25
You can not insert with the same key unless you do Identity Insert, never used that in EF, but you can do it in sql. It seems you should only update if you need to insert with the same key. — AD.Net, Sep 15 '11 at 13:42
I need to update many roles each with a different value. Would you recommend an efficient to update all the information in oldTasks with the information in newTasks? — Candy Chiu, Sep 15 '11 at 14:03
@CandyChiu: Don't make DataContext a singleton! You will leak memory like crazy (it caches everything forever). DataContext is an implimination of the unit of work pattern which means you should create a datacontext, do a small amount of work, then dispose of it. — Allon Guralnek, Oct 07 '11 at 21:46

arviman · Answer 1 · 2011-09-28T00:06:08.330

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.

edited Sep 28 '11 at 00:06

answered Sep 26 '11 at 22:54

arviman

5,087
41
48

The quote contains a typo. Task doesn't contain LastLegDuration, it is a computed value. – Candy Chiu Sep 27 '11 at 12:16
Oops! I've modified it now assuming that `getIdsOfTasksToUpdate` would return a Dictionary with `id` of task as key, and `LastLegDuration` as value. It should be a simple change to your existing query to do this. – arviman Sep 27 '11 at 16:38
your approach looks very similar to the row by row update approach. I think the bulk of the time is spent on the update, not the get. – Candy Chiu Sep 27 '11 at 18:39
It is similar, but your query will run much faster that what you've currently got. In your case you're making a database call *each* time you update a row. This will only make one call. – arviman Sep 27 '11 at 20:39
Even when SubmitChange is outside the loop? What makes my code calling db multiple times? – Candy Chiu Sep 27 '11 at 21:20
Sorry for the miscommunication, i meant to say that when you do a `where` `contains`, the SQL that is generated is of the form SELECT * FROM [Task] Where[Task].[Id] IN (*id1*,*id2*) instead of multiple select clauses of the form: SELECT * from [TASK] Where [Task].[Id] = *id1*;SELECT * from [TASK] Where [Task].[Id] = *id2* which is what happens when you use a `foreach` and `single`. I hope this clarifies it a bit. I was not referring to multiple *network calls*. – arviman Sep 27 '11 at 21:51

score 0 · Answer 2 · answered Oct 21 '11 at 00:08

0

I had same problem in LinqToSql and I don't think its to do with the transaction, but with how the session/context is coalescing changes. I say this because I fixed the problem by bypassing linqtosql for the delete and using some raw sql to do it. Ugly I know, but it worked, and all inside a transaction scope.

answered Oct 21 '11 at 00:08

DanH

3,772
2
27
31

Not possible unfortunately - don't have access now. However it's not hard to describe, I simply looked up how to execute sql commands directly in LinqtoSql and used those for the deletes inside the trasaction scope (these hit server direct without submit changes obviously). Pretty sure that is how I fixed it, but it was a year ago... It's pretty annoying how hard upsert is, as I was writing something to cache data from a web service and didn't want to manually implement the merge logic for each item, easier to delete/replace -especially as these were multi-table objects. – DanH Oct 25 '11 at 14:59
Did you do both delete and insert using SQL? – Candy Chiu Oct 31 '11 at 14:13
no you only need to do the delete. We are just tricking the state machine so that when it merges it back to the db it doesn't think you are trying to delete and insert on same key. – DanH Oct 31 '11 at 14:18

Linq: Delete and Insert same Primary Key values within TransactionScope

2 Answers2