Renaming Identity tables with EF6 Migrations Failing

asp.net-mvc entity-framework entity-framework-6 sql-server

Question

I'm attempting to rename my Identity 2.0 tables using EF6's Migrations tool. However, halfway through, it starts to blow apart. In IdentityModels.cs, after the "ApplicationDBContext Create," I'm just calling the following line of code:

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

The following error then blows:

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.

The SQL Server 2014 Express is remote. To be sure it wasn't a permissions issue, I briefly made my VS/sql user a SysAdmin, but all the tables are there, including the FK it seems to blow up on.

I'm tempted to simply execute the sql script I download from the actual SQL server:

Update-Database -Script

then perform:

Add-Migration InitialCreate –IgnoreChanges

But then I worry that I'll make more mistakes and encounter obstacles. I sincerely appreciate any assistance.

1
8
5/8/2015 2:17:48 AM

Accepted Answer

OnModelCreating is where you should do it, however you should also use migrations for this sort of stuff. Having said that, supposing we implement the modifications to "ApplicationDbContext" in our insideOnModelCreating :

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 produce our migration script on our behalf (this presumes there is already a migration to construct the identity tables with 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");
    }
}

calling nowUpdate-Database the rest is taken care of. Naturally, this would be carried out for you on the first load if you had configured your DbInitializer using the Migrate initializer.

The tables have been renamed as of now:

Database Screenshot

On GitHub, you may find a sample (with a commit history) at https://github.com/bchristie/AspNetIdentity-RenameTables.


To further clarify:

OnModelCreating , which is to inform EF of relationships, keys, entity-to-table linkages, etc. I prefer to use the Migrations function of EF whenever feasible because it excels at taking things a step further and handling the in-between states of the database.

Due to the fact that the IdentityDbContext contains its own declarations, we must override its configuration byOnModelCreating until after we have called the basic method, however, of our own. We still have the same structure as before, but with a different name.

References to the entities you are working with are also crucial. Given that the default project makes use of anApplicationUser This is what we would mention inmodelBuilder.Entity<>() .

7
5/8/2015 8:58:20 PM

Popular Answer

If you read the comments up there, you'll see that this is more of a workaround than a real answer.

First (and unrelated to the mistake above), a crucial line from my code that was used to rename my AspNetUsers table was missing.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 that was fixed, the appropriate migrations code was still being produced using the incorrect SQL order and throwing the aforementioned issue, referring the old table names after the table had already undergone a renaming. Therefore, I had to explicitly edit the migration file and relocate theDropForeignKey approaches to the top beforeRenameTable 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 doing itUpdate-Database ran effectively.

I read various google leads on it and watched a video explanation of this process on www.asp.net/identity, but I still don't understand why my version is failing while the examples succeed. I guess I'm fortunate. But just in case it's a bug, here is my environment:

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

Let's move on to the next topic; we'll see you shortly.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow