There is already an object named 'AspNetRoles' in the database

Question

Some time ago, I created a ASP.NET MVC 5 website with the Identity 1.0 version, and i created the Identity tables with this project. Now i have to make other website using the same database for authentication, but now the Identity version is 2.0. So when i try to authenticate in the new website i get some errors.

Im trying to migrate the database using the Migrations approach, but its confused and im getting this error There is already an object named 'AspNetRoles' in the database. when i type Update-Database in the PM console.

My question is, how is the best way to use the same database for the authetication of both sites (one using the 1.0 identity version and other using 2.0). Do I really need to migrate the database?

If yes, how can i solve this error that im getting?

Answer 1

Add-Migration InitialMigrations -IgnoreChanges

This should generate a blank "InitialMigration" file. Now, add any desired changes to the class you want. Once changes are added, run the update command again:

update-database -verbose

Now the automatic migration will be applied and the table will be altered with your changes.

Edit: Here is a solution to migrate identity 1 to 2 Upgrading from ASP.NET.Identity 1.0 to 2.0 Use this manual migration

public override void Up()
    {
        RenameColumn(table: "dbo.AspNetUserClaims", name: "User_Id", newName: "UserId");
        RenameIndex(table: "dbo.AspNetUserClaims", name: "IX_User_Id", newName: "IX_UserId");
        DropPrimaryKey("dbo.AspNetUserLogins");
        AddColumn("dbo.AspNetUsers", "Email", c => c.String(maxLength: 256));
        AddColumn("dbo.AspNetUsers", "EmailConfirmed", c => c.Boolean(nullable: false));
        AddColumn("dbo.AspNetUsers", "PhoneNumber", c => c.String()); 
        AddColumn("dbo.AspNetUsers", "PhoneNumberConfirmed", c => c.Boolean(nullable: false));
        AddColumn("dbo.AspNetUsers", "TwoFactorEnabled", c => c.Boolean(nullable: false));
        AddColumn("dbo.AspNetUsers", "LockoutEndDateUtc", c => c.DateTime());
        AddColumn("dbo.AspNetUsers", "LockoutEnabled", c => c.Boolean(nullable: false));
        AddColumn("dbo.AspNetUsers", "AccessFailedCount", c => c.Int(nullable: false));
        AlterColumn("dbo.AspNetUsers", "UserName", c => c.String(nullable: false, maxLength: 256));
        AlterColumn("dbo.AspNetUsers", "FirstName", c => c.String(nullable: false));
        AlterColumn("dbo.AspNetUsers", "LastName", c => c.String(nullable: false));
        AddColumn("dbo.AspNetUsers", "CreatedDateTime", c => c.DateTime(nullable: false));
        AlterColumn("dbo.AspNetRoles", "Name", c => c.String(nullable: false, maxLength: 256));
        AddPrimaryKey("dbo.AspNetUserLogins", new[] { "LoginProvider", "ProviderKey", "UserId" });
        CreateIndex("dbo.AspNetUsers", "UserName", unique: true, name: "UserNameIndex");
        CreateIndex("dbo.AspNetRoles", "Name", unique: true, name: "RoleNameIndex");
        DropColumn("dbo.AspNetUsers", "Discriminator");
    } 

Answer 2

While you can (since EF6) use migrations in two separate projects for the same database, there can't be any overlap. The way migrations work is through a dbo._MigrationHistory table that stores the context that generated the migration and the model state of your application, which includes the Identity models.

When you try to connect your second application, it finds no previous migrations, and thus needs to generate it's initial migration, which will include tables for the Identity models, which are in its context as well. That's where your problem is.

For the purposes of Identity, you need to choose one project to make the master. This one will utilize a standard IdentityDbContext where the Identity models will be migrated.

The other project will need to be made a slave, at least in terms of utilizing Identity. So, you will need to interact with at least two contexts in this application. One will be a subclass of IdentityDbContext, but treated as database-first:

public class MyIdentityContext : IdentityDbContext<ApplicationUser>
{
    public MyIdentityContext()
        : base("ConnectionStringNameForYourSharedDB")
    {
        Database.SetInitializer<MyIdentityContext>(null);
    }
}

The other context will be just a regular DbContext subclass that will be migrated as normal. You'll need to repeat this for any other project that may need access to the same Identity information from the same database. Also, because of the repetitious code this will lead to (and the fact that your ApplicationUser class will need to be shared) you should move this code into a class library that each project can reference.

Answer 3

In the "appsettings.json" file change the name of database

"ConnectionStrings": {
    "DefaultConnection": "Server=DESKTOP-S0S1I;Database=New_name_here;Trusted_Connection=True;"

and then

add-migration 
update-database

It works.