LINQ to SQL update ("cannot add an entity with a key that is already in use. linq to sql" problem)

Question

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:

C#

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

   m_commentsTable.Context.SubmitChanges();
}

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)?

Thanks

Posted 1-Jan-13 10:17am

kalkwarf

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Christian Graus · Answer 1 · 2013-01-01T10:26:00

Solution 1

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 ?

Posted 1-Jan-13 10:26am

Christian Graus

Comments

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.

PTX007 · Answer 2 · 2014-01-05T14:38:00

XML

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";
CommentsRepository.SaveRecord(comment);

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>