Why is LINQ to SQL entity association creating a new (duplicate) row when inserting a new record?

Question

I am trying to insert a new entity using LINQ-to-SQL, and entity is associated with a User entity. The insert of the new entity is successful, but my existing User entity gets inserted as if it were a new User. The code looks something like the following:

var someEntity = new Entity();
someEntity.User = this.User;
dataContextInstance.SomeEntities.InsertOnSubmit(someEntity);
dataContextInstance.SubmitChanges();

Does anyone know why the user is being inserted as a brand new entity into the Users table? It would seem that the User.UserId would become the foreign key value in the UserId column of the row mapped to the someEntity that is being inserted.

Thanks for any help/suggestions/comments

Answer 1

Sorry, I don't get it? Do you want to update your existing user? What is someEntity for?

It makes sense, that LINQ tries to Insert a New user, because you tell it to do so. If you just want to change one of your users you need to select him out of SomeEntities, do the update and then call SubmitChanges (LINQ will recognize, that the original entity has been modified) - without .InsertOnSubmit.

Answer 2

Make sure you're using the same instance of DataContext for both tables. See my question here for (I think) a clearer explanation of the problem.

Answer 3

Since the User entity has been previously loaded by another DataContext (which should hopefully be disposed by now!), you have to attach it to the new (current) DataContext otherwise the DataContext will view it as a new Entity and not an existing one (which already exists in the DB).

Answer 4

My Old Frustration is Not an Answer, I Apologize:

I had marked below as an answer, its unmarked now.

"I've decided to answer my own question since it is apparent that I have more of a gripe with LINQ-to-SQL as an incomplete, unintuitive ORM at this stage in its development. Don't get me wrong: it's a great technology, but I was hoping to use it as fully functional ORM without having to write additional plumbing or decorate the existing functionality.

Bottom line: LINQ-to-SQL as an ORM or over WCF is (currently) a "World of Pain". Consider NHibernate, Subsonic or some other ORM if you need many-to-many functionality or think you might want something more than a bunch of dumb DTOs.h And yes, I've seen the posts and examples how to do many to many in LINQ, but ORMs should be intuitive. LINQ-to-SQL has a ways to go in that regard.

Thank you everyone for your comments and suggestions."