Error with creating record in EF 6 - "Cannot insert explicit value for identity column in table "Photos" when IDENTITY_INSERT is set to OFF"

Question

I recently upgraded my application from ASP.NET MVC 3 and EF 4 to ASP.NET MVC 5 and EF 6. I have several repository functions that I'm using for CRUD functionality.

I haven't changed anything, but I'm suddenly receiving this error if I try to add a record to my entities:

Cannot insert explicit value for identity column in table "Photos" when IDENTITY_INSERT is set to OFF

Here's an example of one of these methods

 public void SavePhoto(Photo photo)
 {
        if (photo.PhotoID == 0)             // new photo
            _entities.Photos.Add(photo);
        else                                // edit photo
        {
            _entities.Photos.Attach(photo);
            _entities.Entry(photo).State = EntityState.Modified;               
        }

        _entities.SaveChanges();
  }

The PhotoID column in my database for this table is set to be the Identity. When this worked before, it would just increment the PhotoID column value based on the last entry (as expected).

Is there a better way to do this now with EF 6?

It doesn't seem to be wrong. I've looked at my Add, Update and AddOrUpdate methods on my repository class and they basically do the same thing. The only difference is I don't check the ID = 0 and instead am using something like if(_entitites.Photos.Contains(photo)) Update(photo); — kjbartel, Jul 19 '14 at 04:17
Have you tried any of these: http://stackoverflow.com/a/11174400/1730559 — kjbartel, Jul 19 '14 at 04:19
Review other navigational models of the Photo entity. I had a problem once with an entity, it turns out that its navigational properties had the problem. — Yorro, Jul 19 '14 at 04:40

score 2 · Accepted Answer · answered Jul 19 '14 at 05:03

Is your PhotoId column is "int" type and annotated with "DateGenerated identity"? If so, the following code will error. Because EF might thinking you are inserting 0 into identity column.

if (photo.PhotoID == 0)             // new photo
            _entities.Photos.Add(photo);

Use id == 0 to check whether it is a new photo or not is not a good practice, actually it is a "bug", because say you have 3 records in the system(which default could mean your photoid is not greater than 4), And somehow you PhotoID was manipulated as 100 in the backend, now when your code run, your code will set its state as Modified. And EF might throw error for you, or EF might try to insert it for you instead of editing.

So I would suggest to use follow code

  var photo = _entities.Photos.Find(photo.PhotoId)
if (photo == null) {
//your code to add photo
}
else
{
//your code to set the the modal state to modified. 
}

Error with creating record in EF 6 - "Cannot insert explicit value for identity column in table "Photos" when IDENTITY_INSERT is set to OFF"

1 Answers1