Can't insert explicit value for identity column
I am getting the above error while creating a new post. I don't want to insert the ID - the database is automatically generated, which is great.
Here's 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 great. If I make the Id overridable and also specify the attributes in the subclass, it still doesn't work.
I OnModelCreating
only do things because I am trying to get it to work. I feel like it should work without it. Of course it doesn't even work with that ...
The following query works great when I run it in SQL Management Studio:
insert into PartRevisions (originalid, revisiondate, revisionnumber, revisionby, partnumber, owner)
values (1, '1/1/2013', 1, 'eep', '123', 'ME')
Here's the gist with a complete program. I tried this on a test project because I assumed I would have problems with inheritance in EF (not done yet).
https://gist.github.com/eyston/4956444
Thank!
source to share
The table PartRevisions
should not contain a column Id
as an autogenerated identifier, but only a table Parts
for the base type. The two tables share primary keys. EF joins two tables when queried PartRevision
, and inserts rows into both tables if an object is PartRevision
inserted. Since both strings must have the same character Id
, there can only be an identity.
source to share
Alternatively, remove the automatic identification property from the "Id" column. Or alternatively, you can use Query / Store in your procedure. This will allow explicit Id values to be entered into your table even if auto-authentication is set on the column. (Although not always recommended, useful for fixes)
SET IDENTITY_INSERT dbo.YourTableName ON;
GO
Then after each table inserts:
SET IDENTITY_INSERT dbo.YourTableName OFF;
GO
source to share