EF Core - Cannot insert explicit value for identity column SqlException

asp.net-core entity-framework-core

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

1
1
1/2/2019 3:54:15 PM

Accepted Answer

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

0
1/4/2019 1:13:14 PM

Popular Answer

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; }


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