Cannot update identity column 'Index'. EF Core

Question

Like the title says, I'm getting a error message while I try to update a entity in EF Core.

Here's the entity that I'm trying to update. Pay attention to the Index property tha's decorated with the Databasegenerated attribute.

I tried to decorate wthe property so a value would only be generated when a new entity is added, but without success.

Regarding to this link: http://ef.readthedocs.io/en/latest/modeling/generated-properties.html#value-generated-on-add

And finally the update method looks like this:

What am I doing wrong here?

The error in the title, **An exception occurred in the database while saving changes. Microsoft.Data.Entity.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Cannot update identity column 'Index'.** — dont_trust_me, May 27 '16 at 08:13
No, I'm just trying to update the entity like in the last image. I want the identity column(Index) to stay the same. — dont_trust_me, May 27 '16 at 08:43

smoksnes · Answer 1 · 2020-08-22T14:17:03.487

2

It seems that you're combining two different approaches (Fluent and Data Annotation). You should stick to one of them. And when attaching your model should have a key (so EF knows what to attach to).

Either:

builder.Entity<Section>()
            .HasKey(p => p.Index);
builder.Entity<Section>()
            .Property(p => p.Index)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

OR

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Key, Column(Order = 0)]
public int Index { get; set; }

EDIT:

If you want to change the value of an identity column you can do something like this:

try
{
    _context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Section] ON");
    //Do updates
    _context.SaveChanges();
}
finally()
{
   _context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Section] OFF");
}

There's a chance that you need to recreate that column instead of updating it.

edited Aug 22 '20 at 14:17

answered May 27 '16 at 08:58

smoksnes

10,509
4
49
74

Yeah I agree, the thing is that I was only using DataAnnotations for the property **[DatabaseGenerated(DatabaseGeneratedOption.Indentity)]** and fluent api for mapping the foreign keys. But since it didn't work I added the line in the second image. – dont_trust_me May 27 '16 at 10:03
I removed the DataAnnotation for [DatabaseGenerated(DatabaseGeneratedOption.Identity)] and replaced it with builder.Entity
().Property(p => p.Index).ValueGeneratedOnAdd(); So now everything happens in the fluent API. But it seems like it doesn't matter because I'm still ending up with the same exception.
– dont_trust_me May 27 '16 at 10:09
The message itself is self-explainatory, you're trying to change the value of an identity column. The question is what's changing it. Is there anything in your code before SaveChanges()? Can you debug and check the value of Index before SaveChanges()? – smoksnes May 27 '16 at 10:33
But should'nt .ValueGeneratedOnAdd(); take care of this? – dont_trust_me May 27 '16 at 11:05
Another question, is it possible to update a column that's being generated with **[DatabaseGenerated(DatabaseGeneratedOption.Indentity)]**? If so, how? – dont_trust_me May 27 '16 at 12:59
Honestly, I've never tried it. But the point is that you shouldn't. By setting it to identity you let the db manage that column. And I really don't see a scenario when I want to change the value of the primary key. It will most likely also cause violations to your constraints. Have you set your primary key? It didn't show in your code. That could be the reason for the error. – smoksnes May 27 '16 at 13:06
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/113149/discussion-between-smoksnes-and-carl91). – smoksnes May 27 '16 at 13:20

Mitch · Answer 2 · 2017-03-08T03:50:56.017

2

For those who run into this, it was a bug with EF Core 1.0. See https://github.com/aspnet/EntityFramework/issues/6426.

It has been marked as fixed in EF Core 1.2.

edited Mar 08 '17 at 03:50

answered Mar 08 '17 at 02:20

Mitch

1,839
16
23

陸普世 · Answer 3 · 2018-03-01T07:12:24.603

I had this issue too!! I tried to add an annotation [Key, Column(Order = 1)] for my property, but it doesn't work.

Try the method below. I use it to fix my problem (.Net Core and EF Core 1.1.1)

1.Please add these code in your Context.cs

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   base.OnModelCreating(modelBuilder);

   // Adding the code below tells DB "NumericId is an AlternateKey and don't update".
   modelBuilder.Entity<User>()
   .HasAlternateKey(x => x.Index ).HasName("IX_Index");
}

2. In PackageManager window, execute update-datebase

Ref:

[Stackoverflow] Unique Key Constraints For Multiple Columns In EntityFramework

[MSDN] Alternate Keys (Unique Constraints)

score 0 · Answer 4 · answered Jan 19 '22 at 17:29

Not sure which version of EF Core you're using, but you might find success with this SO answer: cannot update identity column in Entity Framework Core

modelBuilder.Entity<Type>().Property(u => u.Property).Metadata.SetAfterSaveBehavior(PropertySaveBehavior.Ignore);

Cannot update identity column 'Index'. EF Core

4 Answers4