EF Code First giving problems in foreign keys

Question

public class ParikshaContext :DbContext
{
    public ParikshaContext()
    {

       Database.SetInitializer(new DropCreateDatabaseIfModelChanges<ParikshaContext>());

    }
    public DbSet<UserDetail> UserDetails { get; set; }
    public DbSet<Standard> Standards { get; set; }
    public DbSet<Subject> Subjects { get; set; }
    public DbSet<QuestionDescriptor> QuestionDescriptors { get; set; }
    public DbSet<QuestionBrief> QuestionBriefs { get; set; }
    public DbSet<QuestionCustom> QuestionCustoms { get; set; }
    public DbSet<QuestionChoice> QuestionChoices { get; set; }
    public DbSet<QuestionMatch> QuestionMatches { get; set; }
    public DbSet<Test> Tests { get; set; }
    public DbSet<Test_Question> Test_Questions { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<QuestionCustom>().ToTable("Custom");
        modelBuilder.Entity<QuestionBrief>().ToTable("Brief");
        modelBuilder.Entity<QuestionMatch>().ToTable("Match");
        modelBuilder.Entity<QuestionChoice>().ToTable("Choice");
    }       
} 

public class QuestionDescriptor
{

    public int QuestionDescriptorId { get; set; } 

    public int StandardId { get; set; }
    [ForeignKey("StandardId")]
    public virtual Standard Standard { get; set; }

    public int SubjectId { get; set; }
    [ForeignKey("SubjectId")]
    public virtual Subject Subject { get; set; }

    public int Rating { get; set; }
    public int Difficulty { get; set; }

    public DateTime DateOfCreation{get;set;}

    public int UserDetailId { get; set; }     
    [ForeignKeyAttribute("UserDetailId")]
    public virtual UserDetail Creator { get; set; }

}

public class QuestionBrief : QuestionDescriptor
{
    public String QuestionText { get; set; }
    public String Answer { get; set; }
    //true for fill in the blanks and false for a loing answers
    public bool Short { get; set; }
}

public class Standard
{
    public int StandardId { get; set; }
    public String StandardName { get; set; }

}

public class Subject
{
    public int SubjectId { get; set; }
    public String SubjectName { get; set; }
    public String SubjectCategory { get; set; }        

  //  public int StandardId { get; set; }
  //  [ForeignKey("StandardId")]
   // public virtual Standard Standard { get; set; }
}

public class Test
{

    public int TestID { get; set; }
    public DateTime DateOfCreation { get; set; }    

    public String StandardName { get; set; }
    public String SubjectName { get; set; }
    public String SubjectCategory { get; set; }

//    public int UserDetailId { get; set; }
//    [ForeignKey("UserDetailId")]
 //   public virtual UserDetail Creator { get; set; }


}

public class Test_Question
{

    public int Test_QuestionID { get; set; }     

    public int TestId { get; set; }
    [ForeignKey("TestId")]
    public virtual Test Test { get; set; }

    public int QuestionDescriptorId { get; set; }
    [ForeignKey("QuestionDescriptorId")]
    public virtual QuestionDescriptor Question { get; set; }
}

public class UserDetail
{

    public int UserDetailId { get; set; }

    [Required]
    [MaxLength(10, ErrorMessage = "UserName must be 10 characters or less"), MinLength(5)]
    public String Name { get; set; }

    [Required]        
    public String Password { get; set; }
    public String UserRole { get; set; }
    public DateTime DateOfCreation{  get; set;}    
}

//Match,Custom,Choice classes have been omitted for lack of space (which sounds stupid when i look at the amount of code i have pasted )

I have two problems:-

1. I cant get a foreign key relation between standard and subjects,it says the relation will cause several cascade delete paths...

2. if I make a foreign key rlation between test and usedetail it gives me the above problem for mapping the tst_question table .

Also since I am new to EF code first ,please point out my mistakes.all help and disccussion is welcome.

Answer 1

By default EF will create foreign keys will cascade delete. In your model if you delete a Standard there are multiple paths to delete the QuestionDescriptor.

Standard -> QuestionDescriptor

and

Standard -> Subject -> QuestionDescriptor

That is why SQL server does not allow you to do this. See this answer for more details

What you can do is explicitly tell EF to create foreign keys without cascade delete. But this may create data integrity problems. So make sure you understand the consequences.

What you can do is configure the relationships using fluent API with WillCascadeOnDelete(false).

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  //other mappings

      modelBuilder.Entity<Subject>()
        .HasRequired(subject => subject.Standard).WithMany()
        .HasForeignKey(subject => subject.StandardId)
        .WillCascadeOnDelete(false);
}