A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column:

Question

First, thanks for taking the time to read this. I'm having some difficulties with trying to update a database using EF. I've used this same approach before on another project, so I'm thinking the issue is perhaps in the database, but I'm just not seeing it. The database is a legacy db in SQL 2005, and the naming convention has a lot to be desired, therefore I've been mapping the inconsistent names to something more consistent via the HasColumnName method: as in.

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

I've compared my issue to the other similar ones and investigated to verify the foreign key relationships are pointed in the right direction and that the identity is only on the PK. When I do NOT have Data Generation Option explicitly set, the command fires against the database, but it fails b/c I'm inserting a 0 as my Key and option explicit is set to OFF. I can see this in SQL Profiler. When I add the option for Identity, I get the Referential constraint error.

I've lost 2 days on this and am no closer to reaching a solution and I'm pretty much at the end of my rope. As an aside, in my other project I didn't need to decorate my POCO's with the DatabaseGeneratedOption, yet I didn't get any error on inserts. I didn't know if this was a SQL 2005 thing, or if I'm missing something else...

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

The foreign keys:

    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]

score 3 · Answer 1 · answered Aug 01 '12 at 15:08

3

I had the same error. Eventually traced it to an incorrect relationship. I was linking between two primay keys at the database instead of a primary and a foreign.

answered Aug 01 '12 at 15:08

Echilon

10,064
33
131
217

James Fleming · Accepted Answer · 2012-03-07T16:55:48.437

It only took about another hour of head banging, but I came to a solution using a hint from another response. The short of it is that EntityFramework was confused about some of the relationships (or my description using fluent expressions was).

I finally bit the bullet and cleaned up some of the names of the columns in the database and made them 'conventional', i.e. tableName+Id removed my fluent expressions and it worked.

I also took out a composite key on one table, added an Id field and made the composite key a unique index instead.

I hope this helps someone else.

I also confirmed my suspicion that when using SQL Server 2008 the identity annotation was not needed. The database being used was 2005.

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]

score 0 · Answer 3 · answered Apr 19 '15 at 11:48

0

I had to manually remove a bad reference in the .edmx.

answered Apr 19 '15 at 11:48

Sarah

301
1
3
7

A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column:

3 Answers3

Linked