.NET 5 - Prevent update on auto-increment column?

Question

I am trying to create an auto-increment field on my Identity Users table called MembershipID, but I am having some issues.

I subclass the Identity user and have created my own "ApplicationUser" so I can add additional fields, and this all works fine.

ApplicationUser.cs

public class ApplicationUser : IdentityUser
{
    public int Currency { get; set; }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int membershipID { get; set; }
    public string Name { get; set; }


}

(NOTE - I have tried both with and without the [key] annotation).

The issue The field is generated and auto-incremented just fine on model creation. However, every single time I update the model, let's say name, I get an error saying:

SqlException: Cannot update identity column 'membershipID'.

Why is that? I thought that by saying it is a database generated value it wouldn't do this? How do I prevent it from updating the column? I am NOT trying to edit the auto-incremented field, I am editing another column but somehow it tries to update the other field as well which causes the issue.

Thanks all.

either remove `[DatabaseGenerated(DatabaseGeneratedOption.Identity)]` attribute or do not give membershipID explicitly while inserting. Sql wont allow user passed value on identity enabled columns — Beingnin, Apr 25 '21 at 05:58
i am not trying to update or change it, but if i change another field in the model (say the name) it also tries to change that field (which i don't want it to) — Seerex, Apr 25 '21 at 06:01
I am confused. Your class is "IdentityUser" and the table is called "table called MembershipID"? But the field in the class is an int? And it is defined in ApplicationUser? — Klamsi, Apr 25 '21 at 06:18
you should post your involved code to show that it does not touch the `membershipID`. The error says it clearly that somehow that's changed. The problem is not to find out how to prevent it, you need to find out how ***why*** it's modified. — King King, Apr 25 '21 at 09:00
When update the entity, for the Identity column, we should ignore the value set or changed. As Dionis Takac said, you could add `builder.Entity().Property(u => u.membershipID).Metadata.SetAfterSaveBehavior(PropertySaveBehavior.Ignore);` in the ApplicationDbContext's OnModelCreating method. Or set the property's IsModified property to **false** when udpate the enttity, code like this: `var user = //find exist user from database; // update the name _context.Update(user).Property(c => c.membershipID).IsModified = false; _context.SaveChanges();` — Zhi Lv, Apr 26 '21 at 08:08

score 1 · Answer 1 · answered May 19 '22 at 19:02

1

The below approach worked for me on asp.net core 6

    builder.Entity<Entity>(b =>
        {
          b.Property(x => x.ColumnName).IsRequired().ValueGeneratedOnAddOrUpdate();
        });

answered May 19 '22 at 19:02

Eyayu Tefera

771
9
9

score 0 · Answer 2 · answered Apr 25 '21 at 09:14

I found that somebody reported similar issue before. Try to add something like this in your OnModelCreating method if you use core 3.1:

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

Here is similar issue - cannot update identity column in Entity Framework Core. You can find there solutions for earlier versions of core.

.NET 5 - Prevent update on auto-increment column?

2 Answers2