locked
Many To many relationship creating error - cycles or multiple cascade paths RRS feed

  • Question

  • User438705957 posted

    I am developing a code-first MVC application in Visual Studio 2013 with EF 6.2 and MVC 5.2.

    I have what I think is a standard many to many relationship in the model between Audit and Audit_Measure i.e an Audit can have many Audit_Measures and an Audit_Measure can have many Audits. However, both can be created without the other i.e an Audit does not need an Audit_Measure and a Audit_Measure does not need an Audit.
    Audit_Measure is also in a one to many relationship with Audit_Measure_Assessment.

    Here is the Model:

       [Table("TRACS.Audit")]
        public class Audit
        {  
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            [Display(Name = "Id", AutoGenerateField = true)]
            [DisplayInReportsAttribute]
            public int Audit_Id { get; set; }
    ....
            [Display(Name = "NoDisplay", AutoGenerateField = false, Order = 2)]
            public virtual ICollection<Audit_Measure> Audit_Measures { get; set; }   
           
        }
     [Table("TRACS.Audit_Measure")]
        public class Audit_Measure
        {
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            [Display(Name = "Id", AutoGenerateField = true)]
            public int Audit_Measure_Id { get; set; }
            
    [Display(Name = "NoDisplay", AutoGenerateField = false, Order = 1)]
            public virtual ICollection<Audit> Audits { get; set; }
    
            [Display(Name = "NoDisplay", AutoGenerateField = false, Order = 2)]
            public virtual ICollection<Audit_Measure_Assessment> Audit_Measure_Assessments { get; set; }
    
        }

    To the context I have added fluent API as follows:

     modelBuilder.Entity<Audit>()
                    .HasMany(c => c.Audit_Measures).WithMany(i => i.Audits)
                    .Map(t => t.MapLeftKey("Audit_Id")
                        .MapRightKey("Audit_Measure_Id")
                        .ToTable("Audit_Audit_Measure", "TRACS")); 

    On the very first run of the application, I am receiving the error:
    "Introducing FOREIGN KEY constraint 'FK_TRACS.Audit_Audit_Measure_TRACS.Audit_Measure_Audit_Measure_Id' on table 'Audit_Audit_Measure' 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."

    After consulting Professor Google, I can't quite figure out what it is exactly complaining about and where the multiple cascade paths issue comes into it.
    In addtion, I am not sure where to specify ON DELETE NO ACTION or ON UPDATE NO ACTION, as the database is not created yet.

    Thanks for considering.

    Tuesday, February 20, 2018 11:18 PM

All replies

  • User438705957 posted

    I got around this by adding the following fluent API:

    modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();

    But I still have no idea what it could possibly be complaining about in the first instance.

    Wednesday, February 21, 2018 12:34 AM
  • User1120430333 posted

    I have what I think is a standard many to many relationship in the model between Audit and Audit_Measure i.e an Audit can have many Audit_Measures and an Audit_Measure can have many Audits.

    That looks to be a circular reference. You may have to rethink it. A child has reference to its parent. A child having reference to many parents is questionable.

    https://en.wikipedia.org/wiki/Circular_reference

    Wednesday, February 21, 2018 5:19 AM
  • User1400794712 posted

    Hi madog,

    I've tried this code, it will generate a table —— TRACS.Audit_Audit_Measure to store the Many-to-Many relationship.

    Let's check the design of this table:

    CREATE TABLE [TRACS].[Audit_Audit_Measure] (
        [Audit_Id]         INT NOT NULL,
        [Audit_Measure_Id] INT NOT NULL,
        CONSTRAINT [PK_TRACS.Audit_Audit_Measure] PRIMARY KEY CLUSTERED ([Audit_Id] ASC, [Audit_Measure_Id] ASC),
        CONSTRAINT [FK_TRACS.Audit_Audit_Measure_TRACS.Audit_Audit_Id] FOREIGN KEY ([Audit_Id]) REFERENCES [TRACS].[Audit] ([Audit_Id]) ON DELETE CASCADE,
        CONSTRAINT [FK_TRACS.Audit_Audit_Measure_TRACS.Audit_Measure_Audit_Measure_Id] FOREIGN KEY ([Audit_Measure_Id]) REFERENCES [TRACS].[Audit_Measure] ([Audit_Measure_Id]) ON DELETE CASCADE
    );
    
    GO
    CREATE NONCLUSTERED INDEX [IX_Audit_Id]
        ON [TRACS].[Audit_Audit_Measure]([Audit_Id] ASC);
    
    GO
    CREATE NONCLUSTERED INDEX [IX_Audit_Measure_Id]
        ON [TRACS].[Audit_Audit_Measure]([Audit_Measure_Id] ASC);

    We can find there is 'DELETE CASCADE' for these two foreign keys. 

    When we delete one relationship between them, it will delete all Audit and Audit_Measure belong to that. It may cause above problem.

    So, we add modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>(); to turn it off.

    Best Regards,

    Daisy

    Wednesday, February 21, 2018 6:06 AM
  • User438705957 posted

    Thanks DA924, I will follow that link. but isn't that the very nature of a many to many relationship. Neither being the child or parent., but both related to one another on a many to many basis?

    I would have thought that's a valid relationship.

    Friday, February 23, 2018 4:37 AM
  • User438705957 posted

    Thanks XD.

    I guess adding modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>(); turns off the On Delete Cascade

    I think no one in their right mind would deal with the many to many relationship by editing the actual join table, either thru the UI or not.
    So I belive EF should not add the cascade delete in the firstplace.

    In any case, I've nominated a parent between the tables as per the fluent API, and will deal with the MTM by adding/removing Audit_Measures from Audit. 

    I am actually upgrading my application to MVC from a Web API/Code First EF/HTML5/Javascript app, and it worked  beautifully in the old app.

    Friday, February 23, 2018 4:47 AM
  • User1120430333 posted

    Thanks DA924, I will follow that link. but isn't that the very nature of a many to many relationship. Neither being the child or parent., but both related to one another on a many to many basis?

    I would have thought that's a valid relationship.

    Thanks DA924, I will follow that link. but isn't that the very nature of a many to many relationship. Neither being the child or parent., but both related to one another on a many to many basis?

    I would have thought that's a valid relationship.

    No that is not correct. And you couldn't successfully set table schemas with a DB administration tool  or by using a T-SQL script that I know about.

    Saturday, February 24, 2018 6:15 AM