Entity Framework Cascade Delete with Multiple Column Key

Question

I have these models set up:

public class Advisor
    {
    public virtual int AdvisorId { get; set; }
    public virtual int UserId { get; set; }
    public User User { get; set; }

    public ICollection<AdvisorStudentMap> AdvisorStudentMaps { get; set; }
    }

public class AdvisorStudentMap
{
    [Required]
    public virtual int AdvisorId { get; set; }
    [Required]
    public virtual int UserId { get; set; }
}

public class User
{
    public virtual int UserId { get; set; }
    public virtual string UserName { get; set; }
    public virtual string FirstName { get; set; }
    public ICollection<AdvisorStudentMap> AdvisorStudentMaps { get; set; }
}

In my OnModelCreating I have:

modelBuilder.Entity<AdvisorStudentMap>()
    .HasKey(t => new {t.AdvisorId, t.UserId});

In my fluent api how do I set it up so that when I delete an advisor, it deletes the AdvisorStudentMap as well? I keep getting the error: Message=Introducing FOREIGN KEY constraint 'FK_dbo.AdvisorStudentMaps_dbo.Users_UserId' on table 'AdvisorStudentMaps' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors.

Update - also in OnModelCreating I have:

modelBuilder.Entity<Advisor>()
.HasRequired(t => t.AdvisorStudentMaps).WithMany().WillCascadeOnDelete(true);

With that I get the error 'Cascading foreign key 'FK_dbo.Advisors_dbo.AdvisorStudentMaps_AdvisorId_UserId' cannot be created where the referencing column 'Advisors.AdvisorId' is an identity column.

What is AdvisorStudentMap? It looks like a table? Have you tried just making public IEnumerable<User> Adviser.Users {get;set;}? — Aron

Colin Colin · Accepted Answer · 2013-08-20T08:20:55

You appear to be trying to model a many-to-many relationship between Students and Advisors. This would be the way you'd normally do that:

public class Advisor
 {
    //Key fields don't need to be marked virtual
    public int AdvisorId { get; set; }

    //If you want the property to lazy load then you should mark it virtual
    public virtual ICollection<Student> Students{ get; set; }

    //Advisors have a UserProfile
    public int UserProfileId{get;set;}
    public virtual UserProfile UserProfile {get; set;}    
}

public class Student
{
    public int StudentId { get; set; }
    public virtual ICollection<Advisor> Advisors { get; set; }

    //Students also have a UserProfile
    public int UserProfileId{get;set;}
    public virtual UserProfile UserProfile {get; set;}
}

public class UserProfile
{
   public int UserProfileId{get;set;}

   //NB not marked virtual - that is only needed on navigation properties when 
   //we want to use lazy loading
   public string UserName {get;set}
   public string FirstName {get;set}
}

Entity framework will automatically create the join table to model the relationship. You don't need the AdvisorStudentMap entity unless you need to add attributes to the relationship.

As for the cascade on delete problem. If you delete a User then this can cascade to the Student table and the Advisor table. There is a cascade path from Student to StudentAdvisorMap and another from Advisor to StudentAdvisorMap. Hence multiple cascade paths. Sql Server does not allow this. You will have to explicitly implement the deletes in your code to avoid this

Entity Framework Cascade Delete with Multiple Column Key

2 Answers