Cannot insert explicit value for identity column

Question

I'm getting the above error when creating a new record. I don't want to insert the identity -- the database has it auto generated and that is great.

Here is the exact exception:

[System.Data.UpdateException]
{"An error occurred while updating the entries. See the inner exception for details."}
{"Cannot insert explicit value for identity column in table 'PartRevisions' when IDENTITY_INSERT is set to OFF."}

Here are the mappings:

Public Class PartContext
    Inherits DbContext

    Public Property Parts As DbSet(Of Part)
    Public Property PartRevisions As DbSet(Of PartRevision)


    Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
        MyBase.OnModelCreating(modelBuilder)

        modelBuilder.Entity(Of PartRevision)().HasKey(Function(r) r.Id).Property(Function(r) r.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
    End Sub

End Class

<Table("Parts")>
Public Class Part
    <Key(), DatabaseGenerated(DatabaseGeneratedOption.Identity)>
    Public Property Id As Integer

    Public Property PartNumber As String
    Public Property Owner As String
End Class

<Table("PartRevisions")>
Public Class PartRevision
    Inherits Part

    Public Property OriginalId As Integer
    Public Property RevisionDate As DateTime
    Public Property RevisionNumber As Integer
    Public Property RevisionBy As String

End Class

If I don't use inheritance it works fine. If I make Id overridable and also specify the attributes on the sub class it still doesn't work.

I'm doing the OnModelCreating stuff just because I'm trying to get it to work. I feel like it should work without this. Of course it doesn't even work with this...

The following query works fine when I execute it in SQL Management Studio:

insert into PartRevisions (originalid, revisiondate, revisionnumber, revisionby, partnumber, owner)
values (1, '1/1/2013', 1, 'eep', '123', 'ME')

Here is a gist with the full program. I was trying this out in a test project because I assumed I'd run into some issues doing inheritance in EF (never done yet).

https://gist.github.com/eyston/4956444

Thanks!

Answer 1

The PartRevisions table must not have the Id column set as an autogenerated identity, only the table Parts for the base type. The two tables have shared primary keys. EF joins the two tables when it queries a PartRevision and it inserts rows into both tables if a PartRevision entity is inserted. Because both rows must have the same Id only one can be an identity.