Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL LINQ MVC
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)
         {
            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
Rate this: bad
good
Please Sign up or sign in to vote.

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 ?
  Permalink  
Comments
kalkwarf at 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 at 1-Jan-13 18:34pm
   
If you're using MVC and LINQ, you may as well use EF.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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>
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 6,745
1 OriginalGriff 6,696
2 CPallini 5,315
3 George Jonsson 3,599
4 Gihan Liyanage 2,650


Advertise | Privacy | Mobile
Web04 | 2.8.140922.1 | Last Updated 5 Jan 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100