Cannot insert explicit value for identity column in table 'Regions' when IDENTITY

I have a table called Regions with an auto increment ID column. When adding data to the regions table through my ASP.NET Core application, I get this error:

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

I know why this is happening, I just don't know how to prevent EF Core from writing a value into this Id column. I have tried a few things to try and mitigate this but to no avail.

Here is my model, dbContext and the CreateRegions method. The primary key on this table is the RegionId.

Model class:

public partial class RegionsModel
{
    [DisplayName("No :")]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
    [Key]
    public Guid RegionId { get; set; }
    [DisplayName("Region Name :")]
    public string RegionName { get; set; } = null!;
}

DbContext:

    modelBuilder.Entity<RegionsModel>(entity =>
    {
        entity.Property(e => e.Id).ValueGeneratedNever();

        entity.Property(e => e.RegionId).HasDefaultValueSql("(newid())");

        entity.Property(e => e.RegionName).HasMaxLength(50);
    });

Create region method:

public async Task<IActionResult> CreateRegion([Bind("Id,RegionId,RegionName")] RegionsModel regionsModel)
{
    if (ModelState.IsValid)
    {
        _context.Add(regionsModel);
        await _context.SaveChangesAsync();
        return RedirectToAction(nameof(RegionsIndex));
    }
    return View(regionsModel);
}

CodePudding user response：

You need to change two things:

(1) The data annotation on your model class needs to be [DatabaseGenerated(DatabaseGeneratedOption.Identity)] :

public partial class RegionsModel
{
    [DisplayName("No :")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    [Key]
    public Guid RegionId { get; set; }
    [DisplayName("Region Name :")]
    public string RegionName { get; set; } = null!;
}

(2) Your OnModelCreating needs to define the column Id as being set on Add:

modelBuilder.Entity<RegionsModel>(entity =>
{
    // set this to "ValueGeneratedOnAdd" 
    entity.Property(e => e.Id).ValueGeneratedOnAdd();

    entity.Property(e => e.RegionId).HasDefaultValueSql("(newid())");
    entity.Property(e => e.RegionName).HasMaxLength(50);
});

That defines Id to be an IDENTITY column in your database table, and sets up EF Core to let SQL Server add a new value "on add" (when inserting a new row into the table) to that column.

CodePudding user response：

Table it has to be explicit about SET IDENTITY_INSERT RegionsModel ON. Changes shown on the ef model doesn't set table insert identity. Maybe there is, but I don't know if there's an entity framework fluent api to set IDENTITY_INSERT on.

Try in a new ef migration added to do: migrationBuilder.Sql("SET IDENTITY_INSERT RegionsModel ON").

Or in sql if DB first approach is used, just manually run query SET IDENTITY_INSERT RegionsModel ON.

Don't know your flow, but I don't think is the approach is correct, if the table has to exit with IDENTITY_INSERT OFF, maybe there's some kind of id's reservation before insert, than yes it make sense.

Personally, I would do the classical way, off/on at db insert

    dataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT RegionsModel  ON");
    dataContext.RegionsModel.Add(new RegionsModel  {Id = 1234});
    dataContext.SaveChanges();
    dataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT RegionsModel  OFF");