Auto increment Identity column insert error

Question

Ok, first off I have seen several questions on this issue. I have gone through just about all of them and none of the solutions prescribed worked for me. I am using asp.net core 1.1.0 and SQL Server 2016. I am also using the code first approach. The following is my mappings for the table in question

public class TierMap : IEntityTypeConfiguration<Tier>
{
    public void Map(EntityTypeBuilder<Tier> builder)
    {
        builder.HasKey(a => a.TierID);
        builder.Property(a => a.TierID).UseSqlServerIdentityColumn<int>();
        builder.HasOne(a => a.Country)
            .WithMany(a => a.Tiers)
            .HasForeignKey(a => a.CountryID);
    }
}

Below is the design view of the table in SQL Server:

When I create a new instance of a Tier object, all the properties except the TierID get some value (a random negative integer). So inserting into the database throws the following error

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

Below is the code the attempts to save the data

[HttpPost("AddTier")]
public async Task<IActionResult> AddTier([FromBody]TierDataModel model)
{
    if (!ModelState.IsValid)
    {
        Response.AddApplicationError("Data is invalid ");
        return BadRequest();
    }

    Tier tier = new Tier();
    tier.TierName = model.TierName;
    tier.TierNo = model.TierNo;
    tier.CountryID = model.CountryID;
    _tierService.AddTier(tier);

    await _unitOfWorkAsync.SaveChangesAsync();

    return Ok();
}

Try change this line: builder.Property(a => a.TierID).UseSqlServerIdentityColumn<int>(); by: builder.Property(a => a.TierID).UseSqlServerIdentityColumn(); — H. Herzl
So I run this command SET IDENTITY_INSERT dbo.Tiers ON in SQL Server Management studio and provided values for TierID still didnt work out. Strangely the random integer value remained ignoring my provided value for TierID. When I checked my migrations folder i had this in there: "TierID = table.Column<int>(nullable: false) .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)". Hmmmmmm!! What am I missing? — Tee-Jay
Why do you need to run that command?, to add an identity in a table it's simple like Id int not null identity(1, 1) — H. Herzl
@H. Herz Because I wanted to see if I provide the data for the Id column it will still result in that same error and it did which i find very confusing. — Tee-Jay

Tee-Jay Tee-Jay · Accepted Answer · 2017-01-07T20:32:54

Changing _dbSet.Attach(entity); to _dbSet.Add(entity); in my Repository Class solved the problem

Auto increment Identity column insert error

1 Answers