EF Foreign Key constraint

Question

I'm still studying Entity Framework and tried to create a model including the foreign keys.

But when I tried to migrate the code, I got this error

Introducing FOREIGN KEY constraint 'FK_dbo.QuestionResults_dbo.QuestionsTables_QuetionsTableId' on table 'QuestionResults' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints

These are my model classes:

public class MainDetails
{
    [Key]
    public int Id { get; set; }
    public string Language { get; set; }

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

    [Required]
    public string ContactNumber { get; set; }
    public string EmailAddress { get; set; }

    [DisplayName("Service Type")]
    [ForeignKey("QuestionsTable")]
    public int ServiceTypeId { get; set; }
    public virtual QuestionsTable QuestionsTable { get; set; }

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

    [Required]
    public string ServiceLocation { get; set; }
    public string Suggestion { get; set; }
    public bool Status { get; set; } = true;

    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    [Display(Name = "Created Date")]
    public DateTime CreatedDate { get; set; } = DateTime.Now;

    public virtual QuestionResults QuestionResults { get; set; }

    public virtual IList<QuestionResults> QuestionResultsMainlist { get; set; }
    public virtual IList<QuestionsTable> QuestionsTables { get; set; }
}

public class QuestionsTable
{
    [Key]
    public int Id { get; set; }
    public string ServiceType { get; set; }
    public string Question { get; set; }
    public virtual IList<MainDetails> MainDetailsServiceType { get; set; }
    public QuestionsTable()
    {
        MainDetailsServiceType = new List<MainDetails>();
    }
}

public class QuestionResults
{
    [Key]
    public int Id { get; set; }

    [DisplayName("MainDetail ID")]
    [ForeignKey("MainDetails")]
    public int MainDetailsId { get; set; }
    public virtual MainDetails MainDetails { get; set; }

    [DisplayName("MainDetail ID")]
    [ForeignKey("QuestionsTable")]
    public int QuetionsTableId { get; set; }
    public virtual QuestionsTable QuestionsTable { get; set; }

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

This is the table structure I wanted to create:

this question is purely related to Entity Framework and does not reference any ASP.NET MVC feature in any way. They are commonly used together which may caused the confusion on your side — Yehor Androsov, Apr 11 '22 at 09:12
Check it out here [Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why?](https://stackoverflow.com/questions/17127351/introducing-foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths) — Reza Heidari, Apr 11 '22 at 09:29
What's your question? The error explains the solution if you turn off cascade delete on the QuestionsTable:Id then the model will be happy :) — Heberda, Apr 11 '22 at 10:13

Heberda · Accepted Answer · 2022-04-12T13:10:15.753

To resolve this you can use the EF Model Builder

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{   
    modelBuilder.Entity<QuestionsTable>()
        .HasRequired(a => a.MainDetails)
        .WithOptionalDependent()
        .WillCascadeOnDelete(false); //This is the important row
}

You'll have to play around with the type of relationship you would like along with whether you specify explicitly the foreign key. If you haven't seen this model builder before have a read here: https://learn.microsoft.com/en-us/ef/core/modeling/

Upon breaking your scenario down I noticed some "oddities". I reduced the noise in the domain model to from your example to this

public class MainDetails
{
    [Key]
    public int Id { get; set; }

    [ForeignKey("QuestionsTable")]
    public int ServiceTypeId { get; set; }
    
    public virtual QuestionsTable QuestionsTable { get; set; }

    public virtual QuestionResults QuestionResults { get; set; }

    public virtual IList<QuestionResults> QuestionResultsMainlist { get; set; }
    
    public virtual IList<QuestionsTable> QuestionsTables { get; set; }
}

public class QuestionsTable
{
    [Key]
    public int Id { get; set; }
    public string ServiceType { get; set; }
    public string Question { get; set; }
    public virtual IList<MainDetails> MainDetailsServiceType { get; set; }
}

public class QuestionResults
{
    [Key]
    public int Id { get; set; }

    [ForeignKey("MainDetails")]
    public int MainDetailsId { get; set; }
    public virtual MainDetails MainDetails { get; set; }

    [ForeignKey("QuestionsTable")]
    public int QuetionsTableId { get; set; }
    public virtual QuestionsTable QuestionsTable { get; set; }
}

I few things I noted.

MainDetails contains both One-To-Many (QuestionTable) and Many-To-Many (IList) relationships? I'm unsure on your intention
QuestionsResults contains singular relationships to both entities which aren't replicated in the QuestionTable class? that's fine if it's intentional
ServiceType is a string in QuestionsTable but you are expecting an int as the foreign key in MainDetails?

thanks for the code, Before that, I want to know if my Foreign key mapping is correct? I thought my mapping was wrong. — Dev Beginner, Apr 12 '22 at 02:51
I've updated my answer although I think it goes beyond the general question and has veered into "waffle" on my part. — Heberda, Apr 12 '22 at 13:10

EF Foreign Key constraint

1 Answers1