How can I store one to many in PostgreSQL via array of alt keys?-CodePudding

I have two classes - Role and PosUser.

public class Role : IEntity
{
    public string Name { get; set; }
    [Column(TypeName = "jsonb")]
    public string[] Permissions { get; set; }
    public bool IsProtected { get; set; }
    public uint Priority { get; set; }
    
    #region IEntity
    #endregion
}

public class PosUser : IEntity
{
    public string Name { get; set; }
    public List<Role> Roles { get; set; }

    #region IEntity
    #endregion
}

I want to have two tables on each of these entitites. Roles should not know anything about Users, but every User should store jsonb array of role's names like ["Admin", "Test"]

I tried to use:

protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<Role>().HasAlternateKey(x => x.Name);

        builder.Entity<PosUser>().Property(u => u.Roles)
            .HasPostgresArrayConversion(r => r.Name, name => Find<Role>(name));
        base.OnModelCreating(builder);
    }

But I got error about context disposed.

These doesn't fit:

Store links by ForeignKeys in new table
Store all links to users at Role table

CodePudding user response：

I would create a new table to store a many:many relationship between PosUser and Role, let's call it PosUserRoles

public class PosUserRoles
{
    // FK to PosUser
    public string PosUserKey { get; set; }
    
    // FK to Role
    public string RoleKey { get; set; }
}