Geek Answers Handbook

In Manual Mode Script Identifies Existing ASP.Net Database

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

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

The table comparisons I'm trying to achieve are

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

I cannot use the first code because the database is used by other applications. Therefore, I need to send the script changes to the database administrator.

** 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 CONTROL

        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");
        }

CLASSES OF IDENTITY

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 no luck.

+4
source share
2 answers

If I create a new ASP.NET MVC application and change the contents of the identifier as you indicated, and create a database containing only the tables you specify, and I try to log in, then I get an error

Invalid column name 'Password'.

This makes sense because there is no "Password" field in the definition of your table, but the field is defined in your entity.

OnModelCreating :

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");
}


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");
}

:

ApplicationUserClaim ApplicationRole ", .

, Role, Roles

, , , :

0

EF Identity Role UserRole.

:

public class ApplicationUserRole : IdentityUserRole<int>
{
    public ApplicationUserRole()
        : base()
    { }
    //this is important
    public virtual ApplicationRole Role { get; set; }
}

OnModelCreating , :

modelBuilder.Entity<ApplicationUserRole>().HasRequired(ur => ur.Role).WithMany().HasForeignKey(ur => ur.RoleID);

, , DbContext.

0

More articles:


All Articles