Understanding Entity framework's Many to Many relationships. Why does my code crash once I try to add more data to it?

asp.net-core c# entity-framework-core many-to-many

Question

I am trying to create a join table that combines the Employee and Tractor table to record each time an Employee is assigned/unassigned a truck. I am able to record an initial employeeID and truckId but the code crashes once I try to record second of employeeID and truckID on a different time and Day. Thus, the Datetime object is always unique.

This is the error it shows:

SqlException: Violation of PRIMARY KEY constraint 'PK_DriverTractorsAssignmentHistory'. Cannot insert duplicate key in object 'dbo.DriverTractorsAssignmentHistory'. The duplicate key value is (1, 2). The statement has been terminated.

Using a Many to many relationship was the one solution I could think of to capture each time an employee is assigned a truck. Pls show me a better solution if you have one

 public class Employee
 { 
    public int EmployeeID { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }
    public string LastName { get; set; }

    public int? TractorID { get; set; }
    public virtual Tractor Tractor { get; set; }
    public virtual List<DriverTractorAssignmentHistory>DriverTractorAssignmentHistories { get; set; }
 }

public class Tractor
{
    public int TractorID { get; set; }
    public string TruckNumber {get; set;}
    public string Status { get; set; } 
    public virtual Employee Employee { get; set; }
    public virtual List<DriverTractorAssignmentHistory> DriverTractorAssignmentHistories { get; set; }

    public Tractor()
    {
        Status = "Available";
    }
}


public class TrailerOrderDbContext:DbContext
{
    public DbSet<Employee> Employees { get; set; } 
    public DbSet<DriverTractorAssignmentHistory> DriverTractorsAssignmentHistory { get; set; }
    public DbSet<Tractor> Tractors { get; set; }

    public TrailerOrderDbContext(DbContextOptions<TrailerOrderDbContext> options)
        : base(options)
    {
    }  

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<DriverTractorAssignmentHistory>().HasKey(co => new { co.EmployeeId, co.TractorId });

        modelBuilder.Entity<DriverTractorAssignmentHistory>()
        .HasOne(e => e.Driver)
        .WithMany(c => c.DriverTractorAssignmentHistories)
        .HasForeignKey(trac => trac.TractorId);

        modelBuilder.Entity<DriverTractorAssignmentHistory>()
        .HasOne(trac => trac.Tractor)
        .WithMany(c => c.DriverTractorAssignmentHistories)
        .HasForeignKey(e => e.EmployeeId);
 }

 }
1
1
2/11/2019 11:11:30 AM

Popular Answer

Remove this line of code in OnModelCreating(ModelBuilder modelBuilder)beause this line of code prevents duplicate data entry and prevents many-to-many mapping:

    modelBuilder.Entity<DriverTractorAssignmentHistory>()
        .HasKey(co => new { co.EmployeeId, co.TractorId });

Or modify as below if in case unique key is needed:

    modelBuilder.Entity<DriverTractorAssignmentHistory>()
      .HasKey(co => new { co.EmployeeId, co.TractorId, co.AssignTimestamp };
3
2/11/2019 11:53:50 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow