ASP.NET MVC 4 Code-First IdentityUserRole with additional primary key 3-way-relation

Question

I have been searching the web for weeks in hope of finding a solution to an issue I'm having when using the .NET Identity framework to create my database.

One of the closest related issues i found is this: Customised IdentityUserRole primary key

However I am using automatic migrations, in order to avoid manually specifying Up and Down methods.

What I'm trying to achieve is to have an additional primary key in my UserRoles table, which is derived from IdentityUserRoles and the implementation looks like this:

public class UserRole : IdentityUserRole
{
    [Key, ForeignKey("Company")]
    public string CompanyId { get; set; }

    public Company Company { get; set; }
}

My OnModelCreating method looks like this:

...
public DbSet<UserRole> UserRolesExt { get; set; }
...

protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<User>().ToTable("Users");
    modelBuilder.Entity<IdentityUserRole>().ToTable("UserRoles");
    // Failed attempt to make property a primary key below:
    modelBuilder.Entity<UserRole>().HasKey(ur => ur.CompanyId).ToTable("UserRoles");
    modelBuilder.Entity<IdentityUserLogin>().ToTable("UserLogins");
    modelBuilder.Entity<IdentityUserClaim>().ToTable("UserClaims");
    modelBuilder.Entity<IdentityRole>().ToTable("Roles");
}

However, the table ends up looking like this:

https://i.stack.imgur.com/Ziz0e.png

One thing that got me puzzled is the fact that it is also set as a nullable type, but I have a feeling that is refering to my custom Company class which only contains primitive data types.

The reason why I want this 3-way-relationship for this entity is that my system will contain several users in several companies, where each user can be a member of more than one company, and hereby also individual roles of that user within the given company.

I would if possible, very much like to avoid making my own UserStores and RoleStores etc., which is some of the solutions I've found, since I would like to keep it simple so to say.

An example from one of my classes that does not derive from Identity classes looks as follows and works just as intended:

public class UserModule
{
    [Key, ForeignKey("User"), Column(Order = 0)]
    public string UserId { get; set; }
    public User User { get; set; }

    [Key, ForeignKey("Module"), Column(Order = 1)]
    public string ModuleId { get; set; }
    public Module Module { get; set; }
}

I have tried fiddling around with various versions of my data annotations in my UserRole class but without any luck. Is there really no way of extending this UserRole class to have an additional primary key, without having to make major customizations?

It should also be noted that if I simply edit the database manually after creation, I am able to make all three properties primary keys and the table works as intended. But I would very much like to know how I can make it work properly so to say.

Any help would be greatly appreciated!

Answer 1

If I understand correctly, you want to add an additional column to the IdentityUserRole class and have that column be part of the primary key. To do so, you have to slightly modify your OnModelCreating method as follows:

modelBuilder.Entity<UserRole>()
            .ToTable("UserRoles")
            .HasKey(r => new { r.UserId, r.RoleId, r.CompanyId })
            .HasRequired(r => r.Company).WithMany();

This code essentially redefines the primary key to also include the CompanyId column and then makes it a required field.

You should also remove the [Key, ForeignKey("Company")] attributes from the CompanyId property, to prevent the annotations from interfering with the fluent syntax. I don't think the modelBuilder.Entity<IdentityUserRole>().ToTable("UserRoles"); line is necessary too, you could try removing it.