Manually Script ASP.Net Identity Existing Database

Question

I have an existing database that has a roles table already and I am trying to get Identity to use the existing roles in the table but I keep getting this error.

The entity types 'ApplicationRole' and 'Role' cannot share table 'Roles' because they are not in the same type hierarchy or do not have a valid one to one foreign key relationship with matching primary keys between them.

Table Mappings I am trying to achieve is,

AspNetUsers -> User (ApplicationUser)
AspNetRoles -> Role (ApplicationRole)
AspNetUserLogins -> UserLogins (ApplicationUserLogin)
AspNetUserRoles -> RoleUser (ApplicationUserRole)

I can't use code-first due to the database is in use by other applications. So I have to send my script changes to a DBA.

** EXISTING SQL TABLES **

/* Object:  Table [dbo].[Role] */
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Role](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Description] [varchar](100) NOT NULL CONSTRAINT [DF_Role_Description]  DEFAULT (''),
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/* Object:  Table [dbo].[RoleUser] */
CREATE TABLE [dbo].[RoleUser](
    [UserID] [int] NOT NULL,
    [RoleID] [int] NOT NULL,
 CONSTRAINT [PK_AppUserRole] PRIMARY KEY CLUSTERED 
(
    [UserID] ASC,
    [RoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

/* Object:  Table [dbo].[User] */
CREATE TABLE [dbo].[User](
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [UserName] [dbo].[shortString] NOT NULL CONSTRAINT [DF_User_UserName]  DEFAULT (''),
    [FirstName] [dbo].[shortString] NULL CONSTRAINT [DF_User_FirstName]  DEFAULT (''),
    [LastName] [dbo].[shortString] NULL CONSTRAINT [DF_User_LastName]  DEFAULT (''),
    [Email] [dbo].[longString] NULL CONSTRAINT [DF_User_Email]  DEFAULT (''),
    [Pager] [dbo].[smallString] NULL CONSTRAINT [DF_User_Pager]  DEFAULT (''),
    [IsActive] [bit] NOT NULL CONSTRAINT [DF_User_IsActive]  DEFAULT ((1)),
    [LastPasswordChange] [datetime] NULL,
    [AccountLockedDate] [datetime] NULL,
    [AccountLockedByComputerName] [dbo].[shortString] NULL,
    [AccountLockedByUserName] [dbo].[shortString] NULL,
    [LastActive] [datetime] NULL,
    [PasswordHash] [nvarchar](max) NULL,
    [SecurityStamp] [nvarchar](max) NULL,
    [Discriminator] [nvarchar](max) NULL,
    [EmailConfirmed] [bit] NULL,
    [PhoneNumber] [nvarchar](50) NULL,
    [PhoneNumberConfirmed] [bit] NULL,
    [TwoFactorEnabled] [bit] NULL,
    [LockoutEndDateUtc] [datetime] NULL,
    [LockoutEnabled] [bit] NULL,
    [AccessFailedCount] [int] NULL,
 CONSTRAINT [PK_AppUser] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

DATABASE CONTEXT

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

            // Asp.net Identity
            modelBuilder.Entity<ApplicationUser>().ToTable("User");
            modelBuilder.Entity<ApplicationRole>().ToTable("Role");
            modelBuilder.Entity<ApplicationUserClaim>().ToTable("UserClaims");
            modelBuilder.Entity<ApplicationUserLogin>().ToTable("UserLogins");
            modelBuilder.Entity<ApplicationUserRole>().ToTable("RoleUser");
        }

IDENTITY CLASSES

public class ApplicationUserLogin : IdentityUserLogin<int> { }
    public class ApplicationUserClaim : IdentityUserClaim<int> { }
    public class ApplicationUserRole : IdentityUserRole<int> { }

    public class ApplicationRole : IdentityRole<int, ApplicationUserRole>, IRole<int>
    {
        public string Description { get; set; }

        public ApplicationRole() : base() { }
        public ApplicationRole(string name)
            : this()
        {
            this.Name = name;
        }

        public ApplicationRole(string name, string description)
            : this(name)
        {
            this.Description = description;
        }
    }

    public class ApplicationUser : IdentityUser<int, ApplicationUserLogin, ApplicationUserRole, ApplicationUserClaim>, IUser<int>
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Password { get; set; }

        public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser, int> manager)
        {
            var userIdentity = await manager
                .CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
            return userIdentity;
        }
    }

Trying to create FK/PK relationships but having no luck.

What di you really want to do ? Di you need the ApplicationRole and Role to share the same existing TAble ? — CodeNotFound
ApplicationRole simply extends my IdentityRole to use an int instead of a string. I just want Identity to use the Role table for managing roles. — devfunkd

CamW CamW · Accepted Answer · 2016-05-30T06:32:17

If I create a new ASP.NET MVC app and change the identity stuff as you've indicated and create a database containing only the tables you've specified and I try to login then I get an error

Invalid column name 'Password'.

Which makes sense since there is no 'Password' field in the definition of your table but the field is defined in your entity.

Then I change the OnModelCreating method from:

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

    // Asp.net Identity
    modelBuilder.Entity<ApplicationUser>().ToTable("User");
    modelBuilder.Entity<ApplicationRole>().ToTable("Role");
    modelBuilder.Entity<ApplicationUserClaim>().ToTable("UserClaims");
    modelBuilder.Entity<ApplicationUserLogin>().ToTable("UserLogins");
    modelBuilder.Entity<ApplicationUserRole>().ToTable("RoleUser");
}

to:

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

    // Asp.net Identity
    modelBuilder.Entity<ApplicationUser>().ToTable("User");
    modelBuilder.Entity<ApplicationRole>().ToTable("Role");
    modelBuilder.Entity<ApplicationUserClaim>().ToTable("Role");
    modelBuilder.Entity<ApplicationUserLogin>().ToTable("UserLogins");
    modelBuilder.Entity<ApplicationUserRole>().ToTable("RoleUser");
}

Then I get the error:

The entity types 'ApplicationUserClaim' and ApplicationRole' cannot share table 'Role' because they are not in the same type hierarchy or do not have a valid one to one foreign key relationship with matching primary keys between them.

Notice how my error complains about the table Role whereas your error is complaining about the table Roles

So, having considered all of that, I have some observations, suggestions and requests:

There's no fundamental issue preventing you from using ASP.NET Identity in the way that you want to since I was able to set it up as you require it on a fresh install and get past the point where it complained about entity types sharing tables.
Perhaps there are other entities in your app which are conflicting is that possible?
Do you have both a Role and Roles table in your database? Or entities in your project? Are you sure that this is the error that you get, using this exact code that you posted? As I said, it's strange that you're error complains about Roles and mine about Role
Are you able to provide a minimal verifiable example of the problem (https://stackoverflow.com/help/mcve) as I said, I'm unable to reproduce the problem from a clean MVC project.

Manually Script ASP.Net Identity Existing Database

2 Answers