Put list in own table

Question

Put list in own table

c# entity-framework-core

Question

I'm trying to make a permission system in my C# application.

My Idea is this:

Class: Permission
 - string name;
 - string value;

Class: Role
 - string name
 - List<Permission> Permissions;

Class: User
 - List<Role> Roles;
 - List<Permission> AdditionalPermissions;

Very nice and flexible. I can create roles with permissions, and even custom assign permissions to some users, in special cases.

Entity framework had other plans, in the database, the permission table has a UserID and a RoleID reference, not exactly what i was expecting.

Table: Permissions
 - int id;
 - nvarchar name;
 - nvarchar value;
 - int UserID
 - int RoleID


Table: Roles
 - int id;
 - nvarchar name;

Table: Users;
 - int id

Can I make entity framework make a structure like below, without having to create the C# classes myself?

Table: Permissions
 - int id;
 - nvarchar name;
 - nvarchar value;

Table: Roles
 - int id;
 - nvarchar name;

Table: Users;
 - int id

Table: Users_Permissions
 - int UserID;
 - int PermissionID;

Table: Role_Permissions
 - int RoleID
 - int PermissionID;

PeaceDealer

View more on Stack Overflow

Marc Brekoo · Accepted Answer · 9/6/2018 8:28:03 AM

When you structure your entity classes like this, the db structure turns out the way you want:

public class Permission
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Value { get; set; }

}

public class RolePermission
{
    [ForeignKey(nameof(Role))]
    public int RoleId { get; set; }
    public virtual Role Role { get; set; }

    [ForeignKey(nameof(Permission))]
    public int PermissionId { get; set; }
    public virtual Permission Permission { get; set; }
}

public class Role
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<RolePermission> RolePermissions { get; set; }

}

To ensure the RolePermission table gets a composite primary key of the two foreign keys, add this to your context:

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<RolePermission>().HasKey(table => new {
        table.RoleId,
        table.PermissionId
    });
}

Put list in own table

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related