Migrate many-to-many users-roles relationship to a many-to-one users-roles relationship in ASP.NET Core

I am developing an application using ASP.NET Core 6 and PostgreSQL. Currently there is a many-to-many relationship defined between users and roles, which means the following database tables:

asp_net_users
asp_net_roles
asp_net_user_roles

The latter table contains integer foreign keys to the user and role tables.

This is giving us some headache since the business logic requires exactly one role per user. The database and the code could be much clearer if the structure reflected this business requirement.

What I want is to

keep the asp_net_roles table as is
add a new not null column to the asp_net_users table (role_id) which contains a foreign key to the asp_net_roles table
drop the asp_net_user_roles table altogether.

Since the app is already in production, I would need to provide a migration so that everyone keeps their existing roles. My concept is doing it in multiple steps:

add the new column
set the new column to the appropriate value for every user (their highest role in the current structure)
drop the asp_net_user_roles table.

I don't have a problem doing this multi-stage migration, however I cannot seem to be able to configure the app to use a many-to-one relationship between users and roles. I'm not using the RoleManager (only UserManager), but the [Authorize("Role")] attributes on the controllers and controller actions need to work.

Startup:

services.AddIdentity<ApplicationUser, ApplicationRole>(options =>
            {
                //...
            })
            .AddEntityFrameworkStores<Context>()
            .AddDefaultTokenProviders();

ApplicationUser:

public class ApplicationUser : IdentityUser<int>
{
    // ...
}

ApplicationRole:

public class ApplicationRole : IdentityRole<int>
{
    public ApplicationRole(string roleName)
        : base(roleName)
    {
    }

    public ApplicationRole()
    {
    }
}

Context:

public class Context : IdentityDbContext<ApplicationUser, ApplicationRole, int>

I tried using the AddIdentityCore method instead of the AddIdentity without specifying the role and updating the Context inheritance accordingly, but every time the generated migration would not drop the asp_net_user_roles table or role-based authorization wouldn't work.

Solution

every time the generated migration would not drop the asp_net_user_roles table

You could Press F12 and check the codes in class IdentityDbContext<TUser, TRole, TKey> :

protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
        .......

        builder.Entity<TUserRole>(b =>
        {
            b.HasKey(r => new { r.UserId, r.RoleId });
            b.ToTable("AspNetUserRoles");
        });
    }

It would always create AspNetUserRoles table

Then I checked the document:

Each User can have many UserClaims.

Each User can have many UserLogins.

Each User can have many UserTokens.

Each Role can have many associated RoleClaims.

Each User can have many associated Roles, and each Role can be associated with many Users. This is a many-to-many relationship that requires a join table in the database. The join table is represented by the UserRole entity.

It was designed as above in parent class and you can't modify /remove the relationship in child class

If you insist on droping AspNetUserRoles table you could modify the db yourself,and try with Scaffold-dbcontext to create dbcontext&related entities ,but the usermanager/sigininmanager...wouldn't work any more,you have to implement authentication yourself