c# - ReferentialConstraint 中的依赖属性映射到存储生成的列。列 :

Question

首先，感谢您花时间阅读本文。我在尝试使用 EF 更新数据库时遇到了一些困难。我之前在另一个项目中使用过同样的方法，所以我认为问题可能出在数据库中，但我只是没有看到它。该数据库是 SQL 2005 中的遗留数据库，命名约定有很多不足之处，因此我一直在通过 HasColumnName 方法将不一致的名称映射到更一致的名称:如中所示。

        modelBuilder.Entity<Case_Person_1>().ToTable("dbo.Case_Person_1");
        modelBuilder.Entity<Case_Person_1>().Property(c => c.Id).HasColumnName("CaseNumber");

我已将我的问题与其他类似问题进行比较，并进行调查以验证外键关系是否指向正确的方向，并且身份仅在 PK 上。当我没有显式设置数据生成选项时，该命令将针对数据库触发，但它失败了 b/c 我正在插入一个 0 作为我的 key 并且显式选项设置为 OFF。我可以在 SQL Profiler 中看到这一点。当我添加身份选项时，我收到引用约束错误。

我已经为此浪费了 2 天的时间，并且离找到解决方案还差得很远，而且我几乎已经筋疲力尽了。 顺便说一句，在我的其他项目中，我不需要用 DatabaseGeneratedOption 装饰我的 POCO，但我没有在插入时遇到任何错误。我不知道这是 SQL 2005 的问题，还是我遗漏了什么...

---

the full error detail is here: System.Data.Entity.Infrastructure.DbUpdateException was caught
Message=An error occurred while updating the entries. See the inner exception for details. Source=EntityFramework StackTrace: at System.Data.Entity.Internal.InternalContext.SaveChanges() at System.Data.Entity.Internal.LazyInternalContext.SaveChanges() at System.Data.Entity.DbContext.SaveChanges() at Scyfis.Ocyf.DataLayer.EntityExtensions.AddItem[T](T entity, DbSet1 dbSet, DbContext context, Action2 foriegnKeyUpdater) in C:\TFS\CGProjects\GOC\SCYFIS\PHTSY\Dev\src\Datalayer\Helpers\EntityExtensions.cs:line 83 at Scyfis.Ocyf.DataLayer.EntityExtensions.SaveItem[T](T entity, DbSet1 dbSet, DbContext context, Action2 foriegnKeyUpdater) in C:\TFS\CGProjects\GOC\SCYFIS\PHTSY\Dev\src\Datalayer\Helpers\EntityExtensions.cs:line 27 at Scyfis.Ocyf.DataLayer.Case_PHTSYRepository.Save(Case_PHTSY model) in C:\TFS\CGProjects\GOC\SCYFIS\PHTSY\Dev\src\Datalayer\Custom\Case_PHTSYRepository.cs:line 44 at Scyfis.Ocyf.Service.Case_PHTSYService.Create(Case_PHTSY model) in C:\TFS\CGProjects\GOC\SCYFIS\PHTSY\Dev\src\Service\Custom\Case_PHTSYService.cs:line 184 InnerException: System.Data.UpdateException Message=An error occurred while updating the entries. See the inner exception for details. Source=System.Data.Entity StackTrace: at System.Data.Mapping.Update.Internal.TableChangeProcessor.CompileCommands(ChangeNode changeNode, UpdateCompiler compiler) at System.Data.Mapping.Update.Internal.UpdateTranslator.d_0.MoveNext() at System.Linq.Enumerable.d_711.MoveNext()
at System.Data.Mapping.Update.Internal.UpdateCommandOrderer..ctor(IEnumerable1 commands, UpdateTranslator translator) at System.Data.Mapping.Update.Internal.UpdateTranslator.ProduceCommands() at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache) at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options) at System.Data.Entity.Internal.InternalContext.SaveChanges() InnerException: System.InvalidOperationException Message=A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'Id'. Source=System.Data.Entity StackTrace: at System.Data.Mapping.Update.Internal.UpdateCompiler.BuildSetClauses(DbExpressionBinding target, PropagatorResult row, PropagatorResult originalRow, TableChangeProcessor processor, Boolean insertMode, Dictionary`2& outputIdentifiers, DbExpression& returning, Boolean& rowMustBeTouched) at System.Data.Mapping.Update.Internal.UpdateCompiler.BuildInsertCommand(PropagatorResult newRow, TableChangeProcessor processor) at System.Data.Mapping.Update.Internal.TableChangeProcessor.CompileCommands(ChangeNode changeNode, UpdateCompiler compiler) InnerException:

public partial class Case_PHTSY
{
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }                 
    public int? FamilyHistoryID { get; set; } 
    public int? PersonId { get; set; }              
    public DateTime? ReportDate { get; set; }       
    public string PhysicianName { get; set; }       
    public string MRNumber { get; set; }
    public virtual  F_FamilyHistory F_FamilyHistory { get; set; }
    public virtual  Person Person { get; set; }

    public virtual ICollection<CP_ActualDischarge> CP_ActualDischarges { get; set; }
    public virtual ICollection<CP_Barrier> Barriers { get; set; }
    public virtual ICollection<CP_AgencyContact> AgencyContacts { get; set; }
    public virtual ICollection<CP_DischargeActivity> DischargeActivities { get; set; }
    public virtual ICollection<C_Role> Roles { get; set; }
    public virtual ICollection<Case_Person_RD1> Case_Person_RD1s { get; set; }
    public virtual ICollection<PHTSY_CensusCode> PHTSY_CensusCodes { get; set; }     
}

public class CP_ActualDischarge
{
    public int Id { get; set; }

    [ForeignKey("Case_PHTSY")]
    public int Case_PHTSYId{ get; set; }

    ...

    public virtual Case_PHTSY Case_PHTSY { get; set; }
}

public partial class CP_Barrier 
{

    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; } 

    public int? Case_PHTSYId { get; set; }

   ...

    public virtual  Case_PHTSY Case_PHTSY { get; set; }

}


public partial class CP_AgencyContact 
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; } 

    public int Case_PHTSYId { get; set; }               
    ...

    public virtual  Case_PHTSY Case_PHTSY { get; set; }

}

public partial class CP_DischargeActivity 
{

    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; } 

    public int? Case_PHTSYId { get; set; }

    ...             

    public virtual  Case_PHTSY Case_PHTSY { get; set; }

}

 public partial class C_Role
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; } 

    public int? Case_PHTSYId{ get; set; }
    public virtual Case_PHTSY Case_PHTSY { get; set; }
}

 public partial class Case_Person_RD1
{
    public virtual C_Role C_Role { get; set; }

    public virtual Case_PHTSY Case_PHTSY { get; set; }
}

外键:

    ALTER TABLE [dbo].[CP_ActualDischarge]  WITH NOCHECK ADD  CONSTRAINT         [FK_CP_ActualDischarge_Case_PHTSY] FOREIGN KEY([Case_PHTSYId])
    REFERENCES [dbo].[Case_PHTSY] ([Id])
    ON DELETE CASCADE
    NOT FOR REPLICATION 
    GO
    ALTER TABLE [dbo].[CP_ActualDischarge] CHECK CONSTRAINT [FK_CP_ActualDischarge_Case_PHTSY]

    ALTER TABLE [dbo].[CP_AgencyContacts]  WITH NOCHECK ADD  CONSTRAINT         [FK_CP_AgencyContacts_Case_PHTSY] FOREIGN KEY([Case_PHTSYId])
    REFERENCES [dbo].[Case_PHTSY] ([Id])
    ON DELETE CASCADE
    NOT FOR REPLICATION 
    GO
ALTER TABLE [dbo].[CP_AgencyContacts] CHECK CONSTRAINT [FK_CP_AgencyContacts_Case_PHTSY]

    ALTER TABLE [dbo].[CP_Barriers]  WITH NOCHECK ADD  CONSTRAINT     [FK_CP_Barriers_Case_PHTSY] FOREIGN KEY([Case_PHTSYId])
REFERENCES [dbo].[Case_PHTSY] ([Id])
ON DELETE CASCADE
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[CP_Barriers] CHECK CONSTRAINT [FK_CP_Barriers_Case_PHTSY]

ALTER TABLE [dbo].[CP_DischargeActivities]  WITH NOCHECK ADD  CONSTRAINT [FK_CP_DischargeActivities_Case_PHTSY] FOREIGN KEY([Case_PHTSYId])
REFERENCES [dbo].[Case_PHTSY] ([Id])
ON DELETE CASCADE
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[CP_DischargeActivities] CHECK CONSTRAINT [FK_CP_DischargeActivities_Case_PHTSY]
GO


        modelBuilder.Entity<Case_PHTSY>().HasMany(c => c.AgencyContacts).WithOptional().HasForeignKey(k => k.Case_PHTSYId);
            modelBuilder.Entity<Case_PHTSY>().HasMany(c => c.Barriers).WithOptional().HasForeignKey(k => k.Case_PHTSYId);
            modelBuilder.Entity<Case_PHTSY>().HasMany(r => r.DischargeActivities).WithOptional().HasForeignKey(k => k.Case_PhstsyId);
            modelBuilder.Entity<Case_PHTSY>().HasMany(c => c.Roles).WithOptional().HasForeignKey(k => k.Case_PHTSYId);
            modelBuilder.Entity<Case_PHTSY>().HasMany(m => m.Case_Person_RD1s).WithOptional().HasForeignKey(k => k.Case_PhtsyId);
            modelBuilder.Entity<Case_PHTSY>().HasMany(o => o.DischargeActivities).WithOptional().HasForeignKey(k => k.Case_PhstsyId);
            modelBuilder.Entity<Case_PHTSY>().HasOptional(o => o.F_FamilyHistory).WithRequired();
            modelBuilder.Entity<Case_PHTSY>().HasMany(o => o.PHTSY_CensusCodes).WithOptional().HasForeignKey(k => k.Case_PHTSYId);



the primary table: 
CREATE TABLE [dbo].[Case_PHTSY](
    [Id] [int] IDENTITY(5000,1) NOT FOR REPLICATION NOT NULL,
    [FamilyHistoryID] [int] NULL,   
    [IntensityNHours] [smallint] NULL,
    [IEPRequested] [bit] NULL CONSTRAINT [DF_Case_PHTSY_IEPRequested]  DEFAULT (0),
    ...
    [Kidnet] [bit] NULL,
 CONSTRAINT [PK_Case_PHTSY] PRIMARY KEY CLUSTERED 
(
    [Id] 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
ALTER TABLE [dbo].[Case_PHTSY]  WITH NOCHECK ADD  CONSTRAINT [FK_Case_PHTSY_F_FamilyHistory] FOREIGN KEY([FamilyHistoryID])
REFERENCES [dbo].[F_FamilyHistory] ([FamilyHistoryID])
ON DELETE CASCADE
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[Case_PHTSY] CHECK CONSTRAINT [FK_Case_PHTSY_F_FamilyHistory]
GO
ALTER TABLE [dbo].[Case_PHTSY]  WITH CHECK ADD  CONSTRAINT [FK_Case_PHTSY_Persons] FOREIGN KEY([CaseChildID])
REFERENCES [dbo].[Persons] ([PersonID])
GO
ALTER TABLE [dbo].[Case_PHTSY] CHECK CONSTRAINT [FK_Case_PHTSY_Persons]

CREATE TABLE [dbo].[CP_ActualDischarge](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Case_PHTSYId] [int] NOT NULL,
    [DischargeDate] [datetime] NULL,

 CONSTRAINT [PK_CP_ActualDischarge] PRIMARY KEY CLUSTERED 
(
    [Id] 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
ALTER TABLE [dbo].[CP_ActualDischarge]  WITH NOCHECK ADD  CONSTRAINT [FK_CP_ActualDischarge_Case_PHTSY] FOREIGN KEY([Case_PHTSYId])
REFERENCES [dbo].[Case_PHTSY] ([Id])
ON DELETE CASCADE
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[CP_ActualDischarge] CHECK CONSTRAINT [FK_CP_ActualDischarge_Case_PHTSY]

CREATE TABLE [dbo].[CP_AgencyContacts](
    [AgencyContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Case_PHTSYId] [int] NOT NULL,
    [AgencyType] [int] NULL,
    [Agency] [int] NULL,
 CONSTRAINT [PK_Case_PHTSY_AgencyContacts] PRIMARY KEY CLUSTERED 
(
    [AgencyContactID] 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
ALTER TABLE [dbo].[CP_AgencyContacts]  WITH NOCHECK ADD  CONSTRAINT [FK_CP_AgencyContacts_Case_PHTSY] FOREIGN KEY([Case_PHTSYId])
REFERENCES [dbo].[Case_PHTSY] ([Id])
ON DELETE CASCADE
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[CP_AgencyContacts] CHECK CONSTRAINT [FK_CP_AgencyContacts_Case_PHTSY]

Answer 1

我有同样的错误。最终追溯到一段不正确的关系。我在数据库中的两个主键之间进行链接，而不是在主键和外部键之间进行链接。