I'm following a code-first approach with EF Core and have the following entities defined.

Role:

// This table will contain a list of all possible roles within the app
public class Role
{
    [Key]
    public int Id { get; set; }

    [Required]
    public string Name { get; set; }
}

Permission:

// This table will contain a list of all possible permissions within the app
public class Permission
{
    [Key]
    public int Id { get; set; }

    [Required]
    public string Name{ get; set; }
}

RolePermission:

// Links roles to permissions
public class RolePermission
{
    [Key]
    public int Id { get; set; }

    public Role Role { get; set; }

    public Permission Permission { get; set; }
}

As you can see, the idea is to have a one-to-many relationship between Role and Permission through a RolePermission table, giving the users of my application a way to configure what roles should have what permissions attached to them during runtime.

They will be able to create new roles as they wish, but the set of permissions available to them will not be configurable.

So how do I define this relationship and the foreign keys correctly? Do I use the FluentAPI, DataAnnotations, or both? How do I do it?

CodePudding user response：

From Microsoft docs.

"Relationships that are discovered by convention will always target the primary key of the principal entity. To target an alternate key, additional configuration must be performed using the Fluent API."

In your scenario you have a M:N relationship that tells that the correct path is Fluent API.

You must create a joining entity class for a joining table. The joining entity for the above Role and Permission entities should include a foreign key property and a reference navigation property for each entity.

The steps for configuring many-to-many relationships would the following:

1. Define a new joining entity class which includes the foreign key property and the reference navigation property for each entity.

2. Define a one-to-many relationship between other two entities and the joining entity, by including a collection navigation property in entities at both sides (Role and Permission, in this case).

3. Configure both the foreign keys in the joining entity as a composite key using Fluent API.

So, first of all, define the joining entity RolePermission, as shown below.

RolePermission.cs

public class RolePermission
{
    public int RoleId { get; set; }
    public Role Role { get; set; }

    public int PermissionId { get; set; }
    public Permission Permission { get; set; }
}

The above joining entity RolePermission includes reference navigation properties Role and Permission and their foreign key properties RoleId and PermissionId respectively (foreign key properties follow the convention).

Now, we also need to configure two separate one-to-many relationships between Role-> RolePermission and Permission -> RolePermission entities. We can do it by just following the convention for one-to-many relationships, as shown below.

Role.cs

public class Role
{
    public int RoleId { get; set; }
    public string Name { get; set; }

    public IList<RolePermission> RolePermissions { get; set; }
}

Permission.cs

public class Permission
{
    public int PermissionId { get; set; }
    public string Name { get; set; }

    public IList<RolePermission> RolePermissions { get; set; }
}

Now, the foreign keys must be the composite primary key in the joining table. This can only be configured using Fluent API, as below.

public class MyContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("CONNECTIONSTRING");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<RolePermission>().HasKey(rp => new { rp.RoleId, rp.PermissionId });
    }
    
    public DbSet<Role> Roles { get; set; }
    public DbSet<Permission> Permissions { get; set; }
    public DbSet<RolePermission> RolePermissions { get; set; }
}

In the above code, modelBuilder.Entity().HasKey(rp => new { rp.RoleId, rp.PermissionId }) configures RoleId and PermissionId as the composite key.

This is how you can configure many-to-many relationships if entities follow the conventions for one-to-many relationships with the joining entity. Suppose that the foreign key property names do not follow the convention (e.g. RId instead of RoleId and PId instead of PermissionId), then you can configure it using Fluent API, as shown below.

modelBuilder.Entity<RolePermission>().HasKey(rp => new { rp.RId, rp.PId });

modelBuilder.Entity<RolePermission>()
    .HasOne<Role>(rp => rp.Role)
    .WithMany(r => r.RolePermissions)
    .HasForeignKey(rp => rp.RId);


modelBuilder.Entity<RolePermission>()
    .HasOne<Permission>(rp => rp.Permission)
    .WithMany(p => p.RolePermissions)
    .HasForeignKey(rp => rp.PId);

IMPORTANT: You can configure the relationships in the UsingEntity arguments. For instance,constraints,cascades, etc.