Entity Framework Framework First Collection Mapping - FriendRequests

Question

I need to map many-to-many relationships using First Entity Framework Code First. This is a standard FriendRequests socialnetworking mapping. The user object has a FriendRequests collection of type List<User> . In the database, I use the connection table for the relationship as follows:

 CREATE TABLE dbo.FriendRequests( UserId INT NOT NULL FOREIGN KEY REFERENCES dbo.Users(id), FriendId INT NOT NULL FOREIGN KEY REFERENCES dbo.Users(id), RequestDate SMALLDATETIME NOT NULL DEFAULT GETUTCDATE()) GO ALTER TABLE dbo.FriendRequests ADD PRIMARY KEY (UserId,FriendId) GO 

How to map a user object in Entity Framework code First, to enable the collection through the connection table?

Answer 1

You can try this as follows:

 public class User { public int Id { get; set; } public ICollection<FriendRequest> FriendRequests { get; set; } } public class FriendRequest { public int UserId { get; set; } public int FriendId { get; set; } public DateTime RequestDate { get; set; } public User User { get; set; } public User Friend { get; set; } } 

Mapping with the Fluent API:

 modelBuilder.Entity<User>() .HasMany(u => u.FriendRequests) .WithRequired(f => f.User) .HasForeignKey(f => f.UserId); modelBuilder.Entity<FriendRequest>() .HasKey(f => new { f.UserId, f.FriendId }); modelBuilder.Entity<FriendRequest>() .HasRequired(f => f.Friend) .WithMany() .HasForeignKey(f => f.FriendId); 

Due to the RequestDate property in the link table, you cannot match this as a many-to-many relationship. Instead, you need two one-to-many relationships, as shown above.

You may need to disable cascading deletion, I'm not sure. You can do this by adding .WillCascadeOnDelete(false) at the end of two one-to-many comparisons.

Edit

To your comment: If you remove the RequestDate column, you can create your own model with a many-to-many relationship. In this case, you do not need the FriendRequest object:

 public class User { public int Id { get; set; } public ICollection<User> FriendRequests { get; set; } } 

Mapping with the Fluent API:

 modelBuilder.Entity<User>() .HasMany(u => u.FriendRequests) .WithMany() .Map(m => { m.ToTable("FriendRequests"); m.MapLeftKey("UserId"); m.MapRightKey("FriendId"); });