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 Adviser.Users {get;set;}`? — Aron, Aug 20 '13 at 02:21

score 1 · Answer 1 · edited May 23 '17 at 11:50

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

We are explicitly implementing the deletes in code right now - I thought there is a way to turn off the cascade from the map table (we have to have it)... so if I delete the advisor, it will cascade to the map table, but on the map table I can turn off the cascade (so it doesn't affect my User table). I think there is a way from what I've been reading but I can't figure out the syntax. — user2697832, Aug 20 '13 at 12:59
I don't think EF is creating cascades from the StudentAdvisorMap is it? The cascades should be from Student and from Advisor to the StudentAdvisorMap. Try changing your `WillCascadeOnDelete(true)` to `WillCascadeOnDelete(false)` — Colin, Aug 20 '13 at 13:13

score 1 · Answer 2 · answered Aug 22 '13 at 14:19

1

I figured this out with help from this link: http://blog.cdeutsch.com/2011/09/entity-framework-code-first-error-could.html As he says on the blog, it isn't very intuitive but here is the syntax that made it work:

modelBuilder.Entity<AdvisorStudentMap>()
            .HasRequired(u=>u.User)
            .WithMany(m=>m.AdvisorStudentMaps)
            .WillCascadeOnDelete(false);

answered Aug 22 '13 at 14:19

user2697832

31
4

.willCascadeOnDelete(false); worked for me. I'm doing a migration from a home grown account table into Identity in a complex model and one of the many-to-many relationships required the cascade method. – JQII Mar 23 '15 at 21:05

Entity Framework Cascade Delete with Multiple Column Key

2 Answers2