ASP.NET MVC error SqlException: Cannot insert explicit value for identity column in table 'Users' when IDENTITY_INSERT is set to OFF

Question

I'm trying to make a login AUTH with C# .NET but I'm getting this error and can't solve it.

Here is my code.

User class

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

RegisterDTO

    public string Name { get; set; }
    public string Email { get; set; }
    public string Password { get; set; }

IUserRepository

public class UserRepository : IUserRepository
{
    private readonly ApiDbContext _context;

    public UserRepository(ApiDbContext context) 
    {
        _context = context;
    }

    public User Create(User user)
    {
        _context.Users.Add(user);
        user.UserId =_context.SaveChanges();
        return user;
    }
}

And the error I'm getting is when I start Swagger and trying to post username email and password:

SqlException: Cannot insert explicit value for identity column in table 'Users' when IDENTITY_INSERT is set to OFF.

Out of curiosity, what does the config look like in the context for the user entity? (Post the onmodelcreating section that sets up the user entity, if it exists) — Caius Jard
Also, the int returned from SaveChanges is NOT any kind of id- it is the number of entities that were saved. The value generated by the db will be automatically placed in your user ID; you do not need to set it. As a slight terminology correction, swagger is a specification; you cannot "start it". You're probably referring to swaggerUI; a web page that generates a bunch of example operations and tryouts based on the swagger/open api spec your project exports — Caius Jard

CMGeek CMGeek · Accepted Answer · 2021-10-05T18:47:13

In your database, your Users table is defined as

CREATE TABLE Users (
...
UserId int identity(1,1)
...
)

This means that your database wants to generate the UserId value for you, unless Identity_Insert=off is specified when a record in inserted. However, when you are calling Create(), the query that is being executed includes the UserId column.

You don't state in your question (or your tags) that your are using EntityFramework, but from your syntax, I am making that assumption. As such, you should specify this in your OnModelCreating method.

protected override void OnModelCreating(ModelBuilder modelBuilder) 
{
...
modelBuilder.Entity<Users>(Entity =>
  {
...
    Entity.Property(e=>e.UserId).ValueGeneratedOnAdd();
...
  }
...
}

ASP.NET MVC error SqlException: Cannot insert explicit value for identity column in table 'Users' when IDENTITY_INSERT is set to OFF

1 Answers