EF Core one-to-many or many-to-many ? users<-->friends

Question

I'm going to implement a user-friends module in my project.

Let's say I have 2 simple classes :

public class User
{
    int Id { get; set; }
    string Name { get; set; }
}

And FriendShip class (which has a relation between user) :

public class FriendShip
{
    int User1Id { get; set; }
    int User2Id { get; set; }
    int Status  { get; set; }
}

Status type is Enum

Logic for these 2 classes :

• When first user invite second user to his friendship-list, there should be 2 objects created in the database

user1Id: 1,
user2Id: 2,
Status: 1, (where 1 means 'invited')

---

user1Id: 2,
user2Id: 1,
Status: 0,    (where 0 means 'nonaccepted')

• When second user accepts, these 2 status will be updated by value 3 (where means 'friendship' or something like that - but forget about logic now.

I don't know how can I design the database.

It will be one-to-many, like one user can have many relationships to other-users ?

The private key must be 2 foreign key : user1, user2

The problem is that these 2 foreign keys goes to one table (Users)

I tried something like this :

modelBuilder.Entity<User>()
    .HasMany(x => x.UsersFriendships)
    .WithOne(x => x.User2)

modelBuilder.Entity<UsersFriendship>()
    .HasMany(x => x.Users)
    .WithOne(x => x.UsersFriendships)

But it doesn't make sense, I can't do the second statement

.WithOne(x => x.userFriendships)

---

The simplest way is to type in model builder :

modelBuilder.Entity<UsersFriendship>()
   .HasKey(x => new { x.User1Id, x.User2Id });

UserFriendship class :

public class UsersFriendship 
{
    [ForeignKey("User1Id")]
    public User User1 { get; set; }

    [ForeignKey("User2Id")]
    public User User2 { get; set; }

    public int User1Id { get; set; }
    
    public int User2Id { get; set; }
    
    public RelationStatus RelationStatus { get; set; }
}

But from now, I can't navigate from User entity to his friends (like User.Friendship)

and when I add navigation property to User class :

public virtual List<UsersFriendship> UsersFriendships { get; set; }

Database will four fields : user1Id, user2Id, status, userId

the latest field comes from user class (UsersFriendships field) <-- and I don't want this field on my database!

Answer 1

You can find one example in the official docs: https://learn.microsoft.com/en-us/ef/core/modeling/relationships#many-to-many.

I adjusted this example to reflect your case:

namespace foo
{
  public class User
  {
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual List<UsersFriendship> UsersFriendships { get; set; }
  }

  public class Friendship
  {
      public User User1 { get; set; }
      public int User1Id { get; set; }
      public User User2 { get; set; }
      public int User2Id { get; set; }
      public int Status  { get; set; }
  }

  class MyContext : DbContext
  {
      public DbSet<Friendship> Friendships { get; set; }
      public DbSet<User> Users { get; set; }

      protected override void OnModelCreating(ModelBuilder modelBuilder)
      {
          modelBuilder.Entity<Friendship>()
              .HasKey(fs => new { fs.User1, fs.User2, fs.Status });

          modelBuilder.Entity<Friendship>()
              .HasOne(fs => fs.User1)
              .WithMany(u => u.UsersFriendships)
              .HasForeignKey(fs => fs.User1);

          modelBuilder.Entity<Friendship>()
              .HasOne(fs => fs.User2)
              .WithMany(u => u.UsersFriendships)
              .HasForeignKey(fs => fs.User2);
      }
  }

the latest field comes from user class (UsersFriendships field) <-- and i dont want this field on my database!

When registering this as navigational property (see my code) then it will not be stored in db.

In other cases, if for some reason you don't want a property to be stored in db by ef core, mark the property with the [NotMapped] attribute (namespace System.ComponentModel.DataAnnotations.Schema).