Linq problem with inserting new rows that have references to existing records
(I believe this is the same problem as this one, but there's no answer there, and I think I can express the problem better here...)
I have two Linq-to-SQL classes,
County has a FK to
State. Here's some test code:
State s = State.GetState("NY"); // here I do a load of a State class via the Linq DataContext County c = new County(); c.Name = "Rockland"; c.State = s; MyDataContext.GetTable<County>().InsertOnSubmit(c); MyDataContext.SubmitChanges(); // throws an exception
The exception thrown is
"Violation of PRIMARY KEY constraint 'PK_State'. Cannot insert duplicate key in object 'dbo.State'".
In other words, what appears to be happening here is that despite my having loaded
s as an existing record, when I attempt to insert
c, Linq is assuming that all related objects,
State included, also need to be inserted!
This is completely absurd, and I cannot believe that Microsoft would have made such a huge blunder - so it must be that somewhere my own understanding is faulty.
Can anyone please explain what I am doing wrong, and what the correct approach here is?