Entity Framework 6 Code First - Many to Many with User UserRole Permission

Question

I'm using EF6 Code First and fluent API. I have a many to many relationship between Users, Roles and Permission. Users have many Roles and viceversa (but I'm not interested in navigating from Roles to Users). Roles have many Permissions and viceversa (but I'm not interested in navigating from Permissions to Roles).

My database structure is (simplified for this example):

User = (UserId, Name)
UserRole = (UserRoleId, Name)
UserUserRole = (UserId, UserRoleId)
UserPermission = (UserPermissionId, Name)
UserRoleUserPermission = (UserRoleId, UserPermissionId)

My C# classes are:

public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Password { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public DateTime DateCreated { get; set; }
        public DateTime DateUpdated { get; set; }
        public virtual IList<Role> Roles { get; set; }
        public bool Active { get; set; }
    }

public class Role
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public bool Active { get; set; }
        public virtual IList<Permission> Permissions { get; set; }
    }

public class Permission
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public bool Active { get; set; }
    }

My mapping is:

// User
            modelBuilder.Entity<User>().ToTable("User");
            modelBuilder.Entity<User>().Property(p => p.Id).HasColumnName("UserId");
            modelBuilder.Entity<User>().Property(p => p.DateCreated).HasColumnName("DateCreated");
            modelBuilder.Entity<User>().Property(p => p.DateUpdated).HasColumnName("DateUpdated");
            modelBuilder.Entity<User>().Property(p => p.Email).HasColumnName("Email");
            modelBuilder.Entity<User>().Property(p => p.FirstName).HasColumnName("FirstName");
            modelBuilder.Entity<User>().Property(p => p.LastName).HasColumnName("LastName");
            modelBuilder.Entity<User>().Property(p => p.Name).HasColumnName("Name");
            modelBuilder.Entity<User>().Property(p => p.Password).HasColumnName("Password");
            modelBuilder.Entity<User>().HasMany(p => p.Roles).WithMany().Map(p => 
            { 
                p.ToTable("UserUserRole");
                p.MapLeftKey("UserId");
                p.MapRightKey("UserRoleId");
            });
            modelBuilder.Entity<User>().Property(p => p.Active).HasColumnName("IsActive");

            // UserRole
            modelBuilder.Entity<Role>().ToTable("UserRole");
            modelBuilder.Entity<Role>().Property(p => p.Id).HasColumnName("UserRoleId");
            modelBuilder.Entity<Role>().Property(p => p.Name).HasColumnName("Name");
            modelBuilder.Entity<Role>().HasMany(p => p.Permissions).WithMany().Map(p =>
            {
                p.ToTable("UserRoleUserPermission");
                p.MapLeftKey("UserRoleId");
                p.MapRightKey("UserPermissionId");
            });
            modelBuilder.Entity<Role>().Property(p => p.Active).HasColumnName("IsActive");

            // UserPermission
            modelBuilder.Entity<Permission>().ToTable("UserPermission");
            modelBuilder.Entity<Permission>().Property(p => p.Id).HasColumnName("UserPermissionId").HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
            modelBuilder.Entity<Permission>().Property(p => p.Name).HasColumnName("Name");
            modelBuilder.Entity<Permission>().Property(p => p.Active).HasColumnName("IsActive");

This is the error that I'm getting:

One or more validation errors were detected during model generation:

UserRoleId: Name: Each property name in a type must be unique. Property name 'UserRoleId' is already defined. UserRole_RolePermission: : The number of properties in the Dependent and Principal Roles in a relationship constraint must be identical.

Answer 1

I really don't like how I solved this problem and I'm sure that there has to be a better way. Apparently, there was a problem (maybe it's a bug in EF) with the column names because the name of my primary keys are the same as the name of the compound primary keys and I was mapping all my properties indicating the mapping db column. So, I renamed my classes to avoid having to specify the column mapping on the primary keys and that solved the problem.

C# classes:

public class User
    {
        public int UserId { get; set; }
        public string Name { get; set; }
        public string Password { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public DateTime DateCreated { get; set; }
        public DateTime DateUpdated { get; set; }
        public virtual ICollection<UserRole> Roles { get; set; }
        public bool Active { get; set; }
    }

public class UserRole
    {
        public int UserRoleId { get; set; }
        public string Name { get; set; }
        public bool Active { get; set; }
        public virtual ICollection<UserPermission> Permissions { get; set; }
    }

public class UserPermission
    {
        public int UserPermissionId { get; set; }
        public string Name { get; set; }
        public bool Active { get; set; }
    }

EF Mapping:

// User
            modelBuilder.Entity<User>().ToTable("User");
            modelBuilder.Entity<User>().Property(p => p.UserId).HasColumnName("UserId");
            modelBuilder.Entity<User>().Property(p => p.Name).HasColumnName("Name");
            modelBuilder.Entity<User>().Property(p => p.Password).HasColumnName("Password");
            modelBuilder.Entity<User>().Property(p => p.Email).HasColumnName("Email");
            modelBuilder.Entity<User>().Property(p => p.FirstName).HasColumnName("FirstName");
            modelBuilder.Entity<User>().Property(p => p.LastName).HasColumnName("LastName");
            modelBuilder.Entity<User>().Property(p => p.DateCreated).HasColumnName("DateCreated");
            modelBuilder.Entity<User>().Property(p => p.DateUpdated).HasColumnName("DateUpdated");
            modelBuilder.Entity<User>().HasMany(p => p.Roles).WithMany().Map(p => 
            { 
                p.ToTable("UserUserRole");
                p.MapLeftKey("UserId");
                p.MapRightKey("UserRoleId");
            });
            modelBuilder.Entity<User>().Property(p => p.Active).HasColumnName("IsActive");

            // UserRole
            modelBuilder.Entity<UserRole>().ToTable("UserRole");
            modelBuilder.Entity<UserRole>().Property(p => p.Name).HasColumnName("Name");
            modelBuilder.Entity<UserRole>().HasMany(p => p.Permissions).WithMany().Map(p =>
            {
                p.ToTable("UserRoleUserPermission");
                p.MapLeftKey("UserRoleId");
                p.MapRightKey("UserPermissionId");
            });
            modelBuilder.Entity<UserRole>().Property(p => p.Active).HasColumnName("IsActive");

            // UserPermission
            modelBuilder.Entity<UserPermission>().ToTable("UserPermission");
            modelBuilder.Entity<UserPermission>().Property(p => p.UserPermissionId).HasColumnName("UserPermissionId").HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
            modelBuilder.Entity<UserPermission>().Property(p => p.Name).HasColumnName("Name");
            modelBuilder.Entity<UserPermission>().Property(p => p.Active).HasColumnName("IsActive");

I hope to find a better way because I'm not happy with this solution but at least it works and I can continue developing the app.

EDIT:

I made some changes to my classes and this mapping also works:

So, the problem only happens when I rename the class UserRole to Role. It has to be something related to the database table UserUserRole, maybe the property for mapping to this table (ToTable property) is not working as expected.

// User
            modelBuilder.Entity<User>().ToTable("User");
            modelBuilder.Entity<User>().Property(p => p.Id).HasColumnName("UserId");
            modelBuilder.Entity<User>().Property(p => p.Name).HasColumnName("Name");
            modelBuilder.Entity<User>().Property(p => p.Password).HasColumnName("Password");
            modelBuilder.Entity<User>().Property(p => p.Email).HasColumnName("Email");
            modelBuilder.Entity<User>().Property(p => p.FirstName).HasColumnName("FirstName");
            modelBuilder.Entity<User>().Property(p => p.LastName).HasColumnName("LastName");
            modelBuilder.Entity<User>().Property(p => p.DateCreated).HasColumnName("DateCreated");
            modelBuilder.Entity<User>().Property(p => p.DateUpdated).HasColumnName("DateUpdated");
            modelBuilder.Entity<User>().HasMany(p => p.Roles).WithMany().Map(p => 
            { 
                p.ToTable("UserUserRole");
                p.MapLeftKey("UserId");
                p.MapRightKey("UserRoleId");
            });
            modelBuilder.Entity<User>().Property(p => p.Active).HasColumnName("IsActive");

            // UserRole
            modelBuilder.Entity<UserRole>().ToTable("UserRole");
            modelBuilder.Entity<UserRole>().HasKey(p => p.Id).Property(p => p.Id).HasColumnName("UserRoleId");
            modelBuilder.Entity<UserRole>().Property(p => p.Name).HasColumnName("Name");
            modelBuilder.Entity<UserRole>().HasMany(p => p.Permissions).WithMany().Map(p =>
            {
                p.ToTable("UserRoleUserPermission");
                p.MapLeftKey("UserRoleId");
                p.MapRightKey("UserPermissionId");
            });
            modelBuilder.Entity<UserRole>().Property(p => p.Active).HasColumnName("IsActive");