ASP.NET MVC 5 EF Cannot add or update a child row: a foreign key constraint fails

Question

I've a thread which contains a startpost as part of the thread-content. It also includes a list of replys, which are also posts.

class Post {
    [Key]
    public int Id{get;set;}
    public DateTime CreationDate{get;set;}
    public virtual string Content{get;set;}
    public int ThreadId{get;set;}
    public virtual Thread Thread{get;set;}
}

class Thread {
    [Key]
    public int Id{get;set;}
    public string Title{get;set;}
    public int FirstPostId{get;set;}
    public virtual Post FirstPost{get;set;}
    public List<Post> Replys{get;set;}
}

When a thread is created i simply add them to the DbContext and save it. This works without issues. But if a reply is submitted, I add them to the post-list and mark the entity of the thread as modified like this

var db = new MyContext();
var thread = db.Threads.Where(thread => thread.Id = threadId).FirstOrDefault();
thread.Replys.Add(newPost);
db.Entry<Thread>(thread).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();

Here is the problem that I get a violation of the foreignkey on Thread.FirstPost:

Cannot add or update a child row: a foreign key constraint fails ("MyTable"."posts", CONSTRAINT "Thread_FirstPost" FOREIGN KEY ("Id") REFERENCES "threads" ("Id") ON DELETE NO ACTION ON UPDATE NO ACTION)

I found many information about this. In short, all say, that this is related to the default behavior of EF that checks the integrity. So when a Thread has to be deleted it depends on the FirstPost which has also to be deleted but this depends on the Thread which seem to confuse EF.

The internet has 2 solutions for this problem: Using fluent-API to disable cascade for the entity using .WillCascadeOnDelete(false); or disable it completely by removing the convention. I tried both ways:

protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        //base.OnModelCreating(modelBuilder);
        modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
        modelBuilder.Entity<Thread>()
            .HasOptional(t => t.FirstPost)
            .WithRequired()
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<Post>()
            .HasOptional(p => p.Thread)
            .WithMany()
            .HasForeignKey(p => p.ThreadId)
            .WillCascadeOnDelete(false);
}

But nothing is working, I get the same exception than before. I have no idea why, and it seems that all others which had this problem could solve it by using one of those methods, but in my case both have no effect...

Table-Definitions from Visual Studio Server-Explorer

CREATE TABLE `posts` (
  `Id` int(11) NOT NULL,
  `CreationDate` datetime NOT NULL,
  `Content` longtext NOT NULL,
  `ThreadId` int(11) NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `ThreadId` (`ThreadId`),
  CONSTRAINT `Thread_Post` FOREIGN KEY (`Id`) REFERENCES `threads` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `Thread_Replys` FOREIGN KEY (`ThreadId`) REFERENCES `threads` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `threads` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Title` longtext NOT NULL,
  `PostId` int(11) NOT NULL,
  `ViewsCount` int(11) NOT NULL,
  `IsClosed` tinyint(1) NOT NULL,
  `IsVisible` tinyint(1) NOT NULL,
  `ReplysCount` int(11) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Table-Definition generated by EF (from Database-Server)

CREATE TABLE `Posts`(
    `Id` int NOT NULL, 
    `CreationDate` datetime NOT NULL, 
    `Content` longtext NOT NULL, 
    `ThreadId` int NOT NULL
)
ALTER TABLE `Posts` ADD PRIMARY KEY (Id)
ALTER TABLE `Posts` ADD CONSTRAINT Thread_Post
ALTER TABLE `Posts` ADD KEY (`ThreadId`)
ALTER TABLE `Posts` ADD CONSTRAINT Thread_Replys
ALTER TABLE `Posts` ADD CONSTRAINT Thread_Post
    FOREIGN KEY (Id)
    REFERENCES `Threads` (Id)
ALTER TABLE `Posts` ADD CONSTRAINT Thread_Replys
    FOREIGN KEY (ThreadId)
    REFERENCES `Threads` (Id)
ALTER TABLE `Posts` ADD CONSTRAINT Thread_Replys
    FOREIGN KEY (ThreadId)
    REFERENCES `Threads` (Id)
    ON DELETE NO ACTION ON UPDATE NO ACTION

CREATE TABLE `Threads`(
    `Id` int NOT NULL AUTO_INCREMENT UNIQUE, 
    `Title` longtext NOT NULL, 
    `PostId` int NOT NULL, 
    `ViewsCount` int NOT NULL, 
    `IsClosed` bool NOT NULL, 
    `IsVisible` bool NOT NULL, 
    `ReplysCount` int NOT NULL
)
ALTER TABLE `Threads` ADD PRIMARY KEY (Id)

Here are some pages I found on my research to this topic: http://weblogs.asp.net/manavi/associations-in-ef-code-first-ctp5-part-3-one-to-one-foreign-key-associations

http://www.codeproject.com/Articles/368164/EF-Data-Annotations-and-Code-Fluent

http://geekswithblogs.net/danemorgridge/archive/2010/12/17/ef4-cpt5-code-first-remove-cascading-deletes.aspx

http://patrickdesjardins.com/blog/entity-framework-4-3-delete-cascade-with-code-first-poco

ASP.NET MVC 4 Multiple Foreign Keys Referencing Single Parent Entity

http://www.davepaquette.com/archive/2012/09/15/whered-my-data-go-andor-how-do-i-get-rid-of-it.aspx

http://czetsuya-tech.blogspot.de/2012/01/specify-on-delete-no-action-or-on.html#.Viy-0X54u9J

Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why?

Entity Framework: how to solve "FOREIGN KEY constraint may cause cycles or multiple cascade paths"?

Specify ON DELETE NO ACTION in ASP.NET MVC 4 C# Code First

Answer 1

Your foreign keys are not set properly, it's ok not to define a foreign key explicitly in case you user the original class name, I'll explain it on your code:

class Post {
    [Key]
    public int Id{get;set;}
    public DateTime CreationDate{get;set;}
    public virtual string Content{get;set;}
    public int ThreadId{get;set;}  **-> here you used ThreadId which is implicitly a foreignkey for Thread, and that's good**
    public virtual Thread Thread{get;set;}
}

class Thread {
    [Key]
    public int Id{get;set;}
    public string Title{get;set;}
    public int FirstPostId{get;set;} **-> here you can do the same by changing this to PostId**
    public virtual Post FirstPost{get;set;} **-> and this to Post**
    public List<Post> Replys{get;set;}
}

Or you have a better option of using data annotations:

Replace this:

 public virtual Post FirstPost{get;set;}

with this:

[ForeignKey("FirstPostId")]
public virtual Post FirstPost{get;set;}

This is telling EF that FirstPostId is the foreignkey for `FirstPost.

Let me know if that worked.

UPDATE

I've changed your sql code manually and it works now:

CREATE TABLE threads (
  Id int NOT NULL,
  Title nvarchar(max) NOT NULL,
  PostId int,
  ViewsCount int NOT NULL,
  IsClosed tinyint NOT NULL,
  IsVisible tinyint NOT NULL,
  ReplysCount int NOT NULL,
  PRIMARY KEY (Id),
) 

CREATE TABLE posts (
  Id int NOT NULL,
  CreationDate datetime NOT NULL,
  Content nvarchar(max) NOT NULL,
  ThreadId int NOT NULL,
  PRIMARY KEY (Id),
  CONSTRAINT Thread_Post FOREIGN KEY (Id) REFERENCES threads (Id) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT Thread_Replys FOREIGN KEY (ThreadId) REFERENCES threads (Id) ON DELETE NO ACTION ON UPDATE NO ACTION
)

ALTER TABLE threads ADD CONSTRAINT Post_Thread FOREIGN KEY (PostId) REFERENCES posts (Id) ON DELETE NO ACTION ON UPDATE NO ACTION

You can't define thread to require postID, and post to require threadID at the same time, if you do you won't be able to create either (unless you create both at the same time - meaning in the same db.savechanges()).

Think about it. you want to define a thread using a post that doesn't exists yet.

Answer 2

The root problem is that your model contains a 1:1 association between Post and Thread in which Thread is the principle, or independent, entity. This is expressed by the part ...

modelBuilder.Entity<Thread>()
    .HasOptional(t => t.FirstPost)
    .WithRequired()

You see it reflected in the DDL statement ...

ALTER TABLE `Posts` ADD CONSTRAINT Thread_Post
    FOREIGN KEY (Id)
    REFERENCES `Threads` (Id)

So Post's primary key is also a foreign key to Thread. This means that you can't ever insert more than one Post per Thread! (Because each subsequent Post necessarily should have a new PK value, but that doesn't refer to an existing Thread so you get constraint violations).

You could solve this (maybe) by making Post the principle entity. In that case, Thread would have a PK/FK combination referring to its first Post. However, to me, 1:1 associations convey that entities are strongly related to a point where they're almost one (Student-StudentDetails). So I don't think a 1:1 association is appropriate here anyway.

I would suggest this mapping:

modelBuilder.Entity<Thread>()
    .HasOptional(t => t.FirstPost)
    .WithMany()
    .HasForeignKey(t => t.FirstPostId)
    .WillCascadeOnDelete(false);

modelBuilder.Entity<Post>()
    .HasRequired(p => p.Thread)
    .WithMany(t => t.Replies)
    .HasForeignKey(p => p.ThreadId)
    .WillCascadeOnDelete(false);

This theoretically turns the relationship between Thread and FirstPost into 1-to-many, but practically it means that Thread now has a foreign key to its first post, and these complicated PK/FK combinations are gone. Note that FirstPostId should be a nullable int to support this.

On the other hand, if in your opinion a Thread and its first post are closely related you could consider merging both into a thread that also has the attributes of its first post (CreationDate, Content). You'll end up with a very simple model of threads (posts?) with replies in which still nothing is redundant.