Can't add multiple Foreign Keys to a table - EF - Code First

I decided to play around with the code first option with the EF, however, i've run into a problem regarding multiple foreign keys in a single table.

The error I get is:

The referential relationship will result in a cyclical reference that is not allowed. [ Constraint name = FK_dbo.Comments_dbo.Users_UserId ]

I have three tables, User, Post and Comments. Using my limited knowledge in this field, I have created three classes.

User

public class User
{
    [Key]
    public int UserId { get; set; }
    public string Username { get; set; }
    public string Email { get; set; }
    public string FName { get; set; }
    public string LName { get; set; }
    public DateTime JoinDate { get; set; }
    public string Password { get; set; }
    public virtual ICollection<Post> Posts { get; set; }
    public virtual ICollection<Comment> Comments { get; set; }

    public User()
    {
        Posts = new List<Post>();
    }
}

Post

public class Post
{
    [Key]
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Body { get; set; }
    public DateTime PublishDate { get; set; }
    public int UserId { get; set; }
    public  virtual ICollection<Comment> Comments { get; set; }

    public Post()
    {
        Comments = new List<Comment>();
    }
}

Comment

public class Comment
{
    [Key]
    public int CommentId { get; set; }
    public string Title { get; set; }
    public string Body { get; set; }
    public DateTime CommentDate { get; set; }
    public int UserId { get; set; }
    public int PostId { get; set; }
}

The relationship between the UserId in the 'User' table, and the UserId in the 'Post' table is fine. However, I run into problems when I wish to create a relationship from the 'Comment' table to the 'Post' and 'User' tables. I'm not sure what i'm doing wrong, as each table is connected to their respective Id. Any help would be appreciated.

Answers


You will probably have to disable cascading delete on one or two of the relationships, for example for the relationships of User.Posts and User.Comments. You must do it with Fluent API in an overridden OnModelCreating of your derived DbContext:

modelBuilder.Entity<User>()
    .HasMany(u => u.Posts)
    .WithRequired()
    .HasForeignKey(p => p.UserId)
    .WillCascadeOnDelete(false);

modelBuilder.Entity<User>()
    .HasMany(u => u.Comments)
    .WithRequired()
    .HasForeignKey(c => c.UserId)
    .WillCascadeOnDelete(false);

Alternatively you could make the relationships to User optional instead of required. It would be as simple as making the UserId foreign key properties in Post and Comment nullable:

public int? UserId { get; set; }

It might even make sense from a business perspective to allow keeping posts and comments in the system as anonymous even after the user has been deleted, represented by a null value of UserId in Post and Comment.


Need Your Help

Gmail footer png not transparent

caching gmail png transparent

I've linked a transparent image into my gmail footer. But after I send it, it's no longer transparent. Google seems to cache it and remove the transparency.

Change control colour, and change it back to original

c# winforms button background click

I want to change colour of the button momentarily to show that button has been pressed. How can I achieve this in C#? I can change the background color easily enough like this: