Click here to Skip to main content
15,879,095 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am having great difficulty updating an entity to my database using LINQ To SQL.

I am working on an MVC web site using Windsor Castle for my repositories. I am working with VS 2010 using the "Pro ASP.NET MVC Framework" book by Sanderson as a guide.

My problem is with the following code in my repository:

public void Save(Comment comment)
   if (comment.CommentID == 0)
   else if (m_commentsTable.GetOriginalEntityState(comment) == null)
      m_commentsTable.Context.Refresh(RefreshMode.KeepCurrentValues, comment);


The code is a direct copy of the book examples (which work correctly) but modified for my entity (Comment). m_commentsTable is of type Table<Comment>

This method is called for saving new Comment objects as well as updating existing ones. Saving new objects works fine, but updating existing objects fails when calling Attach with the following error:

"cannot add an entity with a key that is already in use."

I Googled the issue and found a lot of people with a similar problem. I tried many solutions, but none work.

I tried:

- get the original object from the table and pass both old and new objects to Attach : e.g. Attach(comment, originalComment). I get the same error.

- pass 'true' as second arg to Attach. I get the following error: "An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy."

- pass 'true' as second arg to Attach after changing the UpdateCheck attribute on Comment's columns to Never. I get the original error.

Some forum posts suggested that I create another DataContext and attach my new object to it. This seems wasteful. Also, this would also entail saving my connection string (constructor arg). All that seems a bit messy and unnecessary.

Another suggestion was to add a version column to my table. This also seems wasteful and awkward. It makes me uncomfortable adding a column to a table to work around a data access problem.

Is there something I am missing? Does anyone have another suggestion that I haven't tried?

I am still mystified as to why the book examples work fine, but my 'copy' does not. I have a feeling I am missing something subtle, but have no idea what it is.

Should I give up on LINQ To SQL and try another approach? If so, what might that be?

Should I get over my discomfort and implement one of other suggestions (another DataContext or extra label column)?


If you're using EF, all you need to do to edit an existing object is get it, change the properties, and call SaveChanges on your DB object. Have you tried just removing that other code and doing a save with the changed object ?
Share this answer
kalkwarf 1-Jan-13 18:13pm    
No, not using Entity Framework. I am just creating my entity classes and database by hand, then mostly using LINQ.

Perhaps, I should begin using EF. I have already defined my database, so that step is complete. I wonder how much of my current code would have to be re-worked... I'll start looking at retrofitting my code for EF.
Christian Graus 1-Jan-13 18:34pm    
If you're using MVC and LINQ, you may as well use EF.
I'm a little late responding to this question, but I just ran into this problem myself and seem to have found the solution.  I'll put this in terms of your code so it makes sense.

What I was doing was creating a new Comment object, filling in the fields manually to recreate an existing record, and then submitting it to be saved.  That was giving me the error.

I changed that to load the existing Comment, edit the field I needed to change, and then submitted that to be saved instead.  Worked perfectly.

So based on the book you're using, the code would be something like:

Comment comment = CommentsRepository.Where(x => x.CommentID = thisID).First();
comment.FieldToChange = "New Data";

GetOriginalEntityState assumed the copy was a new record - I guess it didn't compare it based on the identity field, which I thought it would.  So then it conflicted because it thought there were 2 records with the same ID.

Hope this helps someone.<b></b>
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900