我使用的是Microsoft SQL Server 2008 R2，并有三个相关的表：ConvertCarbs，Countries和StateProvinces
使用EF Power Tools我有Reverse Engineered the Database（ http://msdn.microsoft.com/en-us/data/jj593170.aspx ），并尝试使用Entity Framework将记录插入ConvertCarb表。
但是，程序在尝试保存更改时抛出以下错误：
当IDENTITY_INSERT设置为OFF时，无法在表'ConvertCarb'中为标识列插入显式值。
这是运行时代码：
var convertCarb = new ConvertCarb(); convertCarb.CountryID = 150; db.ConvertCarbs.Add(convertCarb); db.SaveChanges();以下是域模型中的类：
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; } }执行此插入应该没有问题，因为ConvertCarbID是一个标识列，我已通过直接在数据库上运行tsql插件来验证这一点。
以下是数据库中表的创建代码：
/****** 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以下是EF工具生成的映射类：
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"); } }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 GOHere 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"); } }最满意答案
这种关系映射......
this.HasRequired(t => t.StateProvince) .WithOptional(t => t.ConvertCarb);...表示StateProvince是主体， ConvertCarb依赖于共享主键的一对一关系。 在这种情况下，EF假定只有主体可以具有标识主键，而不是依赖的ConvertCarb （因为从属设备必须始终具有与主体相同的PK值）。 基本上，此关系会禁用ConvertCarb实体的标识约定。 因此，EF将ConvertCarb实体的PK值（无论是手动提供还是默认值）发送到数据库（ ConvertCarbID列是INSERT语句的一部分），这会抛出数据库异常，因为ConvertCarbID列被标记为identity。
但是我不知道为什么EF从数据库中获取了这种一对一的关系，因为我没有在数据库脚本中看到对StateProvince的FK约束。 或者稍后将该约束添加到数据库中？
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?
更多推荐
发布评论