If you use an implicit insert on a new object and then decide that you don’t actually want it, you have a bit of a problem. First of all, you need to remove the relationships that you created so that it doesn’t show up in any places that it shouldn’t. The problem with this is that Linq will still kind of (see below) have it marked for insertion, so if you call SubmitChanges without doing anything else, it will either still insert it, or if you’ve removed a relationship which relies on a non-nullable foreign key, you’ll get an error like
“An attempt was made to remove a relationship between a A and a B. However, one of the relationship’s foreign keys (A.BID) cannot be set to null.”
Where A and B are the 2 appropriate Linq to SQL classes.
The next thing we need to do then is to make sure that Linq to SQL removes the item from the insert list. The only way that I have found to do this is to call DeleteOnSubmit on the appropriate table. The problem is that if we do that at this point, we get an InvalidOperationException with message “Cannot remove an entity that has not been attached.”
Apparently, Linq to SQL knows it is supposed to insert it, but it isn’t properly attached. To get around this, we need to call InsertOnSubmit on the appropriate table before calling DeleteOnSubmit.
That’s all fine and dandy and works, as long as you are trying to delete something that hasn’t been inserted yet. Normally, however, I use the same code whether it has been inserted or not, and of course if you call InsertOnSubmit on an entity that has already been inserted, you get an InvalidOperationException saying “Cannot add an entity that already exists.”. Therefore, before you call InsertOnSubmit, you need to check if it actually has already been inserted. Since I always have identity fields with the name ID on all my tables and Linq to SQL updates these when it does an insert, I normally just check that the ID is 0 before inserting.
The resulting steps that you need to solve the problem are therefore: -
- Remove the relationships from item
- If the ID=0 then DataContext.Table.InsertOnSubmit(item)
- DataContext.Table.DeleteOnSubmit(item)
You shouldn’t actually have to call DataContext.SubmitChanges at this point – the whole point in the exercise is to mean it will be safe when you do later.
Alternatively, you can just throw away the datacontext without calling SubmitChanges, but I find that is rarely an option because there may be other data that needs submitting.
By the way, you need to do the first step of removing the relationships in order to make sure that none of your code accidentally tries to access the removed object as it will still be in memory as a related object until SubmitChanges is called otherwise and you could end up processing it when accessing entities related to an object that it is related to.
- Linq to SQL Quirks Part 1: Implicit Inserts
- Linq to SQL Quirks Part 2: DeleteOnSubmit and Entity not Attached
- Linq to SQL Quirks Part 3 - Extension to Get Around the Delete Problem
- Linq to SQL Quirks Part 4: Circular References
No related posts.
Tags: .NET, .NET 3.5, Delete, DeleteOnSubmit, Linq, Linq to SQL
[...] previous post about problems deleting can be partly handled with an extension method to the Linq to SQL Table(Of [...]
Very well explained, thank you very much for posting this. It helped me to solve my problem.
I just have a suggestion/alternative way about step 2, for checking if entity already exists.
If you don’t have identity field to check for ID, then in general always could do:
Instead of: ‘If ID=0′
Could do: ‘if (DataContext.Table.GetOriginalEntityState(item) == null)’
Great post, really helped me out!
Is this a better way of checking? I’m guessing if the ID field is automatically generated by the DB then checking for a 0 is more efficient but I’m not sure if it’s too safe.
Thanks, this saved me some (more) misery.
Many many thanks, You are very great developer, You really helped me, All the Best