How do I model friendships between users with EF code first?

Question

I am trying to understand how to represent user-friendliness with Entity Framework (5) Code First. My initial idea was to create a Friendship class that contains references to two user instances, so friendship is represented by separate objects.

public class Friendship { public virtual int Id { get; set; } [Required] public virtual UserProfile User1 { get; set; } [Required] public virtual UserProfile User2 { get; set; } [Required] public virtual DateTime Since { get; set; } } [Table("UserProfile")] public class UserProfile { [Key] [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)] public int UserId { get; set; } [Required] public string UserName { get; set; } } 

When trying to create a database through EF Migrations, however, I came across due to an SQL error:

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

Suggestions on how to solve this problem would be very welcome.

Answer 1

I managed to find a similar question about SO after a few more searches, and there is a solution that at least got into the database without any problems.

Basically, I added foreign keys for each user to the Friendship class, made them a composite primary key, and set up a second foreign key so as not to cascade on deletion. I ended up using the free EF configuration.

 public class Friendship { public virtual int UserId1 { get; set; } public virtual int UserId2 { get; set; } public virtual User User1 { get; set; } public virtual User User2 { get; set; } public DateTime since; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { ... modelBuilder.Entity<Friendship>().HasKey(f => new { f.UserId1, f.UserId2 }); modelBuilder.Entity<Friendship>() .HasRequired(f => f.User1) .WithMany() .HasForeignKey(f => f.UserId1); modelBuilder.Entity<Friendship>() .HasRequired(f => f.User2) .WithMany() .HasForeignKey(f => f.UserId2) .WillCascadeOnDelete(false); } 

Answer 2

You can define foreign keys yourself or delete cascading deletes, depending on your scenario. See This Related Question: Entity Structure Code First: A FOREIGN KEY Constraint May Cause Loops or Multiple Cascading Paths