Entity Framework Core Error: dotnet.exe : System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'Company' in the database

Question

I am building a application with ASP.NET Core MVC6 and Entity Framework Core code first with build in DB context, the SQL database has already been populated with data records. I recently made some small changes to the data models and recreate the migration, with commands as ("dotnet ef migrations add Stage3", "dotnet ef database update") in VS 2015 Package Manager Console, but it ran into error as:

dotnet.exe : System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'Company' in the database.

Company table is on the top of the tables relationship, it seems that because the table Company is already there and the EF can not update the new table structure. If I change DB name in the connection string, it will create new database with new table structure without any issues. I am not sure how to address this issue? After the application go live in the near future I will properly make more changes to the Modes and will have same issue again and I cannot delete database with live data to recreate new table structure, Maybe I should configure it in the Startup.cs file, but I haven't found any useful resources yet. Please give me some advises.

I have attempted to change the DB Initializer as attached screenshot, but not sure how to do it.

I checked the project code again, the migration has not been applied to __MigrationHistory table, the migration code actually contained the code to create whole database structure as sample below:

 migrationBuilder.CreateTable(
            name: "Company",
            columns: table => new
            {
                CompanyId = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                CompanyName = table.Column<string>(maxLength: 100, nullable: false),
                IsAdmin = table.Column<bool>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Company", x => x.CompanyId);
            });

And I haven't changed the project namespace. Recently I just made some changes on few table relationships such as site user permission table(company has many sites). I added a permission table, so now site user permission table can have multiple permissions type instead of single permission type.

Not sure how to set up automatic migrations in Entity framework core.

Answer 1

In Entity framework code first approch, there are four different database initialization strategies:

• CreateDatabaseIfNotExists: This is default initializer. As the name suggests, it will create the database if none exists as per the configuration. However, if you change the model class and then run the application with this initializer, then it will throw an exception.

• DropCreateDatabaseIfModelChanges: This initializer drops an existing database and creates a new database, if your model classes (entity classes) have been changed. So you don't have to worry about maintaining your database schema, when your model classes change.

• DropCreateDatabaseAlways: As the name suggests, this initializer drops an existing database every time you run the application, irrespective of whether your model classes have changed or not. This will be useful, when you want fresh database, every time you run the application, like while you are developing the application.

• Custom DB Initializer: You can also create your own custom initializer, if any of the above doesn't satisfy your requirements or you want to do some other process that initializes the database using the above initializer.

So if you are using DropCreateDatabaseIfModelChanges or DropCreateDatabaseAlways then replace it with CreateDatabaseIfNotExists.

Please try this out.