Renaming Identity tables with EF6 Migrations Failing

Question

I'm trying to rename my Identity 2.0 tables via the Migrations tool in EF6/Package Manager. However, it's blowing up a part of the way through. I'm simply calling the following piece of code after the "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");
        }

It then blows 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: x.x.x.x, 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.

It's a remote SQL Server 2014 Express. However, I temporarily made my VS/sql user a SysAdmin to know it wasn't a permissions problem....and the tables are all there, including the FK it seems to blow up on.

I'm tempted to just run the sql script I get from(on the actual SQL server):

Update-Database -Script

And then do an:

Add-Migration InitialCreate –IgnoreChanges

But, then I fear other errors and hurdles I'll have to jump. Any help is much appreciated.

Arce

Looks like you need to drop FK relationships before renaming (then re-apply them using new table names). -- although I thought sp_rename did this for you... – Pachston

Thanks, yeah, I'm leery to get the EF migrations tool out of whack by tweaking the SQL manually. I'm having a real battle with this thing. However, I think it's creating the SQL commands in the wrong order. So, my best guess is that it doesn't see that constraint in the AspNetUserClaims table, because at the point when it tries to drop the constraint that table has already been renamed to just UserClaims. Therefore, AspNetUserClaims "does not exist"... which means I'll have to intervene manually as I don't see a way around it... ? – Arce

You said you're using Migrations, may try modifying that specific migration (instead of using onModelCreating) then applying Update-Database. – Pachston

Ok, thanks, I see where I can do that now and will give it a try... I just realized as well that the sp_rename, etc for the AspNetUsers table is all missing up top... and then it references the actual "AspNetUsers" table later in the sql/migration code. So, I must have that Identity mapping incorrect or something. Anyway, thanks again, I'll see if I can't figure out a work around and post back later... – Arce

Good luck. But overall I would say stick with keeping migrations within the migration class. On model creating is good for one offs, but becomes compounded when you're looking at multiple deltas. – Pachston

Thanks, I'll definitely heed that advice. However, in this case, onModelCreating seems to be the only examples out there for renaming and mapping those Identity tables? This is all pretty new to me, so I'm still doing a lot of shooting in the dark... while trying not to shoot myself in the foot at the same time... – Arce

Answer 1

OnModelCreating is the place to do it, but you also want to leverage migrations for this kind of thing. Having said that, assuming we add the changes to our "ApplicationDbContext" (using 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");
}

Calling Add-Migration AspNetIdentity_RenameTables should generate our migration script for us (this assumes an existing migration exists to create the identity tables using the 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 calling Update-Database takes care of the rest. Obviously, if you had setup your DbInitializer using the Migrate initializer, this would be done for you on first load).

Now, we have the 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 that's to let EF know of relationships, keys, entity-to-table relationships, etc. The Migrations feature of EF works exceptionally well at taking it a step further and managing the in-between states of the database, so I like to leverage it whenever possible.

Given the IdentityDbContext has its own declarations within, we need to trump its configuration by adding an OnModelCreating of our own (but only after we've called the base method). Now we have all the existing structure, but with a new name.

It's also important to reference the entities you're working with. So, since the default project uses an ApplicationUser, this is what we'd reference in modelBuilder.Entity<>().

Pachston

Excellent write up, thank you! So, looks like I don't even need the modelBuilder.Entity<IdentityUser>().ToTable("Users"); line, which would have avoided the DropForeignKey Methods. I'm going to start again from scratch and see if I can't have a little smoother go of it this time. I really appreciate the help and info. – Arce

Answer 2

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

First (and not related to the error above), part of the code to rename my AspNetUsers table was missing and therefore, so 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 that, the necessary migrations code was being generated with the SQL code in the wrong order and still throwing the error above, referencing the old table names after the table had already been renamed. So, I had to edit the migration file directly and move the DropForeignKey methods to the top ahead of 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");
        }

Upon changing that Update-Database ran successfully.

I watched a video tutorial of this process on www.asp.net/identity and read several google leads on it and I don't really know why mine is erroring and the examples are not. I guess I'm lucky. But, here is my environment, just in case it's a bug:

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

On to the next issue... see you guys soon...

Arce

Answer 3

Firstly. Rename the ApplicationUser class to what you wish since before you can successfully rename your default IdentityUser table.

Because the ApplicationUser class inherits from IdentityUser and is what your application actually directly references for User profile concerns. Therefore if its not changed, the table changing process using the onModelCreating() function that concerns the user profile would give reference,security or relationship errors.

find the attached screenshot of changes to understand my point more. thanks I wish it solves 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 changes to understand my point more. thanks I wish it solves your problem.

Huskamp