Entity Framework Code First One To Many Cascade Error

Question

I have 4 model class in my Asp.Net Mvc project. I'm using Entity Framework Code First (Automatic Migration). I when run command update-database, I get following error;

Introducing FOREIGN KEY constraint 'FK_dbo.Tickets_dbo.Users_CreatedUserId' on table 'Tickets' 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 or index. See previous errors.

What is the problem?

//Company Class
public class Company
{
    public int Id { get; set; }

    [Required, StringLength(100)]
    public string Title { get; set; }

    public virtual ICollection<User> Users { get; set; } = new HashSet<User>();
}

//User Class
public class User
{
    public int Id { get; set; }

    [Required, StringLength(50)]
    public string UserName { get; set; }

    [Required, StringLength(100)]
    public string FullName { get; set; }

    public int CompanyId { get; set; }
    public virtual Company Company { get; set; }
}

//Ticket Class
public class Ticket
{
    public int Id { get; set; }

    [Required]
    public DateTime CreationDate { get; set; }

    [Required, StringLength(100)]
    public string Title { get; set; }

    public bool IsClosed { get; set; }

    public int CreatedUserId { get; set; }
    public virtual User CreatedUser { get; set; }

    public virtual ICollection<TicketMessage> Messages { get; set; } = new HashSet<TicketMessage>();
}

//TicketMessage Class
public class TicketMessage
{
    public int Id { get; set; }

    public DateTime Date { get; set; }

    [Required]
    public string Message { get; set; }

    public int UserId { get; set; }
    public virtual User User { get; set; }

    public int TicketId { get; set; }
    public virtual Ticket Ticket { get; set; }

}

Answer 1

If you delete a record in one model (the parent) it will delete all records in the child model relating to it. In this instance the Users model is the parent that has many Tickets and TicketMessages so when you delete a user cascade deletion would delete all of the users Tickets and TicketMessages also. When a model has a navigation property, EF automatically assigns a foreign key. Foreign keys create relationships between models and mean that you can't delete a record in one model when a record in a 'child' model is dependent on the foreign key present as the primary key in the record you are attempting to delete.

In your situation as the comment has stated, your User model has a one to many relationship with both Tickets and TicketMessages, your Ticket model has a one to many relationship with TicketMessages. This means that deleting a User record would delete all TicketMessages and Tickets associated with that UserID, except that it is possible (for the system) that a TicketMessage with that UserID also uses a TickedID which isn't being deleted by the cascade from the UserID. Hence the error because that breaks the foreign key relationship.

There are two solutions:

1 - Modify your design, your TicketMessages model already has a relationship with Ticket, through Ticket ID and therefore has a relationship with UserID as UserID is associated with Ticket ID in the Ticket model. Therefore you can remove the UserID relationship from the TicketMessage model, it is not needed. Your problem will then be resolved. This is really the correct solution in my mind as it is your design which is the problem.

2 - If you for some reason I can't perceive want to maintain that relationship, you can disable cascade delete altogether using:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
    modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
}

The two lines remove both One to Many and Many to Many cascade deletion conventions respectively. You can keep them in or remove them as you see fit.

You can also remove singular cascade deletion relationships by using the following:

modelBuilder.Entity<ParentEntity>()
.HasMany(p => p.Children)
.WithRequired()
.HasForeignKey(c => c.ParentEntityId)
.WillCascadeOnDelete(false);

I highly recommend solution 1, remove UserID from your TicketMessages model, your TicketMessages are already linked to a UserID through the relationship between the TicketMessages model and the Ticket model.

edit - It's occurred to me you may want the relationship between TicketMessages and User to denote who wrote the message when that person is not the same user who created the ticket. If that's the case I would still suggest removing the relationship to the User table and instead use a field where you can populate whatever user information you want associated with that Ticket Message when it is created, e.g. Name. Or alternatively you can still have a UserID field but without the navigation property, removing the foreign key relationship. User information can then be called by matching this UserID to one in the User table as you retrieve information.

Answer 2

Thank you for answer Rob! I solved this problem with following code.

 modelBuilder.Entity<User>()
          .HasMany(e => e.TicketMessages)
          .WithRequired(e => e.User)
          .WillCascadeOnDelete(false);