EF Core - Cannot insert explicit value for identity column SqlException

Question

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

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
Something in here might be of use to you: stackoverflow.com/questions/4682504/… — thesystem
Something seems kind of fishy here. Have you tried adding meeting to postCodeDetail.Meetings and saving those changes? — GlennSills
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. — Edward
@Vinyl Warmth, Has your problem been solved or it still exists! — TanvirArjel

Vinyl Warmth Vinyl Warmth · Accepted Answer · 2019-01-04T13:13:14

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

EF Core - Cannot insert explicit value for identity column SqlException

1 Answers