EF Core 2 tries to create same table twice

entity-framework entity-framework-core

Question

I have a project using EF Core 2. I created a migration. When running the migration it returns with the following error:

infoinfo:    System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'Clients' in the database.
   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, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:718d9d47-c2e4-4bf5-9e15-165884a5ff11
Error Number:2714,State:6,Class:16
infoerror:   There is already an object named 'Clients' in the database.
: Microsoft.EntityFrameworkCore.Database.Command[200101]
  Executed DbCommand (8ms) [Parameters=[@p0='?' (Size = 400), @p1='?' (Size = 400), @p2='?' (Size = 4000), @p3='?', @p4='?', @p5='?' (Size = 400), @p6='?' (Size = 400), @p7='?' (Size = 4000), @p8='?', @p9='?', @p10='?' (Size = 400), @p11='?' (Size = 400), @p12='?' (Size = 4000), @p13='?', @p14='?', @p15='?' (Size = 400), @p16='?' (Size = 400), @p17='?' (Size = 4000), @p18='?', @p19='?'], CommandType='Text', CommandTimeout='30']
  SET NOCOUNT ON;
  DECLARE @inserted0 TABLE ([Id] int, [_Position] [int]);
  MERGE [Options] USING (
  VALUES (@p0, @p1, @p2, @p3, @p4, 0),
  (@p5, @p6, @p7, @p8, @p9, 1),
  (@p10, @p11, @p12, @p13, @p14, 2),
  (@p15, @p16, @p17, @p18, @p

This is my DbContext:

namespace MyProject.Data
{
    public class MyProjectContext : DbContext
    {
        public MyProjectContext(DbContextOptions<MyProjectContext> options)
            : base(options)
        {
        }

        public DbSet<ExtensionBasicQuotation> ExtensionBasicQuotations { get; set; }
        public DbSet<ExtensionSpecifiedQuotation> ExtensionSpecifiedQuotations { get; set; }
        public DbSet<Client> Clients { get; set; }
        public DbSet<Option> Options { get; set; }
        public DbSet<SpecifiedQuotationAttachment> SpecifiedQuotationAttachments { get; set; }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            builder.ApplyConfiguration(new ClientConfiguration());
            builder.ApplyConfiguration(new ExtensionBasicQuotationConfiguration());
            builder.ApplyConfiguration(new ExtensionSpecifiedQuotationConfiguration());
            builder.ApplyConfiguration(new OptionConfiguration());
        }
    }
}

There is only one migration and the migration has only one CreateTable function for the Clients table.

migrationBuilder.CreateTable(
                name: "Clients",
                columns: table => new
                {
                    Id = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                    Address = table.Column<string>(type: "nvarchar(500)", maxLength: 500, nullable: true),
                    City = table.Column<string>(type: "nvarchar(200)", maxLength: 200, nullable: true),
                    Email = table.Column<string>(type: "nvarchar(200)", maxLength: 200, nullable: true),
                    Name = table.Column<string>(type: "nvarchar(200)", maxLength: 200, nullable: true),
                    Phone = table.Column<string>(type: "nvarchar(20)", maxLength: 20, nullable: true),
                    Postcode = table.Column<string>(type: "nvarchar(20)", maxLength: 20, nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Clients", x => x.Id);
                });

Why is the Clients table created twice?

1
5
8/31/2017 9:09:50 AM

Accepted Answer

If this migration is the InitialCreate migration, it seems you shouldn't run it if you already have a database, it should be run only for creating a new database.

If you created the initial migration when the database already exists, the database creation code is generated but it doesn't have to run because the database already matches the data model. When you deploy the app to another environment where the database doesn't exist yet, this code will run to create your database

http://docs.microsoft.com/en-us/aspnet/core/data/ef-mvc/migrations

2
8/31/2017 9:27:08 AM

Popular Answer

I found the same issue with EF Core 2.0

The problem shows up when creating a Seeder class and inside the seed method you have:

_ctx.Database.EnsureCreated();

Then if you add a migration (Initial or one with more tables) and execute the database update, the seeder will try to create the database first (but somehow not the version record in the history?) and then the update will try again.

Anyhow, commenting out the previous line in the seed method fixed the problem.



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