Btukfyl

I have three entities: Boss, Employee, and Instruction. Bosses have a set of Instructions they can issue (one-to-many relationship) and Bosses have a set of Employees (one-to-many relationship) they can give those instructions to. An instruction can be given to more than one Employee, so there is a many-to-many relationship between Employees and Instructions. So here's my entities:

public class Boss

{

    public int ID { get; set; }

    public string Name { get; set; }

    public ICollection<Instruction> Instructions { get; set; }

    public ICollection<Employee> Employees { get; set; }

}

public class Employee

{

    public int ID { get; set; }

    public int BossID { get; set; }

    public string Name { get; set; }

    public DateTime WorkTime { get; set; }

    public ICollection<Instruction> Instructions { get; set; }

}

public class Instruction

{

    public int ID { get; set; }

    public string Name { get; set; }

    public int BossID { get; set;}

    public ICollection<Employee> Employees { get; set; }

}

The goal is to write queries to get lists of a Boss's Employees, and also to see what Employees are assigned to each Instruction. An additional reason for modeling it this way is so that we can enforce uniqueness constraints so that, for example, we don't have the same Employee entered multiple times, and maybe we don't want a Boss to schedule multiple Employees to work at the same time. So here's the DbContext with those relationships mapped with Fluent API:

public class Model1 : DbContext

{

    public Model1() : base("name=Model1") { }

    public virtual DbSet<Boss> Bosses { get; set; }

    public virtual DbSet<Instruction> Instructions { get; set; }

    public virtual DbSet<Employee> Employees { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)

    {

        modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();

        modelBuilder.Entity<Employee>().HasMany<Instruction>(x => x.Instructions).WithMany(x => x.Employees);

        modelBuilder.Entity<Boss>().HasMany<Instruction>(x => x.Instructions);

        modelBuilder.Entity<Boss>().HasMany<Employee>(x =>x.Employees);



        modelBuilder.Entity<Employee>().Property(x => x.BossID)

            .HasColumnAnnotation(IndexAnnotation.AnnotationName,

            new IndexAnnotation(new IndexAttribute("OneEmployeeAtATime", 0) { IsUnique = true }));



        modelBuilder.Entity<Employee>().Property(x => x.WorkTime)

            .HasColumnAnnotation(IndexAnnotation.AnnotationName,

            new IndexAnnotation(new IndexAttribute("OneEmployeeAtATime", 1) { IsUnique = true }));



        base.OnModelCreating(modelBuilder);

    }

}

The line modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>(); is in there because otherwise I get a "multiple cascade paths" error. The behavior I want is when I delete a Boss it should delete his Employees and Instructions as well, but if I delete either an Instruction or an Employee it should not cascade. But when I run my test:

using (Model1 db = new Model1())

{

    Boss boss = db.Bosses.First();

    db.Bosses.Remove(boss);

    db.SaveChanges();

}

I get this error message:

System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.EmployeeInstructions_dbo.Employees_Employee_ID". The conflict occurred in database "Model1", table "dbo.EmployeeInstructions", column 'Employee_ID'.
The statement has been terminated.

Am I missing something in the Fluent API?