CODEWITHSUNDEEP
c#entity-frameworkef-code-first
CinnamonBun
CinnamonBun

Reputation: 1150

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.

Upvotes: 2

Views: 343

Answers (2)

Slauma
Slauma

Reputation: 177163

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.

Upvotes: 2

Aidin
Aidin

Reputation: 2134

You need to configure your mappings: http://www.remondo.net/code-first-fluent-api-entity-type-configuration/

Upvotes: 0

Related Questions