[Solved] Entity framework – Violation of PRIMARY KEY constraint ‘…’. Cannot insert duplicate key in object ‘dbo…’ the duplicate key value is (…). The statement has been terminated.

If you are struggling to create a new record in a sql db when using entity framework and you are ending up with an error that looks like this:

What this means is that in SQL databases often a table will have a primary key id. And this primary key is generated by the data base everytime a new record is entered.

The reason for this error is that EF is trying to enter the primary key, which it shouldn’t in this case – as this should be left to the DB.

In order to fix it we can check that EF knows that this column is an ID column and that the Database will compute the value for this column. We can do this using the fluent api. This is normally found in the database context file. The start of the file looks like this:

    public partial class yourdataclass : DbContext
    {

        public yourdataclass ()
    : base("name=yourdataclass ")
        {
        }

Below this you will see the references to your models like so:

public virtual DbSet<WorkUnit> WorkUnits { get; set; }
public virtual DbSet<WorkUnitBridge> WorkUnitBridge { get; set; }

public virtual DbSet<YardSurvey> YardSurveys { get; set; }
public virtual DbSet<MapIcon> Icons { get; set; }

If you go futher down, you will see things that look like this, this is called the fluent API, and you can use it to tell EF how to use the DB. If you make sure you add the .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity) option, this will tell EF that this column is generated by the DB, and entity framework will no longer try to insert its own value, fixing the error!

modelBuilder.Entity<YourModel>()
            .Property(e => e.ThePrimaryKeyColumnOfTheModel)
        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); // This tells Entity Framework not to generate values for the ID column