I am having trouble writing up EF code-first approach for a self-referencing table and its relations to other tables.
My WorkPackage table has a hierarchy (Work packages can contain smaller work packages), so it self-references itself. It has a one to many relationship with TimesheetRow. My code for that is:
public class WorkPackage
{
[Key]
public int WorkPackageId { get; set; }
[ForeignKey("ProjectId")]
public int ProjectId { get; set; }
public string WorkPackageName { get; set; }
public string WorkPackageDescription { get; set; }
public double WorkPackageCost { get; set; }
public IList<EmployeeWorkPackageAssignment> EmployeeWorkPackageAssignments { get; set; }
public Project Project { get; set; }
//Self-Reference Relationship
public int? ParentWorkPackageId { get; set; }
public virtual WorkPackage ParentWorkPackage { get; set; }
public virtual IList<WorkPackage> ChildrenWorkPackages { get; set; }
public IList<TimesheetRow> TimesheetRows { get; set; }
}
My TimesheetRow table has a many to one relation with WorkPackage, and I have designed the class as follows:
public class TimesheetRow
{
public int TimesheetRowId { get; set; }
public int TimesheetId { get; set; }
public int WorkPackageId { get; set; }
public int ProjectId { get; set; }
public int Hours{get; set;}
public Timesheet Timesheet { get; set; }
public WorkPackage WorkPackage { get; set; }
}
Timesheet Class for reference:
public class Timesheet
{
public int TimesheetId { get; set; }
public int EmployeeId { get; set; }
public int WeekNumber { get; set; }
public Employee Employee { get; set; }
public IList<TimesheetRow> TimesheetRows { get; set; }
}
This is how I have setup the relations in OnModelCreating. I have omitted irrelevant code.
modelBuilder.Entity<WorkPackage>()
.HasOne(wp => wp.ParentWorkPackage)
.WithMany(wp => wp.ChildrenWorkPackages)
.HasForeignKey(wp => wp.ParentWorkPackageId)
.OnDelete(DeleteBehavior.NoAction);
modelBuilder.Entity<TimesheetRow>()
.HasOne(tr => tr.Timesheet)
.WithMany(tr => tr.TimesheetRows)
.HasForeignKey(tr => new { tr.TimesheetId})
.OnDelete(DeleteBehavior.NoAction);
modelBuilder.Entity<TimesheetRow>()
.HasOne(tr => tr.WorkPackage)
.WithMany(tr => tr.TimesheetRows)
.HasForeignKey(tr => tr.WorkPackageId)
.OnDelete(DeleteBehavior.NoAction);
After creating the migration, I get an error when I update-database, saying Introducing FOREIGN KEY constraint 'FK_TimesheetRows_WorkPackages_WorkPackageId' on table 'TimesheetRows' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
I looked around SO and saw related topics (such as Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why? ) but still have no idea how to fix this. Any solution to this problem would be greatly appreciated, thanks!
If more details about the code is required, please let me know and I will add them in!