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