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, State and County, where 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?

Thanks!

From stackoverflow

• Something isn't right here. I'm assuming State -> Count is a one to many relationship. In which case, the correct way of doing this is:

  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";
  
  s.Counties.Add(c);
  db.SubmitChanges();
  

  Since State is the parent table, you need to add the counties to the state's counties collection.

  Shaul : OK, that's all well for counties; it may make sense for State to have a Counties collection. But let's say we've got a Person object, with an address including a State. Are you going to give your State a People collection? I don't want to pollute my State object's interface with collections of every Tom, Dick and Harry table that decides they want a FK to State...

• is the State.GetState(...) function using the same datacontext as MyDataContext.GetTable() ?

  Shaul : Yes............

  Shaul : Not quite the full solution - see my answer below: http://stackoverflow.com/questions/795196/linq-problem-with-inserting-new-rows-that-have-references-to-existing-records/795430#795430

  John Boker : that's what i meant :)

• Solved!

  John Boker asked:

  is the State.GetState(...) function using the same datacontext as MyDataContext.GetTable() ?

  My answer was "Yes" - they are using the same DataContext class... but they were using different instances.

  Lesson learned: always use the same instance of your DataContext class for any objects you're planning to persist to your DB!

  (John gets credit for the answer, anyway...)

• If the State didn't use the same DataContext instance it might work to call the Attach method first.