When using composite key: Cannot insert explicit value for identity column in table '' when IDENTITY_INSERT is set to OFF

Question

When using composite key: Cannot insert explicit value for identity column in table '' when IDENTITY_INSERT is set to OFF

database ef-code-first entity-framework entity-framework-6

Question

My model looks the following:

public class AliveCheckedRealestate
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Key, Column(Order = 0)]
    public int RealestateId { get; set; }

    [Key, Column(Order = 1)]
    [ForeignKey("Job")]
    public int JobId { get; set; }


    public virtual AliveCheckJob Job { get; set; }
}

Whenever I add a new AliveCheckedRealestate to the context I do the following:

job.AliveCheckedRealestates.Add(new AliveCheckedRealestate(){RealestateId = 33});

(Job is a preexisting job in the database)

However, every time I try to save the changes (that is, the new AliveCheckedRealestate), I get the following exception:

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

What could be the issue here? RealestateId is a unique integer that I manually set (it's not a foreign key) and I DO set it.

J. Doe

Popular Answer

There is an option in the context class of your entityframework called ValueGeneratedNever set on the identity, remove it and use

entity.HasKey(e => e.Id);
entity.ToTable("NameofYourtable");

// also use

yourContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.YourTableName ON");
yourContext.SaveChanges();
yourContext.YourTableFromDB.Add(YourParameterCommingin);
yourContext.SaveChanges();`

let me know if this helped

Shailesh

View more on Stack Overflow

univ · Accepted Answer · 3/30/2016 5:18:13 PM

You are going to have to execute the following sql command against the table:

SET IDENTITY_INSERT Table_Name ON;

I don't know of any inbuilt way of telling EF to set identity insert on, as far as I know the only way is to do it with SQL.

NOTE: Not a good practice, not advised at all. You may very well end up having duplicate values so let the identity column generate the values for you. if you want to be able to insert the values yourself then do not make it an identity column at all.

When using composite key: Cannot insert explicit value for identity column in table '' when IDENTITY_INSERT is set to OFF

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related