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)

Are you using Fluent API? Any handling for NumericId in OnModelCreating method? — Sanket, Aug 27 '16 at 09:37
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. — DethoRhyne, Aug 27 '16 at 16:24
Looks like a bug. EF is trying to insert a value into the database-generated column. Could you [submit a new issue](https://github.com/aspnet/EntityFramework/issues/new)? — bricelam, Aug 29 '16 at 15:57
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. — DethoRhyne, Aug 29 '16 at 18:13

score 23 · Answer 1 · answered Mar 26 '20 at 15:43

23

Solution for asp.net core 3.1

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

answered Mar 26 '20 at 15:43

HO3EiN

893
11
17

1

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 – mighty_mite Apr 16 '20 at 18:11
@mighty_mite yes. you are right. but i used this for code first approach. – HO3EiN Apr 19 '20 at 08:30
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? – Raj Apr 30 '20 at 01:50
1

thanks @HO3EiN, Its works for me in Code first approach in Core 3.1 – Pradip Rupareliya May 12 '20 at 06:10

score 15 · Answer 2 · answered Feb 19 '18 at 19:02

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;

Cooper Wolfe · Answer 3 · 2017-06-15T15:11:13.777

8

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();

edited Jun 15 '17 at 15:11

answered Mar 20 '17 at 15:13

Cooper Wolfe

111
1
7

2

https://stackoverflow.com/questions/49715467/entity-framework-core-2-0-error-does-not-contain-a-definition-for-usesqlserveri – Apr 08 '18 at 07:39

score 8 · Answer 4 · answered Jul 31 '18 at 04:07

8

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.

answered Jul 31 '18 at 04:07

Malhaar Punjabi

735
7
9

score 8 · Answer 5 · answered Aug 15 '18 at 16:20

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();
        }

score 2 · Answer 6 · answered May 23 '17 at 00:14

2

This line solved my problem. Available from 1.1.1

modelBuilder.Entity<ApplicationUser>().Property(u => u.NumberId).Metadata.IsReadOnlyAfterSave = true;

answered May 23 '17 at 00:14

TonySpark

21
3

ebadola sobhanverdy · Answer 7 · 2022-02-13T11:20:28.947

2

Solution for asp.net core 5 EF 5

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

edited Feb 13 '22 at 11:20

answered Feb 11 '22 at 17:12

ebadola sobhanverdy

49
3

`ValueGeneratedOnUpdate` and `SetAfterSaveBehavior(PropertySaveBehavior.Ignore)`. That is: do nothing. – Gert Arnold Feb 11 '22 at 18:55

score 0 · Answer 8 · answered Jun 15 '20 at 19:18

0

Using 3.1, the key is to make sure the identity value of the object you are updating matches the value of the record in the database. So if your object's ID is 0 but the id of the corresponding row in the database is 99, make sure the object's ID value is set to 99 before you try to save it.

answered Jun 15 '20 at 19:18

Hoodlum

1,443
1
13
24

Tried this. Same result. I am still getting, can not update identity column 'id' error. – Pranshu Jawade Apr 26 '21 at 07:43

Bayan Al-Atrash · Answer 9 · 2021-01-07T04:43:14.213

0

Actually this caused by the creation of primary key by entity framework it self although we have an Id column in userIdentity I solved it here in the onModelCreation function inside DBContext

       base.OnModelCreating(modelBuilder);

edited Jan 07 '21 at 04:43

answered Jan 07 '21 at 04:33

Bayan Al-Atrash

1
2

score 0 · Answer 10 · answered Sep 28 '21 at 13:35

Based on the thread on GitHub here, the key (pun intended) is to mark the property as an alternate key, so no attempt is made by EF Core in subsequent updates to try set a value for the underlying identity column which is read-only.

Under the hood it's probably doing the same as other answers, but this would be the semantically correct way to fix it.

Fluent-style configuration:

entityTypeBuilder.HasAlternateKey(e => e.NumericId);

score 0 · Answer 11 · answered Dec 14 '21 at 12:02

0

In my case (core 3.1), i noticed, i was labeled to identity another column by mistake , problem solved when i sign the true column as key

answered Dec 14 '21 at 12:02

Mine.e

1

cannot update identity column in Entity Framework Core

11 Answers11

Linked