Efcore: There is already an object named 'AspNetRoles' in the database.

[dewebeloper]

Strange issue. I'm able to crate migrations, but when I want to update database I get
There is already an object named 'AspNetRoles' in the database.
Here is stack after my line ctx.Database.Migrate();

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 Microsoft.Data.Entity.Storage.Internal.RelationalCommand.<>c.<ExecuteNonQuery>b__13_0(DbCommand cmd, IRelationalConnection con)
   at Microsoft.Data.Entity.Storage.Internal.RelationalCommand.Execute[T](IRelationalConnection connection, Func`3 action, String executeMethod, Boolean openConnection, Boolean closeConnection)
   at Microsoft.Data.Entity.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, Boolean manageConnection)
   at Microsoft.Data.Entity.Storage.RelationalCommandExtensions.ExecuteNonQuery(IEnumerable`1 commands, IRelationalConnection connection)
   at Microsoft.Data.Entity.Migrations.Internal.Migrator.Execute(IEnumerable`1 relationalCommands)
   at Microsoft.Data.Entity.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.Data.Entity.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)

and all my dependencies

"dependencies": {
        "Microsoft.AspNet.IISPlatformHandler": "1.0.0-rc1-final",
        "Microsoft.AspNet.Server.Kestrel": "1.0.0-rc1-final",
        "Microsoft.AspNet.StaticFiles": "1.0.0-rc1-final",
        "Microsoft.AspNet.Hosting": "1.0.0-rc1-final",
        "Microsoft.AspNet.Mvc": "6.0.0-rc1-final",
        "Microsoft.AspNet.Diagnostics": "1.0.0-rc1-final",
        "WhiteBee.CentralConfig.Data": "1.0.0-*",
        "EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
        "EntityFramework.SqlServerCompact40": "7.0.0-rc1-final",
        "EntityFramework.Commands": "7.0.0-rc1-final",
        "Microsoft.AspNet.Identity": "3.0.0-rc1-final",
        "Microsoft.AspNet.Authentication.JwtBearer": "1.0.0-rc1-final",
        "AspNet.Security.OpenIdConnect.Server": "1.0.0-beta4",
        "AspNet.Security.OpenIdConnect.Extensions": "1.0.0-beta4",
        "Microsoft.AspNet.Security.DataProtection": "1.0.0-beta3",
        "WhiteBee.CentralConfig.Repo": "1.0.0-*",
        "Microsoft.AspNet.Mvc.TagHelpers": "6.0.0-rc1-final",
        "Microsoft.Owin.Security": "3.0.1"
    },

[rowanmiller]

Can you check the contents of your __MigrationsHistory table - it should contain an entry for 00000000000000_CreateIdentitySchema saying that this migration has already been applied. That's the migration that is dropped in your project when you create it, and is the one that creates AspNetRoles etc.

Also, can you just confirm that the new migration you created only contains code to apply the changes you made to the model, and is not trying to recreate the objects from the existing 00000000000000_CreateIdentitySchema migration.

[dewebeloper]

Thing is, when I crate migration for first time, I don't have __MigrationsHistory in database, even when i crate second migration I don't have __MigrationsHistory in dataabse.
But after I run from code ctx.Database.Migrate() then I can see __EFMigrationsHistory, but it's empty.

Also, when I create second migration, I have all this in migration file, but only NewProp is newly added property.

public partial class SecondMigration : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropForeignKey(name: "FK_IdentityRoleClaim<string>_IdentityRole_RoleId", table: "AspNetRoleClaims");
            migrationBuilder.DropForeignKey(name: "FK_IdentityUserClaim<string>_User_UserId", table: "AspNetUserClaims");
            migrationBuilder.DropForeignKey(name: "FK_IdentityUserLogin<string>_User_UserId", table: "AspNetUserLogins");
            migrationBuilder.DropForeignKey(name: "FK_IdentityUserRole<string>_IdentityRole_RoleId", table: "AspNetUserRoles");
            migrationBuilder.DropForeignKey(name: "FK_IdentityUserRole<string>_User_UserId", table: "AspNetUserRoles");
            migrationBuilder.DropForeignKey(name: "FK_CompanyUser_Company_CompanyId", table: "CompanyUser");
            migrationBuilder.DropForeignKey(name: "FK_CompanyUser_User_UserId", table: "CompanyUser");
          **  migrationBuilder.AddColumn<string>(
                name: "NewProp",
                table: "Company",
                nullable: true);**
            migrationBuilder.AddForeignKey(
                name: "FK_IdentityRoleClaim<string>_IdentityRole_RoleId",
                table: "AspNetRoleClaims",
                column: "RoleId",
                principalTable: "AspNetRoles",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);
            migrationBuilder.AddForeignKey(
                name: "FK_IdentityUserClaim<string>_User_UserId",
                table: "AspNetUserClaims",
                column: "UserId",
                principalTable: "User",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);
            migrationBuilder.AddForeignKey(
                name: "FK_IdentityUserLogin<string>_User_UserId",
                table: "AspNetUserLogins",
                column: "UserId",
                principalTable: "User",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);
            migrationBuilder.AddForeignKey(
                name: "FK_IdentityUserRole<string>_IdentityRole_RoleId",
                table: "AspNetUserRoles",
                column: "RoleId",
                principalTable: "AspNetRoles",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);
            migrationBuilder.AddForeignKey(
                name: "FK_IdentityUserRole<string>_User_UserId",
                table: "AspNetUserRoles",
                column: "UserId",
                principalTable: "User",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);
            migrationBuilder.AddForeignKey(
                name: "FK_CompanyUser_Company_CompanyId",
                table: "CompanyUser",
                column: "CompanyId",
                principalTable: "Company",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);
            migrationBuilder.AddForeignKey(
                name: "FK_CompanyUser_User_UserId",
                table: "CompanyUser",
                column: "UserId",
                principalTable: "User",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropForeignKey(name: "FK_IdentityRoleClaim<string>_IdentityRole_RoleId", table: "AspNetRoleClaims");
            migrationBuilder.DropForeignKey(name: "FK_IdentityUserClaim<string>_User_UserId", table: "AspNetUserClaims");
            migrationBuilder.DropForeignKey(name: "FK_IdentityUserLogin<string>_User_UserId", table: "AspNetUserLogins");
            migrationBuilder.DropForeignKey(name: "FK_IdentityUserRole<string>_IdentityRole_RoleId", table: "AspNetUserRoles");
            migrationBuilder.DropForeignKey(name: "FK_IdentityUserRole<string>_User_UserId", table: "AspNetUserRoles");
            migrationBuilder.DropForeignKey(name: "FK_CompanyUser_Company_CompanyId", table: "CompanyUser");
            migrationBuilder.DropForeignKey(name: "FK_CompanyUser_User_UserId", table: "CompanyUser");
            migrationBuilder.DropColumn(name: "NewProp", table: "Company");
            migrationBuilder.AddForeignKey(
                name: "FK_IdentityRoleClaim<string>_IdentityRole_RoleId",
                table: "AspNetRoleClaims",
                column: "RoleId",
                principalTable: "AspNetRoles",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);
            migrationBuilder.AddForeignKey(
                name: "FK_IdentityUserClaim<string>_User_UserId",
                table: "AspNetUserClaims",
                column: "UserId",
                principalTable: "User",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);
            migrationBuilder.AddForeignKey(
                name: "FK_IdentityUserLogin<string>_User_UserId",
                table: "AspNetUserLogins",
                column: "UserId",
                principalTable: "User",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);
            migrationBuilder.AddForeignKey(
                name: "FK_IdentityUserRole<string>_IdentityRole_RoleId",
                table: "AspNetUserRoles",
                column: "RoleId",
                principalTable: "AspNetRoles",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);
            migrationBuilder.AddForeignKey(
                name: "FK_IdentityUserRole<string>_User_UserId",
                table: "AspNetUserRoles",
                column: "UserId",
                principalTable: "User",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);
            migrationBuilder.AddForeignKey(
                name: "FK_CompanyUser_Company_CompanyId",
                table: "CompanyUser",
                column: "CompanyId",
                principalTable: "Company",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);
            migrationBuilder.AddForeignKey(
                name: "FK_CompanyUser_User_UserId",
                table: "CompanyUser",
                column: "UserId",
                principalTable: "User",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);
        }
    }

[rowanmiller]

@dewebeloper recreating all the foreign keys like that was a bug in RC1 which has been fixed in our working code base and will be fixed in the RC2 release.

Sounds like you may have a call to context.Database.EnsureCreated() somewhere in your code base? This would cause the schema to be created without migrations... and then migrations tries to create the same schema for you.

[dewebeloper]

Yes, I have call to context.Database.EnsureCreated(). Will remove that I try again.

[rowanmiller]

Closing as I am confident that the EnsureCreated() is the problem. Feel free to reply if it does not solve the issue and I will reopen.

[vsevolodsa]

Yes, I have call to context.Database.EnsureCreated(). Will remove that I try again.

How do you call it?

[jeyara]

@vsevolodsa You can call it inside the constructor of your dbContext

[sethen]

I had the same issue -- removing the context.Database.EnsureCreated() fixed the error message and the migrations sailed through.

[axellejamous]

I removed my context.Database.EnsureCreated() call but still get the error.. anyone else?

[nixxholas]

I removed my context.Database.EnsureCreated() call but still get the error.. anyone else?

I second to that @axellejamous

[sethen]

@axellejamous Do you have migrations? I feel like this could be an issue of having the database seeding happening first (which creates the table structures in the database) and then trying to run the migrations second. I think this was related to my issue as well. If you run the seeding first and then the migrations you could get this error.

Instead, run the migrations first and then use dotnet build && dotnet run to seed the database. This is my guess as to what might be occurring on your end.

[axellejamous]

@sethen sadly, I don't really know what was causing my issue in the end. After manually dropping every table in my database, removing all of my migrations and restarting process a few times it just worked and never stopped working since then. Weird.

[sethen]

@axellejamous Hmm... Well, at least you got it figured out!

[kenhan168]

@axellejamous, I guess the down scripts created by migration may not always work. E.g. I added a Model: ABC, then create a migration, say MigrateAddABC. Then make more changes, create more migrations. Then delete model: ABC, and create another migration , say MigrateDropABC.

Then delete the database and run Update-Database, then I have had some strange error.

And the only way to resolve it was like what you did, delete all the migrations and add them at one migration script.

[axellejamous]

@kenhan168 Indeed. I've also noticed recently that sometimes the database will update eventually but that it just seems to take random amounts of time. I'll check the database every hour or so to sometimes find the updates going through succesfully without doing the whole remove thing, which is just very strange.

[mo-norant]

@axellejamous grappig da ik u net hier tegen kom :p

[axellejamous]

@mo-norant haha idd! zelfde issue of?

[vinhloc1996]

I have exactly the same issue with the EF core. I also remove the EnsureCreated() function and rebuild the project but the error still there. After one day, I run again the update-database and it works fine. I really don't know what kind of this error.
So the solution may be: Remove the method EnsureCreated() (if any), clean and build the project then try the update-database, if it still not working, restart the computer and try to do the same thing and you should see the magic.

[andrewDubyk]

I had the same problem and the comment above helped me also I removed my existing database before.
@vinhloc1996 , thanks

[CKGrafico]

We have found a solution.
Execute the command 'Script-Migration' and copy this part

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'XXXXXX_NAME', N'2.0.2-rtm-10011');

GO

(or similar)

Execute this script in you SQL database and thats it

[sebasijan]

@CKGrafico But there are so many of those - how do I know which one I need to execute?

EDIT Ok I ran ALL of them and that seems to have worked

[CKGrafico]

@sebasijan yes, one for each migration , happy you solved it too :D was a lot of time to me to understand the problem haha

[colinwphillips]

Thanks @vinhloc1996 this resolved my issue in .NET Core v2.2

[colinwphillips]

Odd given the application has been compiling just fine...

[arfrix]

i had same problem and after three hour struggling i find out what's going on

in my case when i wanted to migrate for the first time in up() method the default code wants to create the tables that already existed so i got same error as you

to solve it , just delete dose code and write want you want . for example i wanted to add a column so i just write

migrationBuilder.AddColumn( name: "fieldName", table: "tableName", nullable: true);

[mspace23]

We have found a solution.
Execute the command 'Script-Migration' and copy this part

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'XXXXXX_NAME', N'2.0.2-rtm-10011');

GO

(or similar)

Execute this script in you SQL database and thats it

Hello,

You save my day

[mackoh]

@sethen you said:
"sadly, I don't really know what was causing my issue in the end. After manually dropping every table in my database, removing all of my migrations and restarting process a few times it just worked and never stopped working since then. Weird."

I was having the same issue and dropped my tables, which made the problem go away. I do not have an answer as to why, but it is now working.

[3263927]

I had local database and remote, same migrations. i got this error when remote _EFMigrationshistory table was not the same as local. i copied record from local and pasted to remote, after that i updated database, all worked