How to create a table with two columns that are related to the primary key of another table?-CodePudding

I'm using ASP.NET Core web API and the code-first approach to create required tables (in SQLite). I have the following code for the Users table:

public class UserModel
{
    [Key]
    public int UserID { get; set; }
    [Required]
    public string Username { get; set; }
    [Required]
    public string Password { get; set; }
    [Required]
    public string FirstName { get; set; }
    [Required]
    public string LastName { get; set; }
    [Required]
    public string Role { get; set; }

    //Navigation property
    public Department Department { get; set; }
}

I want to create a whitelist of Users who can send messages to each other.

My expected table is something like this:

                Message Whitelist
-------------------------------------------------
SenderUserID            ReceiverUserID
------------            --------------
    23                       19
    23                       39
    29                       11
    11                       29
    11                       19

I need a C# model for creating this table. Please note that the UserIDs in the table above are linked to the UserIDs in the Users table. I have previously used fluent API to create a many-to-many relationship between two tables but in this case, I need to refer to the same table (Users table) and I don't know how to do so.

I tried to create this table using the following code:

public class UserSuperior
{
    public int UserID { get; set; }
    public UserModel User { get; set; }
    public int SuperiorUserID { get; set; }
    public UserModel SuperiorUser { get; set; }
}

public class UserModel
{
    [Key]
    public int UserID { get; set; }
    [Required]
    public string Username { get; set; }
    [Required]
    public string Password { get; set; }
    [Required]
    public string FirstName { get; set; }
    [Required]
    public string LastName { get; set; }
    [Required]
    public string Role { get; set; }
   
    //Navigation property
    public Department Department { get; set; }
    public List<UserSuperior> UserSuperiors { get; set; }
}

Context.cs:

public class SqliteContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlite(@"DataSource=Database.db;");
}
    protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<UserSuperior>()
                .HasKey(e => new { e.UserID, e.SuperiorUserID });
}

    public DbSet<UserModel> Users { get; set; }
    public DbSet<Department> Departments { get; set; }
    public DbSet<UserSuperior> UserSuperiors { get; set; }
}

But, the following error is shown when trying to add migration:

Unable to determine the relationship represented by navigation 'UserModel.UserSuperiors' of type 'List'. Either manually configure the relationship, or ignore this property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

CodePudding user response：

You could use a entity class like this

public class WhitelistModel
{
    [Key]
    public int ID { get; set; }
    
    public int SenderUserID { get; set; }
    
    public int ReceiverUserID { get; set; }
    
    public UserModel SenderUser { get; set; }
    
    public UserModel ReceiverUser { get; set; }
}

And give the proper foreign key mappings in the EF configurations.