Entity Framework Throwing Cannot Insert Explicit Value for Identity Column In Table ... When IDENTITY_INSERT is set to OFF Error

Question

I am using Microsoft SQL Server 2008 R2 and have three relevant tables: ConvertCarbs, Countries and StateProvinces

Using the EF Power Tools I have Reverse Engineered the Database (http://msdn.microsoft.com/en-us/data/jj593170.aspx) and am trying to insert a record into the ConvertCarb table using the Entity Framework.

But, the program is throwing the following error when trying to save the changes:

Cannot insert explicit value for identity column in table 'ConvertCarb' when IDENTITY_INSERT is set to OFF.

Here is the run time code:

var convertCarb = new ConvertCarb();
convertCarb.CountryID = 150;
db.ConvertCarbs.Add(convertCarb);
db.SaveChanges();

Here are the classes from the domain model:

public partial class ConvertCarb
{
    public int ConvertCarbID { get; set; }
    public Nullable<int> CountryID { get; set; }
    public virtual Country Country { get; set; }
}

public partial class Country
{
    public Country()
    {
        this.ConvertCarbs = new List<ConvertCarb>();
        this.Offices = new List<Office>();
    }

    public int CountryID { get; set; }
    public string CountryName { get; set; }
    public virtual ICollection<ConvertCarb> ConvertCarbs { get; set; }
}

public partial class StateProvince
{
    public StateProvince()
    {
        this.Offices = new List<Office>();
    }

    public int StateProvID { get; set; }
    public string StateProvAbbr { get; set; }
    public virtual ConvertCarb ConvertCarb { get; set; }
    public virtual ICollection<Office> Offices { get; set; }
}

There should be no problem doing this insert since the ConvertCarbID is an Identity Column and I have verified this by running a tsql insert on the database directly.

Here is the create code for the tables in the database:

/****** Object:  Table [dbo].[ConvertCarb]    Script Date: 04/11/2014 03:04:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ConvertCarb](
    [ConvertCarbID] [int] IDENTITY(1,1) NOT NULL,
    [CountryID] [int] NULL,
    [StateProvID] [int] NULL,
    [KWH_FT2] [float] NULL,
    [G_KWH] [decimal](18, 4) NULL,
    [NatGas_GJ_M2] [float] NULL,
    [FuelOil_GJ_M2] [float] NULL,
 CONSTRAINT [PK_ConvertCarb] PRIMARY KEY CLUSTERED 
(
    [ConvertCarbID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ConvertCarb]  WITH NOCHECK ADD  CONSTRAINT [FK_ConvertCarb_Countries] FOREIGN KEY([CountryID])
REFERENCES [dbo].[Countries] ([CountryID])
GO

ALTER TABLE [dbo].[ConvertCarb] NOCHECK CONSTRAINT [FK_ConvertCarb_Countries]
GO

ALTER TABLE [dbo].[ConvertCarb]  WITH NOCHECK ADD  CONSTRAINT [FK_ConvertCarb_StateProvinces] FOREIGN KEY([ConvertCarbID])
REFERENCES [dbo].[StateProvinces] ([StateProvID])
GO

ALTER TABLE [dbo].[ConvertCarb] NOCHECK CONSTRAINT [FK_ConvertCarb_StateProvinces]
GO


/****** Object:  Table [dbo].[Countries]    Script Date: 04/11/2014 03:04:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Countries](
    [CountryID] [int] IDENTITY(1,1) NOT NULL,
    [CountryName] [varchar](255) NULL,
 CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED 
(
    [CountryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO




/****** Object:  Table [dbo].[StateProvinces]    Script Date: 04/11/2014 03:05:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[StateProvinces](
    [StateProvID] [int] IDENTITY(1,1) NOT NULL,
    [StateProvAbbr] [varchar](2) NULL,
 CONSTRAINT [PK_StateProvinces] PRIMARY KEY CLUSTERED 
(
    [StateProvID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Here are the mapping classes generated by the EF tool:

public class ConvertCarbMap : EntityTypeConfiguration<ConvertCarb>
{
    public ConvertCarbMap()
    {
        // Primary Key
        this.HasKey(t => t.ConvertCarbID);

        // Properties
        // Table & Column Mappings
        this.ToTable("ConvertCarb");
        this.Property(t => t.ConvertCarbID).HasColumnName("ConvertCarbID");
        this.Property(t => t.CountryID).HasColumnName("CountryID");
        this.Property(t => t.StateProvID).HasColumnName("StateProvID");
        this.Property(t => t.KWH_FT2).HasColumnName("KWH_FT2");
        this.Property(t => t.G_KWH).HasColumnName("G_KWH");
        this.Property(t => t.NatGas_GJ_M2).HasColumnName("NatGas_GJ_M2");
        this.Property(t => t.FuelOil_GJ_M2).HasColumnName("FuelOil_GJ_M2");

        // Relationships
        this.HasOptional(t => t.Country)
            .WithMany(t => t.ConvertCarbs)
            .HasForeignKey(d => d.CountryID);
        this.HasRequired(t => t.StateProvince)
            .WithOptional(t => t.ConvertCarb);

    }
}





public class CountryMap : EntityTypeConfiguration<Country>
{
    public CountryMap()
    {
        // Primary Key
        this.HasKey(t => t.CountryID);

        // Properties
        this.Property(t => t.CountryName)
            .HasMaxLength(255);

        // Table & Column Mappings
        this.ToTable("Countries");
        this.Property(t => t.CountryID).HasColumnName("CountryID");
        this.Property(t => t.CountryName).HasColumnName("CountryName");
    }
}


public class StateProvinceMap : EntityTypeConfiguration<StateProvince>
{
    public StateProvinceMap()
    {
        // Primary Key
        this.HasKey(t => t.StateProvID);

        // Properties
        this.Property(t => t.StateProvAbbr)
            .HasMaxLength(2);

        // Table & Column Mappings
        this.ToTable("StateProvinces");
        this.Property(t => t.StateProvID).HasColumnName("StateProvID");
        this.Property(t => t.StateProvAbbr).HasColumnName("StateProvAbbr");
    }
}

Having not worked with entity-framework I would say `public int ConvertCarbID { get; set; }` is default `0` instead of `null`. Have you tried making it `nullable` too — juergen d, Apr 10 '14 at 14:54
When you look at the properties of the `ConvertCarbId` field in the .edmx model does it have the `StoreGenerated` property set to `Identity`? — paul, Apr 10 '14 at 14:55
Thank you for the comment. ConvertCarbID is a primary key. It cannot be null. — ADH, Apr 10 '14 at 14:56
@paul Thank you for your comment. There is no .edmx model. I reverse engineered a SQL Server database. — ADH, Apr 10 '14 at 14:57
@Erik Philips - DbUpdateException was unhandled by the user code — ADH, Apr 10 '14 at 15:05
Your convertcarb class does not specify that the primary key is an identity, therefore, when instantiating the class, it's going to default that id to 0, and when you try to save, it's going to try and insert 0 as well. — ganders, Apr 10 '14 at 15:05
When you look at the properties of the `ConvertCarbId` field in the .xsd model does it have the `AutoIncrement` property set to `true`? — paul, Apr 10 '14 at 15:05
By that logic, I would not be able to add a Country, but that functionality works fine. — ADH, Apr 10 '14 at 15:06
@ganders wrong. If the property name is className + Id, the convention states that it's a primary key / identity column in db. — Raphaël Althaus, Apr 10 '14 at 15:06
@paul - I do not know exactl what you mean by .xsd model, but when I go to SQL Server Mgt Studio and right click the table and choose design, then select the ConvertCarbID column, the Identity Specification is Yes, the (Is Identity) is Yes, Indentity Increment = 1 and Identity Seed = 1 — ADH, Apr 10 '14 at 15:09
When you say that you 'reverse engineered' the database, what exactly do you mean? I would have thought you'd have ended up with an .edmx (Entity Framework) or an .xsd (DataSet) — paul, Apr 10 '14 at 15:11
@paul - http://msdn.microsoft.com/en-us/data/jj593170.aspx See "Reverse Engineer Code First". — ADH, Apr 10 '14 at 15:12
And you don't have anohter partial class ConvertCarb, with an empty ctor doing something with ConvertCarbID ? — Raphaël Althaus, Apr 10 '14 at 15:19
Ok. In that case you could try adding something like `this.Property(c => c.ConvertCarbId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)` to your `ConvertCarb` class — paul, Apr 10 '14 at 15:19
I updated the question with the ConvertCarbMap class generated by the VS Power Tool. — ADH, Apr 10 '14 at 15:21
@AllanHorwitz when you debug, does `convertCarb` have a `ConvertCarbId` <> 0 on this line : `db.ConvertCarbs.Add(convertCarb)`; — Raphaël Althaus, Apr 10 '14 at 15:26
Yes, I just noticed that, it says 1, but there is already an existing record with 1 in it. — ADH, Apr 10 '14 at 15:28
try adding the `HasDatabaseGeneratedOption` line to that `ConvertCarbMap` class — paul, Apr 10 '14 at 15:29
@paul - That change throws a new error: A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'ConvertCarbID'. — ADH, Apr 10 '14 at 15:31
@AllanHorwitz so somewhere in your Code, something is setting 1 in ConvertCarbID. This should not happen. Can you try to debug these four lines using F11, and see where this 1 is setted ? — Raphaël Althaus, Apr 10 '14 at 15:34
It changes to 1 one this line: db.ConvertCarbs.Add(convertCarb); — ADH, Apr 10 '14 at 15:37
Actually, I had added a line for "convertCarb.StateProvince = db.StateProvinces.FirstOrDefault(m => m.StateProvID == 1);" and that was changing it to 1 on the Add line, not before. Then I removed that StateProv line of code and it says zero. — ADH, Apr 10 '14 at 15:42
I have a meeting right now, I think the problem is in the Relationship settings with the StateProvinces being Required. I will mess with the Relationship settings and re-reverse engineer. Thanks for everyone's help so far. — ADH, Apr 10 '14 at 15:45
Well, it seems that StateProc can only have one ConvertCarb (or no ConvertCarb). And it seems that StateProc 1 has already a ConvertCarb. So you can't add a new StateProc with this ConvertCarb. — Raphaël Althaus, Apr 10 '14 at 15:48
I removed the foreign key relationships from the database, ran the reverse engineering tool again and the error went away. It seems like there might be a bug with the Visual Studio Power Tools Reverse Engineering tool. — ADH, Apr 10 '14 at 18:28
possible duplicate of [Cannot insert explicit value for identity column in table 'table' when IDENTITY\_INSERT is set to OFF](http://stackoverflow.com/questions/1334012/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity) — Mark Rotteveel, Apr 11 '14 at 07:09
@MarkRotteveel - This is not a duplicate of that post. I am using Entity Framework, not T-SQL. — ADH, Apr 11 '14 at 11:56

score 6 · Accepted Answer · answered Apr 10 '14 at 20:45

This relationship mapping...

this.HasRequired(t => t.StateProvince)
    .WithOptional(t => t.ConvertCarb);

...means that StateProvince is the principal and ConvertCarb the dependent in a shared primary key one-to-one relationship. In that case EF assumes that only the principal can have an identity primary key, not the dependent ConvertCarb (because the dependent must always have the same PK value as the principal). Basically this relationship disables the identity convention for the ConvertCarb entity. As a result EF sends the PK value (no matter if manually supplied or the default) of the ConvertCarb entity to the database (the ConvertCarbID column is part of the INSERT statement) which throws the database exception because the ConvertCarbID column is marked as identity.

However I don't know why EF picked up that one-to-one relationship from the database since I don't see a FK constraint to StateProvince in your database script. Or has that constraint been added later to the database perhaps?

Thank you for your response. I left that code out actually. I was trying to post only the relevant code, but as it turned out that was the most relevant code. I will add it to the original post right now. — ADH, Apr 11 '14 at 06:04
The foreign key relationship was incorrectly mapped to the ConvertCarbID rather than the StateProvID. — ADH, Apr 11 '14 at 14:52
This is the kind of insightful answer that gives SO its reputation — tobiak777, Jun 24 '15 at 09:48

score 0 · Answer 2 · edited Jun 02 '14 at 20:49

public class ConvertCarbMap : EntityTypeConfiguration<ConvertCarb>
{
public ConvertCarbMap()
{
    // Primary Key
    this.HasKey(t => t.ConvertCarbID);

    // Properties
    this.HasKey(t => t.ConvertCarbID)
        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

    // Table & Column Mappings
    this.ToTable("ConvertCarb");
    this.Property(t => t.ConvertCarbID).HasColumnName("ConvertCarbID");
    this.Property(t => t.CountryID).HasColumnName("CountryID");
    this.Property(t => t.StateProvID).HasColumnName("StateProvID");
    this.Property(t => t.KWH_FT2).HasColumnName("KWH_FT2");
    this.Property(t => t.G_KWH).HasColumnName("G_KWH");
    this.Property(t => t.NatGas_GJ_M2).HasColumnName("NatGas_GJ_M2");
    this.Property(t => t.FuelOil_GJ_M2).HasColumnName("FuelOil_GJ_M2");

    // Relationships
    this.HasOptional(t => t.Country)
        .WithMany(t => t.ConvertCarbs)
        .HasForeignKey(d => d.CountryID);
    this.HasRequired(t => t.StateProvince)
        .WithOptional(t => t.ConvertCarb);

}
}

Use DatabaseGeneratedOption.Identity in HasDatabaseGeneratedOption.

Entity Framework Throwing Cannot Insert Explicit Value for Identity Column In Table ... When IDENTITY_INSERT is set to OFF Error

2 Answers2