LINQ to SQL - retrieve object, modify, SubmitChanges() creates new objects

Question

I've been battling this for a while. I'm trying to implement a many to one association. I have a bunch of rows in a table, called readings. These accumulate over time, and every now and then I want to export them. When I export them I want to create a new object called ExportEvent, to track which rows were exported, so they can be re-exported if need be. Therefore Reading has a nullable foreign key relationship with ExportEvent, as I create the readings before I export them.

What I'm finding is that when I then do the export, whether I first create the ExportEvent (evt) and add the readings using

evt.Readings.AddRange(),

or if I use

foreach(reading) 
reading.ExportEvent = evt

When I call SubmitChanges I am always getting a new bunch of readings created with the association to evt, and the original records aren't updated.

I pared this back to its simplest though, just to see if I could create the two objects with no association, and I even found when I just retrieved all the readings and updated an int value on them, submitchanges still inserted a bunch of new records. What's going on?

Answer 1

I suspect it is because you are calling AddRange(). This will add the new objects to the data context. Instead, you should try just re attaching the existing objects by called Attach() on your data context.

(Or if you never detached them and still have your original data context, you don't need to do anything, just make the changes to the objects and call SubmitChanges())

Answer 2

Hmmm. Interesting - just clicked this link in my bookmarks, and found that the question has been resurrected, so will provide the (embarrassing) solution. All of my entities have audit data properties on them - CreatedDate and UpdatedDate. Therefore I've implemented the partial methods for the insert and update of each entity in the datacontext. I had copied and pasted (how often is this the cause of some downfall) some of these insert and update methods for the newly created entities. As a result I'd also copied an error, where the Update[blah] methods were calling ExecuteDynamicInsert, instead of ExecuteDynamicUpdate.

Suffice to say I was very frustrated when for 3 hours I'd been trying frantically to solve this problem, only to find it was due to a (silly) copy/paste error - and only to find the error about 3 mins after I'd posted this question!

Hope this helps someone.