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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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

Shailesh

Related Questions

EntityFramework - Composite Key Table with FK to another Composite Key Table

I've ...two tables... both with ...composite primary keys.... ...Both... have in common ...one of the primary key's with a foreign key... to another table....The problem is that when i create the migrations, it messes up the foreign keys....I must use data annotations....Example:...public class City { [Key, Column(Order = 1)] [DatabaseGener...
asp.net-mvc-5 composite-primary-key entity-framework entity-framework-6 foreign-key-relationship
asked by Leandro Soares

Cannot insert explicit value for identity column while updating entity

I tried this ... public override async Task<StudentDegreeDto> Create(StudentDegreeCreateDto input) { //CheckCreatePermission(); StudentDegreeCore st = new StudentDegreeCore(); try { StudentDegreeCore core = new StudentDegreeCore() { ...
aspnetboilerplate entity-framework entity-framework-6 identity-insert
asked by Nighil

EF Core: Column with ValueGeneratedOnAdd trows "Cannot insert explicit value for identity column..."

I have ASP.NET MVC 5 application that uses Entity Framework 6.2. This app creates some records in MS SQL server....I have to process this records in AWS lambda function. For this purpose, I created c# .NET Core v2.0 lambda function. There I generate models from existed DB as described in the next article - ...Getting Started with EF Core on ASP.NET...
.net-core aws-lambda c# entity-framework entity-framework-core
asked by RredCat

Cannot insert explicit value for identity column - into related table

I have a database first model....My application UI provides a group of checkboxes, one for each value in Data_Type....When the user checks one, I expect a row to be added in BUS_APPL_DATA_TYPE, however I'm getting an error about Cannot insert explicit value for identity column in DATA_TYPE (And I absolutely do not actually want to insert data in t...
entity-framework-core
asked by Chris

EF Core "Cannot insert explicit value for identity column" although IDENTIY_INSERT is set to ON

It was a mix of the comment of @damien-the-unbeliever and the suggested answer of @ivan stoev...You can only have IDENTITY_INSERT turned ON for one table at a time...The code must be wrapped in a transaction because EF Core will use separate connections otherwise....Here is the code...using (var identityContext = new IdentityDatabase(identityOption...
c# entity-framework-core sql-server
asked by wertzui

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

I had a similar problem when trying to save an entity (for ex., ...Cat...) which had many-to-one relationships to existing entities (property ...Owner..., pointing at ...Person...). In addition to a) getting the relationship (Person) from the database before saving the entity (...Cat...), and b) adding another property (...int PersonId...) to the e...
asp.net-core entity-framework-core sql-server
asked by Mr. TA

EF Core - Cannot insert explicit value for identity column SqlException

The cause of this issue was that I was calling ...SaveChanges... on a different context to the context I was creating the entity with.
asp.net-core entity-framework-core
asked by Vinyl Warmth

"Cannot insert explicit value for identity column in table" when inserting object with EF Core

It turned out I had updated the primary key column, ...PkEmployeePermission... to be an identity column and forgotten to re-generate the database scaffolding using EF Core....The root cause of the problem was that inside of the ...OnModelCreating()... method (in the generated database context file) the field ...PkEmployeePermission... had the metho...
c# entity-framework entity-framework-core sql-server
asked by Mr. King

Cannot insert explicit value for identity column in table 'xxx' when IDENTITY_INSERT is set to OF

I have checked all the topics on this error with entitframework but I can't find my issue....I use entityframework core....I have an accomplishment and a criteriagroup in the accomplishment class....When I save for the first time the accomplishment everything works....Then when I want to update it I gave the error "Cannot insert explicit value for ...
asp.net asp.net-core c# entity-framework-core
asked by dalton5

Cannot insert explicit value when IDENTITY_INSERT is set to OFF in Entity Framework

I'm trying to add data to my database and I keep getting this error: ...Cannot insert explicit value for identity column in table "Terms" when IDENTITY_INSERT is set to OFF...The exception gets thrown on ...SaveChanges().... I've already tried using ...[DatabaseGenerated(DatabaseGeneratedOption.Identity)]... above ...public int TermId { get; set; }...
asp.net-mvc c# entity-framework-core ssms
asked by Laberge

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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