EF Core - Cannot insert explicit value for identity column SqlException

In my app I am using EF Core to define 2 entities (Code First), Meeting and PostcodeDetail defined as follows:

public class Meeting

{

  public int Id { get; set;}

  public PostcodeDetail PostcodeDetail { get; set; }



  // other properties removed for brevity

}



public class PostcodeDetail

{

  public int Id { get; set; }

  public ICollection<Meeting> Meetings { get; set; } = new List<Meeting>();



  // other properties removed for brevity

}

When I create a new Meeting and try assigning an existing PostcodeDetail entity as follows:

var meeting = new Meeting();

var details = context.PostcodeDetails

                        .SingleOrDefault(i => i.Prefix == prefix);



meeting.PostcodeDetail = details;

context.SaveChanges();

I get this exception:

Microsoft.EntityFrameworkCore.DbUpdateException: SqlException: Cannot insert explicit value for identity column in the table 'PostcodeDetail' when IDENTITY_INSERT is set to OFF

I can't see why an insert statement is executing on PostcodeDetail, as I am retrieving an exisiting entity from the database - can anyone see what I'm doing wrong here?

Edit: When I run SQL Server Profiler I can see the following is executed

INSERT INTO [PostcodeDetail] ([Id], [DateCreated], [DateModified], [District], [Prefix], [Region])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5);
',N'@p0 int,@p1 datetime2(7),@p2 datetime2(7),@p3 nvarchar(4000),@p4 nvarchar(4000),@p5 nvarchar(4000)',@p0=113,@p1='2019-01-02 15:50:49.5874691',@p2='2019-01-02 15:50:49.5874640',@p3=N'Guernsey',@p4=N'GY',@p5=N'Channel Islands'

I don't know why an insert is generated, as I am getting the PostcodeDetail from the database and referencing it on the new Meeting

edited Jan 2 at 15:54

asked Jan 2 at 9:42

Vinyl Warmth

5751625

1

Could the problem be that since you are trying to "create a new meeting", it doesn't yet exist in the database and therefore is missing the Id, which it is used as primary key? EDIT: The thing is, since identity_insert is off, you have to configure the primary key value yourself, I believe.

– thesystem
Jan 2 at 10:13

Something in here might be of use to you: stackoverflow.com/questions/4682504/…

– thesystem
Jan 2 at 10:16

Something seems kind of fishy here. Have you tried adding meeting to postCodeDetail.Meetings and saving those changes?

– GlennSills
Jan 2 at 17:29

Is there any demo to reproduce your issue? For your current code, you did not call context.Add(meeting); before context.SaveChanges();, it should not make any changes for the database. But for your SQL result, it seems you are running an insert operation. Share us the complete code for this action.

– Tao Zhou
Jan 3 at 6:12

@Vinyl Warmth, Has your problem been solved or it still exists!

– TanvirArjel
Jan 4 at 7:54

add a comment |

In my app I am using EF Core to define 2 entities (Code First), Meeting and PostcodeDetail defined as follows:

public class Meeting

{

  public int Id { get; set;}

  public PostcodeDetail PostcodeDetail { get; set; }



  // other properties removed for brevity

}



public class PostcodeDetail

{

  public int Id { get; set; }

  public ICollection<Meeting> Meetings { get; set; } = new List<Meeting>();



  // other properties removed for brevity

}

When I create a new Meeting and try assigning an existing PostcodeDetail entity as follows:

var meeting = new Meeting();

var details = context.PostcodeDetails

                        .SingleOrDefault(i => i.Prefix == prefix);



meeting.PostcodeDetail = details;

context.SaveChanges();

I get this exception:

Microsoft.EntityFrameworkCore.DbUpdateException: SqlException: Cannot insert explicit value for identity column in the table 'PostcodeDetail' when IDENTITY_INSERT is set to OFF

I can't see why an insert statement is executing on PostcodeDetail, as I am retrieving an exisiting entity from the database - can anyone see what I'm doing wrong here?

Edit: When I run SQL Server Profiler I can see the following is executed

INSERT INTO [PostcodeDetail] ([Id], [DateCreated], [DateModified], [District], [Prefix], [Region])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5);
',N'@p0 int,@p1 datetime2(7),@p2 datetime2(7),@p3 nvarchar(4000),@p4 nvarchar(4000),@p5 nvarchar(4000)',@p0=113,@p1='2019-01-02 15:50:49.5874691',@p2='2019-01-02 15:50:49.5874640',@p3=N'Guernsey',@p4=N'GY',@p5=N'Channel Islands'

I don't know why an insert is generated, as I am getting the PostcodeDetail from the database and referencing it on the new Meeting

edited Jan 2 at 15:54

asked Jan 2 at 9:42

Vinyl Warmth

5751625

1

Could the problem be that since you are trying to "create a new meeting", it doesn't yet exist in the database and therefore is missing the Id, which it is used as primary key? EDIT: The thing is, since identity_insert is off, you have to configure the primary key value yourself, I believe.

– thesystem
Jan 2 at 10:13

Something in here might be of use to you: stackoverflow.com/questions/4682504/…

– thesystem
Jan 2 at 10:16

Something seems kind of fishy here. Have you tried adding meeting to postCodeDetail.Meetings and saving those changes?

– GlennSills
Jan 2 at 17:29

Is there any demo to reproduce your issue? For your current code, you did not call context.Add(meeting); before context.SaveChanges();, it should not make any changes for the database. But for your SQL result, it seems you are running an insert operation. Share us the complete code for this action.

– Tao Zhou
Jan 3 at 6:12

@Vinyl Warmth, Has your problem been solved or it still exists!

– TanvirArjel
Jan 4 at 7:54

add a comment |

In my app I am using EF Core to define 2 entities (Code First), Meeting and PostcodeDetail defined as follows:

public class Meeting

{

  public int Id { get; set;}

  public PostcodeDetail PostcodeDetail { get; set; }



  // other properties removed for brevity

}



public class PostcodeDetail

{

  public int Id { get; set; }

  public ICollection<Meeting> Meetings { get; set; } = new List<Meeting>();



  // other properties removed for brevity

}

When I create a new Meeting and try assigning an existing PostcodeDetail entity as follows:

var meeting = new Meeting();

var details = context.PostcodeDetails

                        .SingleOrDefault(i => i.Prefix == prefix);



meeting.PostcodeDetail = details;

context.SaveChanges();

I get this exception:

Microsoft.EntityFrameworkCore.DbUpdateException: SqlException: Cannot insert explicit value for identity column in the table 'PostcodeDetail' when IDENTITY_INSERT is set to OFF

I can't see why an insert statement is executing on PostcodeDetail, as I am retrieving an exisiting entity from the database - can anyone see what I'm doing wrong here?

Edit: When I run SQL Server Profiler I can see the following is executed

INSERT INTO [PostcodeDetail] ([Id], [DateCreated], [DateModified], [District], [Prefix], [Region])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5);
',N'@p0 int,@p1 datetime2(7),@p2 datetime2(7),@p3 nvarchar(4000),@p4 nvarchar(4000),@p5 nvarchar(4000)',@p0=113,@p1='2019-01-02 15:50:49.5874691',@p2='2019-01-02 15:50:49.5874640',@p3=N'Guernsey',@p4=N'GY',@p5=N'Channel Islands'

I don't know why an insert is generated, as I am getting the PostcodeDetail from the database and referencing it on the new Meeting

edited Jan 2 at 15:54

asked Jan 2 at 9:42

Vinyl Warmth

5751625

In my app I am using EF Core to define 2 entities (Code First), Meeting and PostcodeDetail defined as follows:

public class Meeting

{

  public int Id { get; set;}

  public PostcodeDetail PostcodeDetail { get; set; }



  // other properties removed for brevity

}



public class PostcodeDetail

{

  public int Id { get; set; }

  public ICollection<Meeting> Meetings { get; set; } = new List<Meeting>();



  // other properties removed for brevity

}

When I create a new Meeting and try assigning an existing PostcodeDetail entity as follows:

var meeting = new Meeting();

var details = context.PostcodeDetails

                        .SingleOrDefault(i => i.Prefix == prefix);



meeting.PostcodeDetail = details;

context.SaveChanges();

I get this exception:

Microsoft.EntityFrameworkCore.DbUpdateException: SqlException: Cannot insert explicit value for identity column in the table 'PostcodeDetail' when IDENTITY_INSERT is set to OFF

I can't see why an insert statement is executing on PostcodeDetail, as I am retrieving an exisiting entity from the database - can anyone see what I'm doing wrong here?

Edit: When I run SQL Server Profiler I can see the following is executed

INSERT INTO [PostcodeDetail] ([Id], [DateCreated], [DateModified], [District], [Prefix], [Region])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5);
',N'@p0 int,@p1 datetime2(7),@p2 datetime2(7),@p3 nvarchar(4000),@p4 nvarchar(4000),@p5 nvarchar(4000)',@p0=113,@p1='2019-01-02 15:50:49.5874691',@p2='2019-01-02 15:50:49.5874640',@p3=N'Guernsey',@p4=N'GY',@p5=N'Channel Islands'

I don't know why an insert is generated, as I am getting the PostcodeDetail from the database and referencing it on the new Meeting

asp.net-core entity-framework-core

edited Jan 2 at 15:54

asked Jan 2 at 9:42

Vinyl Warmth

5751625

edited Jan 2 at 15:54

asked Jan 2 at 9:42

Vinyl Warmth

5751625

edited Jan 2 at 15:54

asked Jan 2 at 9:42

Vinyl Warmth

5751625

asked Jan 2 at 9:42

Vinyl Warmth

5751625

asked Jan 2 at 9:42

Vinyl Warmth

5751625

1

Could the problem be that since you are trying to "create a new meeting", it doesn't yet exist in the database and therefore is missing the Id, which it is used as primary key? EDIT: The thing is, since identity_insert is off, you have to configure the primary key value yourself, I believe.

– thesystem
Jan 2 at 10:13

Something in here might be of use to you: stackoverflow.com/questions/4682504/…

– thesystem
Jan 2 at 10:16

Something seems kind of fishy here. Have you tried adding meeting to postCodeDetail.Meetings and saving those changes?

– GlennSills
Jan 2 at 17:29

Is there any demo to reproduce your issue? For your current code, you did not call context.Add(meeting); before context.SaveChanges();, it should not make any changes for the database. But for your SQL result, it seems you are running an insert operation. Share us the complete code for this action.

– Tao Zhou
Jan 3 at 6:12

@Vinyl Warmth, Has your problem been solved or it still exists!

– TanvirArjel
Jan 4 at 7:54

add a comment |

1

Could the problem be that since you are trying to "create a new meeting", it doesn't yet exist in the database and therefore is missing the Id, which it is used as primary key? EDIT: The thing is, since identity_insert is off, you have to configure the primary key value yourself, I believe.

– thesystem
Jan 2 at 10:13

Something in here might be of use to you: stackoverflow.com/questions/4682504/…

– thesystem
Jan 2 at 10:16

Something seems kind of fishy here. Have you tried adding meeting to postCodeDetail.Meetings and saving those changes?

– GlennSills
Jan 2 at 17:29

Is there any demo to reproduce your issue? For your current code, you did not call context.Add(meeting); before context.SaveChanges();, it should not make any changes for the database. But for your SQL result, it seems you are running an insert operation. Share us the complete code for this action.

– Tao Zhou
Jan 3 at 6:12

@Vinyl Warmth, Has your problem been solved or it still exists!

– TanvirArjel
Jan 4 at 7:54

Could the problem be that since you are trying to "create a new meeting", it doesn't yet exist in the database and therefore is missing the Id, which it is used as primary key? EDIT: The thing is, since identity_insert is off, you have to configure the primary key value yourself, I believe.

– thesystem
Jan 2 at 10:13

Something in here might be of use to you: stackoverflow.com/questions/4682504/…

– thesystem
Jan 2 at 10:16

Something seems kind of fishy here. Have you tried adding meeting to postCodeDetail.Meetings and saving those changes?

– GlennSills
Jan 2 at 17:29

Is there any demo to reproduce your issue? For your current code, you did not call context.Add(meeting); before context.SaveChanges();, it should not make any changes for the database. But for your SQL result, it seems you are running an insert operation. Share us the complete code for this action.

– Tao Zhou
Jan 3 at 6:12

@Vinyl Warmth, Has your problem been solved or it still exists!

– TanvirArjel
Jan 4 at 7:54

add a comment |

2 Answers
2

active

oldest

votes

may be you are trying to assign a value explicitly to a column where the database automatically assigns it.
or you created a non-identity PK field on a table. You can set that like this
enter image description here

or you can also do that like this with identity fields:

[Key]

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]

public int ID { get; set; }

edited Jan 4 at 7:18

piet.t

10.1k73246

answered Jan 2 at 10:44

ArunPratap

2,14121028

This question is for EF Core. EDMX isn't used in EF Core.

– Vinyl Warmth
Jan 2 at 12:44

add a comment |

The cause of this issue was that I was calling SaveChanges on a different context to the context I was creating the entity with.

answered Jan 4 at 13:13

Vinyl Warmth

5751625

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54004098%2fef-core-cannot-insert-explicit-value-for-identity-column-sqlexception%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

or you can also do that like this with identity fields:

[Key]

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]

public int ID { get; set; }

edited Jan 4 at 7:18

piet.t

10.1k73246

answered Jan 2 at 10:44

ArunPratap

2,14121028

This question is for EF Core. EDMX isn't used in EF Core.

– Vinyl Warmth
Jan 2 at 12:44

add a comment |

or you can also do that like this with identity fields:

[Key]

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]

public int ID { get; set; }

edited Jan 4 at 7:18

piet.t

10.1k73246

answered Jan 2 at 10:44

ArunPratap

2,14121028

This question is for EF Core. EDMX isn't used in EF Core.

– Vinyl Warmth
Jan 2 at 12:44

add a comment |

or you can also do that like this with identity fields:

[Key]

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]

public int ID { get; set; }

edited Jan 4 at 7:18

piet.t

10.1k73246

answered Jan 2 at 10:44

ArunPratap

2,14121028

or you can also do that like this with identity fields:

[Key]

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]

public int ID { get; set; }

edited Jan 4 at 7:18

piet.t

10.1k73246

answered Jan 2 at 10:44

ArunPratap

2,14121028

edited Jan 4 at 7:18

piet.t

10.1k73246

edited Jan 4 at 7:18

piet.t

10.1k73246

edited Jan 4 at 7:18

piet.t

10.1k73246

answered Jan 2 at 10:44

ArunPratap

2,14121028

answered Jan 2 at 10:44

ArunPratap

2,14121028

answered Jan 2 at 10:44

ArunPratap

2,14121028

This question is for EF Core. EDMX isn't used in EF Core.

– Vinyl Warmth
Jan 2 at 12:44

add a comment |

This question is for EF Core. EDMX isn't used in EF Core.

– Vinyl Warmth
Jan 2 at 12:44

This question is for EF Core. EDMX isn't used in EF Core.

– Vinyl Warmth
Jan 2 at 12:44

add a comment |

The cause of this issue was that I was calling SaveChanges on a different context to the context I was creating the entity with.

answered Jan 4 at 13:13

Vinyl Warmth

5751625

add a comment |

The cause of this issue was that I was calling SaveChanges on a different context to the context I was creating the entity with.

answered Jan 4 at 13:13

Vinyl Warmth

5751625

add a comment |

The cause of this issue was that I was calling SaveChanges on a different context to the context I was creating the entity with.

answered Jan 4 at 13:13

Vinyl Warmth

5751625

The cause of this issue was that I was calling SaveChanges on a different context to the context I was creating the entity with.

answered Jan 4 at 13:13

Vinyl Warmth

5751625

answered Jan 4 at 13:13

Vinyl Warmth

5751625

answered Jan 4 at 13:13

Vinyl Warmth

5751625

answered Jan 4 at 13:13

Vinyl Warmth

5751625

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu