Cannot insert explicit value for identity column in table ef6 db first

Question

While creating application with c# + entity framework 6 (database first) + sql server I got next problem:

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

I've tried to google my problem and the most popular answer was just to add attribure to model

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]

But it doesn't help. Then I decided to run SQL command "SET IDENTITY_INSERT dbo.Qualification ON", but it didn't help as well. In my db (I use management studio) Identity Specification (Is Identity) set to TRUE

There is my entity generated by .NET:

public partial class Qualification
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Qualification()
    {
        this.Trainer = new HashSet<Trainer>();
    }

    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Trainer> Trainer { get; set; }
}

And my context class:

 public partial class SportClubContext : DbContext
{
    public SportClubContext()
        : base("name=SportClubContext")
    {
        
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }

    public virtual DbSet<sysdiagrams> sysdiagrams { get; set; }
    public virtual DbSet<Client> Client { get; set; }
    public virtual DbSet<Group> Group { get; set; }
    public virtual DbSet<Qualification> Qualification { get; set; }
    public virtual DbSet<Schedule> Schedule { get; set; }
    public virtual DbSet<Trainer> Trainer { get; set; }
    public virtual DbSet<Training> Training { get; set; }
}

And the way how I try to add new record to my db:

var qualification = new Qualification
        {
            Name = textBox1.Text
        };

context.Qualification.Add(qualification);
await context.SaveChangesAsync();

I don't see anything wrong with the way you're trying to do this... Your error says you are trying to insert a value into field that is defined as identity, but if your insert code is accurate, and this is where the error is being thrown, I can't tell why it's erroring. — Jonathan

Steve Py Steve Py · Accepted Answer · 2021-06-03T22:07:19

With [DatabaseGenerated(DatabaseGeneratedOption.Identity)] on the ID column it should be inserting the row without that error. The next thing to check would be that the identity column in your Qualification table is actually called "Id" and not something like "QualificationId" or such. (Check the name of the column and that this column is actually the one set to "Identity" in the table)

Also check that after adding that attribute and running that you have rebuilt the entire solution (in case your project structure is not using a direct dependency but rather loosely coupled web services) and also that any exception you are still receiving is the Identity related issue on that Qualification table and not a different exception. (That one catches me out where after getting the same exception a while and trying different things to fix it, I'm chasing an original problem that is fixed and it's something else now causing an exception in the same spot, but different message.)

Cannot insert explicit value for identity column in table ef6 db first

1 Answers