Question 114979 in PostgreSQL Question posted in
The official documentation can be found here.

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

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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    So the final answer is based on Austin C who decided basic M:M solution.

    Code example based on question:

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

    #region IEntity
    #endregion
}

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; }
    public ICollection<PosUser> Users { get; set; }

    #region IEntity
    #endregion
}

    No need for anything else because EF Core can deal with models and it'll make links table like "PosUserRole".

    What was tried is called "Jaywalking" and it's an anti-pattern (Using PostgreSQL array to store many-to-many relationship)


  2. 0

    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; }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search