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.

1
1
3/29/2016 8:38:21 PM

Accepted Answer

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.

0
3/30/2016 5:18:13 PM

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow