1

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

 }
user8964654
  • 87
  • 1
  • 9
  • Create a DateTime field on DriverTractorAssignmentHistory and make it part of the key, so the combination of the three fields should be unique when inserting. – jpgrassi Feb 11 '19 at 11:41
  • Can you show us the code for the `DriverTractorAssignmentHistory` class? – jpgrassi Feb 11 '19 at 11:47

1 Answers1

3

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 };
Bijay Koirala
  • 242
  • 2
  • 10
  • While this solves the error, I don't think it's a good answer. OP should define a unique key, meaning it should also contain the datetime of when this was added – jpgrassi Feb 11 '19 at 11:40
  • For making unique key you may refer this link `https://stackoverflow.com/questions/18889218/unique-key-constraints-for-multiple-columns-in-entity-framework`. But in this case the `unique key` should be combination of more than one column. My recommendation would be to put `EmployeeId` and TractorId ` as unique key. – Bijay Koirala Feb 11 '19 at 11:43
  • 1
    EmployeeId and TractorId already have to be unique in `DriverTractorAssignmentHistory` since they are part of the key, but they aren't unique enough since OP can assign/de-assign a employee of a tractor multiple times a day, so it will fail. What he needs is another field to make it unique. So `.HasKey(co => new { co.EmployeeId, co.TractorId, co.AssignTimestamp } – jpgrassi Feb 11 '19 at 11:46
  • @jpgrassi Thanks. Modified my answer as per your suggestion. – Bijay Koirala Feb 11 '19 at 11:54