How to create grandparent foreign key in EFCORE-CodePudding

I have an employee, with one hourly paying job, each hourly has multiple timecards. I would like the timecards to link to both the employee and Hourly.

public class Employee
{
    public int Id { get; set; }
}
public class Hourly
{
    public int EmployeeId { get; set; }
    public List<Timecard> Timecards{ get; set; }
}
public class Hourly
{
    public int HourlyId{ get; set; }
    public int EmployeeId { get; set; }
}

How do I specify this relationship in EF.

The code appears to set the employeeID but causes issues with the migration and the Hourly is now set to null.

  protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
       
        modelBuilder.Entity<Timecard>()
            .HasOne<HourlyPC>()
            .WithMany(pc => pc.Timecards)
            .HasForeignKey(t => t.EmployeeId)
            .HasPrincipalKey(pc => pc.EmployeeId);
    }

CodePudding user response：

Violates 3NF, meaning duplicated data that can lead to problems such as data anomalies. One hack would be to include the Employee FK in a composite PK for Job. That way, when a Timecard has a FK to Job, it also has a FK to Employee. Perhaps you could use a job code for a second field for inclusion in the composite Job PK or reference another entity, an example of which is below where Position is the normalized details of a Job w/o employee specific data (e.g. hourly rate) and Job relates an Employee to a Position with the employee-specific details:

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

public class Job
{
    public int EmployeeId { get; set; }
    public Employee Employee { get; set; }

    public string PositionId { get; set; }
    public Position Position { get; set; }

    public ICollection<TimeCard> TimeCards { get; set; }

    public decimal HourlyRate { get; set; }
}

public class TimeCard
{
    public Id { get; set; }
    public int EmployeeId { get; set; }
    public Employee Employee { get; set; }

    public string PositionId { get; set; }

    public Job Job { get; set; }
}

Config:

// configure Job
// configure relationshipt to Position
modelBuilder.Entity<Job>()
    .HasOne(j => j.Position)
    .WithMany()
    .IsRequired();
// configure relationship to Employee
modelBuilder.Entity<Job>()
    .HasOne(j => j.Employee)
    .WithMany()
    .IsRequired();

// create composite PK using the two FK's
modelBuilder.Entity<Job>()
    .HasKey(j => new { j.EmployeeId, j.PositionId });

// configure TimeCard
// configure nav prop to Employee
modelBuilder.Entity<TimeCard>()
    .HasOne(tc => tc.Employee);

// configure relationship with Job
modelBuilder.Entity<TimeCard>()
    .HasOne(tc => tc.Job)
    .WithMany(j => j.TimeCards)
    .HasForeignKey(tc => new { tc.EmployeeId, tc.PositionId })
    .IsRequired();

That might need a bit of tweaking but that's the nuts and bolts of it.