.NET Core 2.1 Identity : Creating a table for each Role + bridge M:M table

.net-core asp.net-core-identity entity-framework-core

Question

I'm having issues in figuring out the best design that fits my needs regarding a Role based authorizations using Identity in a .NET Core 2.1 project.

I already extended the User class from Identity with an ApplicationUser class. I need 5 different roles to control the access to the different features of the app :

Admin, Teacher, Student, Parent and Supervisor

All the common attributes are kept in User and ApplicationUser but I still require different relationships to other tables depending of the User's Role.

  • User in Role Teacher is linked to 1-N School
  • User in Role Student is linked to 1-N GroupOfStudents (but not to a School directly)
  • User in Role Parent is linked to 1-N Student (but not to a School)
  • ...

The other requirement is that a User must be able to be in 1-N Role.

What would be the best practice in my case?

Is there something I'm missing in the features of Identity?

My idea at first was to use nullable FK, but as the number of role increased, it doesn't look like a good idea to have so many empty fields for all those records.

I was thinking of using a "bridge table" to link a User to other tables for each role. Have a many-to-many relationship between ApplicationUser and the bridge table nd a 0-1 relationship between the bridge table and individual tables for each role. But that's not really helping either since every record will produce the same amount of empty fields.

I'm fairly new with .NET Core and especially Identity, I'm probably missing some keywords to make an effective research because it looks to me that it's a really basic system (nothing really fancy in the requirements).

Thanks for reading !

EDIT : I don't really have a error right now since I'm trying to figure out the best practice before going deeper in the project. Since it's the first time I face that kind of requirement, I'm trying to find documentation on what are the pros/cons.

I followed Marco's idea and used inheritance for my role based models as it was my first idea. I hope it will help understand my concern.

public class ApplicationUser : IdentityUser
{
    public string CustomTag { get; set; }
    public string CustomTagBis { get; set; }
}
    public class Teacher : ApplicationUser
{
    public string TeacherIdentificationNumber { get; set; }
    public ICollection<Course> Courses { get; set; }
}
public class Student : ApplicationUser
{
    public ICollection<StudentGroup> Groups { get; set; }
}
public class Parent : ApplicationUser
{
    public ICollection<Student> Children { get; set; }
}
public class Course
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Category { get; set; }
}
public class StudentGroup
{
    public int Id { get; set; }
    public string Name { get; set; }
}

This creates the database having one big table for the User containing all the attributes :

User table generated image

I can use this and it will work. A user can have any of those nullable fields filled if he requires to be in different role.

My concern is that for each record I will have a huge number of "inappropriate fields" that will remain empty. Let's say that on 1000 users 80% of the users are Students. What are the consequences of having 800 lines containing : - an empty ParentId FK - an empty TeacherIdentificationNumber

And this is just a small piece of the content of the models. It doesn't "feel" right, am I wrong?

Isn't there a better way to design the entities so that the table User only contains the common attributes to all users (as it is supposed to?) and still be able to link each user to another table that will link the User to 1-N tables Teacher/Student/Parent/... table?

Diagram of the Table-Per-Hierarchy approach image

EDIT 2: Using the answer of Marco, I tried to use the Table-Per-Type approach. When modifying my context to implement the Table-Per-Type approach, I encountered this error when I wanted to add a migration :

"The entity type 'IdentityUserLogin' requires a primary key to be defined."

I believe this happens because I removed :

base.OnModelCreating(builder);

Resulting in having this code :

protected override void OnModelCreating(ModelBuilder builder)
{
        //base.OnModelCreating(builder);
        builder.Entity<Student>().ToTable("Student");
        builder.Entity<Parent>().ToTable("Parent");
        builder.Entity<Teacher>().ToTable("Teacher");
}

I believe those identity keys are mapped in the base.OneModelCreating. But Even if I Uncomment that line, I keep the same result in my database.

After some research, I found this article that helped me go through the process of creating Table-per-type models and apply a migration.

Using that approach, I have a schema that looks like this : Table-Per-Type approach image

Correct me if I'm wrong, but both Techniques fits my requirements and it is more about the preference of design? It doesn't have big consequence in the architecture nor the identity features?


For a third option, I was thinking to use a different approach but I'm not too sure about it.

Does a design like this could fit my requirements and is it valid? By valid, I mean, it feels weird to link a teacher entity to a Role and not to a User. But in a way, the teacher entity represent the features that a User will need when in the teacher role.

Role to Entities image

I'm not yet too sure of how to implement this with EF core and how overriding the IdentityRole class will affect the Identity features. I'm on it but haven't figured it out yet.

1
1
10/2/2018 2:36:10 PM

Accepted Answer

I suggest you take advantage of the new features of asp.net core and the new Identity framework. There is a lot of documentation about security.

You can use policy based security, but in your case resource-based security seems more appropriate.

The best approach is to not mix contexts. Keep a seperation of concerns: Identity context (using UserManager) and business context (school, your DbContext).

Because putting the ApplicationUser table in your 'business context' means that you are directly accessing the Identity context. This is not the way you should use Identity. Use the UserManager for IdentityUser related queries.

In order to make it work, instead of inheriting the ApplicationUser table, create a user table in your school context. It is not a copy but a new table. In fact the only thing in common is the UserId field.

Check my answer here for thoughts about a more detailed design.

Move fields like TeacherIdentificationNumber out of the ApplicationUser. You can either add this as claim to the user (AspNetUserClaims table):

new Claim("http://school1.myapp.com/TeacherIdentificationNumber", 123);

or store it in the school context.

Also instead of roles consider to use claims, where you can distinguish the claims by type name (e.g. http://school1.myapp.com/role):

new Claim("http://school1.myapp.com/role", "Teacher");
new Claim("http://school2.myapp.com/role", "Student");

Though I think in your case it may be better to store the information in the school context.

The bottom line, keep the Identity context as is and add tables to the school context instead. You don't have to create two databases, just don't add cross-context relations. The only thing that binds the two is the UserId. But you don't need an actual database relation for that.

Use UserManager, etc. for Identity queries and your school context for your application. When not for authentication you should not use the Identity context.


Now to the design, create one user table that has a matching UserId field to link the current user. Add fields like name, etc only when you want to show this (on report).

Add a table for Student, Teacher, etc. where you use a composite key: School.Id, User.Id. Or add a common Id and use a unique constraint on the combination of School.Id, User.Id.

When a user is present in the table this means that the user is a student at school x or teacher at school y. No need for roles in the Identity context.

With the navigation properties you can easily determine the 'role' and access the fields of that 'role'.

2
10/3/2018 4:56:48 PM

Popular Answer

What you do is completely up to your requirements. What you currently have implemented is called Table-Per-Hierarchy. This is the default approach, that Entity Framework does, when discovering its model(s).

An alternative approach would be Table-Per-Type. In this case, Entity Framework would create 4 tables.

  1. The User table
  2. The Student table
  3. The Teacher table
  4. The Parent table

Since all those entities inherit from ApplicationUser the database would generate a FK relationship between them and their parent class.

To implemt this, you need to modify your DbContext:

public class FooContext : DbContext
{
    public DbSet<ApplicationUser> Users { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Student>().ToTable("Students");
        modelBuilder.Entity<Parent>().ToTable("Parents");
        modelBuilder.Entity<Teacher>().ToTable("Teachers");
    }
}

This should be the most normalized approach. There is however a third approach, where you'd end up with 3 tables and the parent ApplicationUser class would be mapped into its concrete implementations. However, I have never implemented this with Asp.Net Identity, so I don't know if it would or will work and if you'd run into some key conflicts.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow