locked
FOREIGN KEY May Introduce Multiple Cascade Paths RRS feed

  • Question

  • Hi everyone,

     

    I got the following exception when I tried to create a PM System using EF Code First and mapped the sender and receiver using the Fluent API. I think I understand where the error is coming from but I do not know how to solve it. Anyway, here's my setup:

      public class Message
      {
        [Key]
        public int ID { get; set; }
        public string Subject { get; set; }
        public string Content { get; set; }
        public DateTime DateSent { get; set; }
        public bool IsRead { get; set; }
        public virtual User SentBy { get; set; }
        public virtual User ReceivedBy { get; set; }
      }
    
      public class User 
      {
        public int ID { get; set; }
        public string NickName { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public string Password { get; set; }
        //Removed a lot of the properties to keep the code clear
        public virtual ICollection<Message> ReceivedMessages { get; set; }
        public virtual ICollection<Message> SentMessages { get; set; }
      }
    
    
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
          modelBuilder.Entity<Message>()
            .HasRequired(p => p.SentBy)
            .WithMany(c => c.SentMessages)
            .IsIndependent()
            .Map(mc => mc.MapKey(c => c.ID, "Sender"));
            
    
          modelBuilder.Entity<Message>()
            .HasRequired(p => p.ReceivedBy)
            .WithMany(c => c.ReceivedMessages)
            .IsIndependent()
            .Map(mc => mc.MapKey(c => c.ID, "Receiver"));
        }
    

    And this is the exception:

    Message: The database creation succeeded, but the creation of the database objects did not. See InnerException for details.

    InnerException: Introducing FOREIGN KEY constraint 'Message_SentBy' on table 'Messages' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.\r\nCould not create constraint. See previous errors.

    Any suggestions?

     

    Thanks in advance,

    Kassem

    Saturday, January 29, 2011 1:29 PM

Answers

  • This exception has been  generated purely by SQL Server when Code First was trying to create a database based on your object model since SQL Server does not support Multiple Cascade Paths. In fact, Code First automatically turns on Cascade Deletes on required one-to-many associations based on the convention. In order to resolve this exception, we have no choice other than overriding this convention and switching cascade deletes off on at least one of the associations.

    Your associations are Independent and I cannot find a way in fluent API to directly turn cascade off (like the same way we do it on FK associations by WillCascadeOnDelete() method). But there is one way to achieve this: making one of your associations optional, so Code First wouldn't try to enable cascade delete on that one by convention:

     

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
     modelBuilder.Entity<Message>()
     .HasRequired(m => m.SentBy)
     .WithMany(u => u.SentMessages)
     .IsIndependent()
     .Map(mc => mc.MapKey(c => c.ID, "Sender"));
     
     modelBuilder.Entity<Message>()
     .HasOptional(m => m.ReceivedBy)
     .WithMany(u => u.ReceivedMessages)
     .IsIndependent()
     .Map(mc => mc.MapKey(c => c.ID, "Receiver"));
    }
    

    For more info on this matter, take a look at this blog post where I discuss this at length:

    Associations in EF Code First CTP5: Part 3 – One-to-One Foreign Key Associations

     

     

     

     

     

    • Marked as answer by KassemD Saturday, January 29, 2011 6:39 PM
    Saturday, January 29, 2011 3:52 PM

All replies

  • This exception has been  generated purely by SQL Server when Code First was trying to create a database based on your object model since SQL Server does not support Multiple Cascade Paths. In fact, Code First automatically turns on Cascade Deletes on required one-to-many associations based on the convention. In order to resolve this exception, we have no choice other than overriding this convention and switching cascade deletes off on at least one of the associations.

    Your associations are Independent and I cannot find a way in fluent API to directly turn cascade off (like the same way we do it on FK associations by WillCascadeOnDelete() method). But there is one way to achieve this: making one of your associations optional, so Code First wouldn't try to enable cascade delete on that one by convention:

     

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
     modelBuilder.Entity<Message>()
     .HasRequired(m => m.SentBy)
     .WithMany(u => u.SentMessages)
     .IsIndependent()
     .Map(mc => mc.MapKey(c => c.ID, "Sender"));
     
     modelBuilder.Entity<Message>()
     .HasOptional(m => m.ReceivedBy)
     .WithMany(u => u.ReceivedMessages)
     .IsIndependent()
     .Map(mc => mc.MapKey(c => c.ID, "Receiver"));
    }
    

    For more info on this matter, take a look at this blog post where I discuss this at length:

    Associations in EF Code First CTP5: Part 3 – One-to-One Foreign Key Associations

     

     

     

     

     

    • Marked as answer by KassemD Saturday, January 29, 2011 6:39 PM
    Saturday, January 29, 2011 3:52 PM
  • Yes, I actually tried to turn off cascade on delete but using WillCascadeOnDelete() kept returning an error saying that this method cannot be applied on an object of type "void". But your trick actually did it, so thanks a lot! :)
    Saturday, January 29, 2011 6:41 PM