Renaming identity tables with disabling EF6

Question

I am trying to rename my Identity 2.0 tables using the Migrations tool in EF6 / Package Manager. However, this exploded part of the way. I simply call the following code fragment after "ApplicationDBContext Create" in IdentityModels.cs:

protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity<IdentityUser>().ToTable("Users"); modelBuilder.Entity<IdentityRole>().ToTable("Roles"); modelBuilder.Entity<IdentityUserRole>().ToTable("UserRoles"); modelBuilder.Entity<IdentityUserLogin>().ToTable("UserLogins"); modelBuilder.Entity<IdentityUserClaim>().ToTable("UserClaims"); } 

Then it raises this error:

 PM> Update-Database -Verbose Using StartUp project 'ProjectSender'. Using NuGet project 'ProjectSender'. Specify the '-Verbose' flag to view the SQL statements being applied to the target database. Target database is: 'Projectsender' (DataSource: xxxx, Provider: System.Data.SqlClient, Origin: Configuration). Applying explicit migrations: [201505080059533_RenameTables]. Applying explicit migration: 201505080059533_RenameTables. EXECUTE sp_rename @objname = N'dbo.AspNetRoles', @newname = N'Roles', @objtype = N'OBJECT' IF object_id('[PK_dbo.AspNetRoles]') IS NOT NULL BEGIN EXECUTE sp_rename @objname = N'[PK_dbo.AspNetRoles]', @newname = N'PK_dbo.Roles', @objtype = N'OBJECT' END Caution: Changing any part of an object name could break scripts and stored procedures. Caution: Changing any part of an object name could break scripts and stored procedures. EXECUTE sp_rename @objname = N'dbo.AspNetUserRoles', @newname = N'UserRoles', @objtype = N'OBJECT' IF object_id('[PK_dbo.AspNetUserRoles]') IS NOT NULL BEGIN EXECUTE sp_rename @objname = N'[PK_dbo.AspNetUserRoles]', @newname = N'PK_dbo.UserRoles', @objtype = N'OBJECT' END EXECUTE sp_rename @objname = N'dbo.AspNetUserClaims', @newname = N'UserClaims', @objtype = N'OBJECT' IF object_id('[PK_dbo.AspNetUserClaims]') IS NOT NULL BEGIN EXECUTE sp_rename @objname = N'[PK_dbo.AspNetUserClaims]', @newname = N'PK_dbo.UserClaims', @objtype = N'OBJECT' END EXECUTE sp_rename @objname = N'dbo.AspNetUserLogins', @newname = N'UserLogins', @objtype = N'OBJECT' IF object_id('[PK_dbo.AspNetUserLogins]') IS NOT NULL BEGIN EXECUTE sp_rename @objname = N'[PK_dbo.AspNetUserLogins]', @newname = N'PK_dbo.UserLogins', @objtype = N'OBJECT' END IF object_id(N'[dbo].[FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId]', N'F') IS NOT NULL ALTER TABLE [dbo].[AspNetUserClaims] DROP CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId] System.Data.SqlClient.SqlException (0x80131904): Cannot find the object "dbo.AspNetUserClaims" because it does not exist or you do not have permissions. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c) at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery() at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinTransaction(IEnumerable`1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinNewTransaction(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection) at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClass30.<ExecuteStatements>b__2e() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation) at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements, DbTransaction existingTransaction) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements) at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, VersionedModel targetModel, IEnumerable`1 operations, IEnumerable`1 systemOperations, Boolean downgrading, Boolean auto) at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration) at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClassc.<Update>b__b() at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration) at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.Run() at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate) at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate) at System.Data.Entity.Migrations.Design.ToolingFacade.Run(BaseRunner runner) at System.Data.Entity.Migrations.Design.ToolingFacade.Update(String targetMigration, Boolean force) at System.Data.Entity.Migrations.UpdateDatabaseCommand.<>c__DisplayClass2.<.ctor>b__0() at System.Data.Entity.Migrations.MigrationsDomainCommand.Execute(Action command) ClientConnectionId:c43cef2f-1614-40cc-a405-ecec90028871 Error Number:4902,State:1,Class:16 Cannot find the object "dbo.AspNetUserClaims" because it does not exist or you do not have permissions. 

This is a remote SQL Server 2014 Express. However, I temporarily made VS / sql SysAdmin my user to find out that this is not a permissions problem ... and all the tables are there, including FK, which seems to explode.

I am tempted to just run the sql script from which I get (on the SQL server itself):

 Update-Database -Script 

And then do:

 Add-Migration InitialCreate –IgnoreChanges 

But then I am afraid of other mistakes and obstacles that I will have to jump. Any help is greatly appreciated.

Answer 1

OnModelCreating is the place to do this, but you also want to use migration for this kind of thing. Having said that, assuming that we are adding changes to our "ApplicationDbContext" (using the default project names) within OnModelCreating :

 protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity<ApplicationUser>().ToTable("Users"); modelBuilder.Entity<IdentityRole>().ToTable("Roles"); modelBuilder.Entity<IdentityUserClaim>().ToTable("UserClaims"); modelBuilder.Entity<IdentityUserLogin>().ToTable("UserLogins"); modelBuilder.Entity<IdentityUserRole>().ToTable("UserRoles"); } 

The Add-Migration AspNetIdentity_RenameTables should generate our migration script for us (it is assumed that there is an existing migration to create identity tables using default naming):

 public partial class AspNetIdentity_RenameTables : DbMigration { public override void Up() { RenameTable(name: "dbo.AspNetRoles", newName: "Roles"); RenameTable(name: "dbo.AspNetUserRoles", newName: "UserRoles"); RenameTable(name: "dbo.AspNetUsers", newName: "Users"); RenameTable(name: "dbo.AspNetUserClaims", newName: "UserClaims"); RenameTable(name: "dbo.AspNetUserLogins", newName: "UserLogins"); } public override void Down() { RenameTable(name: "dbo.UserLogins", newName: "AspNetUserLogins"); RenameTable(name: "dbo.UserClaims", newName: "AspNetUserClaims"); RenameTable(name: "dbo.Users", newName: "AspNetUsers"); RenameTable(name: "dbo.UserRoles", newName: "AspNetUserRoles"); RenameTable(name: "dbo.Roles", newName: "AspNetRoles"); } } 

Now the call to Update-Database takes care of the rest. Obviously, if you installed your DbInitializer using the Migrate initializer, this will be done for you the first time you boot).

Now we have renamed tables:

An example (with commit history) can be found on GitHub: https://github.com/bchristie/AspNetIdentity-RenameTables

---

To add some clarity:

OnModelCreating has its purpose, and to let EF know the relationships, keys, relationships between strings, etc. The Migrations EF function works exceptionally well, taking a step forward and managing the intermediate state of the database, so I would like to use it whenever possible.

Given that IdentityDbContext has its own declarations inside, we need to outperform its configuration by adding OnModelCreating our own (but only after we called the base method). Now we have the entire existing structure, but with a new name.

It is also important to refer to the entities you work with. So, since the project uses ApplicationUser by default, this is what we will reference in modelBuilder.Entity<>() .

Answer 2

If you read the comments above, this is more of a job than an actual solution.

At first (and not related to the above error), part of the code to rename my AspNetUsers table was missing, and therefore there was a necessary line from my onModelCreating function:

 protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity<IdentityUser>().ToTable("Users"); modelBuilder.Entity<ApplicationUser>().ToTable("Users"); //this line needed!! modelBuilder.Entity<IdentityRole>().ToTable("Roles"); modelBuilder.Entity<IdentityUserRole>().ToTable("UserRoles"); modelBuilder.Entity<IdentityUserLogin>().ToTable("UserLogins"); modelBuilder.Entity<IdentityUserClaim>().ToTable("UserClaims"); } 

Even after correcting this code, the need code was generated with the SQL code in the wrong order and still threw the error above, referring to the names of the old table after the table was already renamed. So, I had to manually edit the migration file and move the DropForeignKey methods to the top of the page before the RenameTable methods:

 public override void Up() { DropForeignKey("dbo.AspNetUserClaims", "UserId", "dbo.AspNetUsers"); DropForeignKey("dbo.AspNetUserLogins", "UserId", "dbo.AspNetUsers"); DropForeignKey("dbo.AspNetUserRoles", "UserId", "dbo.AspNetUsers"); RenameTable(name: "dbo.AspNetRoles", newName: "Roles"); RenameTable(name: "dbo.AspNetUserRoles", newName: "UserRoles"); RenameTable(name: "dbo.AspNetUsers", newName: "Users"); RenameTable(name: "dbo.AspNetUserClaims", newName: "UserClaims"); RenameTable(name: "dbo.AspNetUserLogins", newName: "UserLogins"); DropIndex("dbo.UserRoles", new[] { "UserId" }); DropIndex("dbo.UserClaims", new[] { "UserId" }); DropIndex("dbo.UserLogins", new[] { "UserId" }); AddColumn("dbo.UserRoles", "IdentityUser_Id", c => c.String(maxLength: 128)); AddColumn("dbo.Users", "Discriminator", c => c.String(nullable: false, maxLength: 128)); AddColumn("dbo.UserClaims", "IdentityUser_Id", c => c.String(maxLength: 128)); AddColumn("dbo.UserLogins", "IdentityUser_Id", c => c.String(maxLength: 128)); AlterColumn("dbo.UserClaims", "UserId", c => c.String()); CreateIndex("dbo.UserRoles", "IdentityUser_Id"); CreateIndex("dbo.UserClaims", "IdentityUser_Id"); CreateIndex("dbo.UserLogins", "IdentityUser_Id"); AddForeignKey("dbo.UserClaims", "IdentityUser_Id", "dbo.Users", "Id"); AddForeignKey("dbo.UserLogins", "IdentityUser_Id", "dbo.Users", "Id"); AddForeignKey("dbo.UserRoles", "IdentityUser_Id", "dbo.Users", "Id"); } 

After changing this, the Update-Database succeeds.

I watched a video tutorial on this process at www.asp.net/identity and read several Google manuals, and I don’t know why my mistake is, but there are no examples. I guess I'm lucky. But here is my environment, just in case this is a mistake:

• MVC 5.2.3
• EF 6.1.3
• Identity 2.2.1
• VS 2013
• SQL Express 2014

To the next question ... see you soon ...

Answer 3

At first. Rename the ApplicationUser class according to what you want before you can successfully rename your default IdentityUser table.

Since the ApplicationUser class inherits from IdentityUser, and this is what your application really refers directly to user profile issues. Therefore, if it has not changed, the process of modifying the table using the onModelCreating () function related to the user profile will result in link, security, or relationship errors.

Find the attached screenshot of the changes to better understand my point. Thanks, I want it to solve your problem.

 // Note this class is what i changed from ApplicationUser to just User public class User : IdentityUser { public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<User> manager) { .......... codes } } protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); base.OnModelCreating(modelBuilder); // Rather than identityUser generic class , use the name of the renamed ApplicationUser being User modelBuilder.Entity<User>().ToTable("User").Property(x => x.Id).HasColumnName("UserId"); modelBuilder.Entity<IdentityUserRole>().ToTable("UserRole"); modelBuilder.Entity<IdentityUserLogin>().ToTable("UserLogin"); modelBuilder.Entity<IdentityUserClaim>().ToTable("UserClaim").Property(x => x.Id).HasColumnName("ClaimId"); modelBuilder.Entity<IdentityRole>().ToTable("Role").Property(x => x.Id).HasColumnName("RoleId"); } 

Find the attached screenshot of the changes to better understand my point. Thanks, I want it to solve your problem.