cannot update identity column in Entity Framework Core

Question

I've added a separate Identification to the AspNetUsers table called NumericId that will serve along with the GUID like ID that ASP has for default.

I've added the property as an additional property of the ApplicationUser class:

public class ApplicationUser : IdentityUser
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public virtual int NumericId { get; set; }
}

However, when I try to register, or update the user's details (that aren't even relevant to the numericId) I keep getting the error

SqlException: Cannot update identity column 'NumericId'.

which prevents any changes and in the end does not update the user (but it does register one, and Numeric Id is properly assigned on that part. But this is irrelevant)

Solution 1

Solution for asp.net core 3.1

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

Solution 2

Per the discussion on GitHub surrounding this issue, for EF Core 2.0 we needed to use both lines suggested in other posts.

for Entity framework core 2.0 , The "IsReadOnlyAfterSave" property is deprecated. Use following:

builder.Property(p => p.Id)
    .UseSqlServerIdentityColumn();

builder.Property(p => p.Id)
    .Metadata.AfterSaveBehavior = PropertySaveBehavior.Ignore;

Solution 3

I found this other solution (I am using .NET Core 2.1):

using System;
using System.Collections.Generic;
using System.Diagnostics.Tracing;
using System.Linq;
using System.Security.Cryptography.X509Certificates;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Caching.Memory;
using YetAnotherERP.Data;
using YetAnotherERP.Entities;
using YetAnotherERP.Exceptions;
using YetAnotherERP.Utils;

namespace YetAnotherERP.Services
{
    public class EmployeeRepository : IEmployeeRepository
    {

    private DataContext _context;

    public EmployeeRepository(DataContext dataContext)
    {
        _context = dataContext;
    }


    ....


            public async Task UpdateEmployee(Employee employee)
        {
            _context.Update(employee).Property(x=>x.Id).IsModified = false;
            _context.SaveChanges();
        }

Solution 4

If you are using EF Core 2.1, you can try that.

builder.Property(e => e.ColumnName).Metadata.AfterSaveBehavior = PropertySaveBehavior.Ignore;

It worked for me.

IsReadOnlyAfterSave is deprecated.

Solution 5

This was a bug with EF Core 1.0. See EF trying to Insert into Identity field.

EF Core 1.2 has marked the issue as fixed, but the workaround without updating is to use

modelBuilder.Entity<Type>().Property(u => u.Property).UseSqlServerIdentityColumn();

View more solutions

Comments

[Sanket]

Are you using Fluent API? Any handling for NumericId in OnModelCreating method?

[DethoRhyne]

Not directly. I just use Add-Migration command to the ApplicationDBContext and it generates the code that I use to update the database with.. Update-Database command.

[bricelam]

Looks like a bug. EF is trying to insert a value into the database-generated column. Could you submit a new issue?

[DethoRhyne]

Technically it shouldn't even be inserting. The value is set and the method called should just be an "Update". I Could try making the property null, maybe then it will ignore it, but I'm afraid if doing that will cause some damage instead.

[Gert Arnold]

Missing: the actual code that throws the exception.

[Admin]

stackoverflow.com/questions/49715467/…

[mighty_mite]

I can confirm that this does work, but until the scaffolding update with customizations issue is fixed, this solution would get wiped out and have to be retyped anytime you needed to update your models from the database first approach. Perhaps a better approach to use this would be to create a separate class for this instead of placing it directly in your dbContext file

[HO3EiN]

@mighty_mite yes. you are right. but i used this for code first approach.

[Raj]

This also resolved an issue for me. However I dont understand why a particular entity would need this? Only one of the entities in my code seem to require this line - all other entities update fine without it?

[Pradip Rupareliya]

thanks @HO3EiN, Its works for me in Code first approach in Core 3.1

[Pranshu Jawade]

Tried this. Same result. I am still getting, can not update identity column 'id' error.

[Gert Arnold]

ValueGeneratedOnUpdate and SetAfterSaveBehavior(PropertySaveBehavior.Ignore). That is: do nothing.