EF Core 2 tries to create same table twice

5

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?

entity-framework
entity-framework-core
asked on Stack Overflow Aug 31, 2017 by jao

3 Answers

4

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.

answered on Stack Overflow Nov 18, 2017 by Jesus Mogollon
2

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

answered on Stack Overflow Aug 31, 2017 by Dan Dumitru
0

I had a similar issue, database was created according to initial migration, but a migrations table was empty. So the fix was to manually fill it:

  • MigrationName is located inside _migrationName_.Designer.cs file insite an attribute e.g. [Migration("20210209115216_InitialMigration")]
  • ProductVersion is located in the context's snapshot class e.g. MyDbContextModelSnapshot, there is a row .HasAnnotation("ProductVersion", "5.0.2");

I faced this lag during with an initial migration script, but I assume that it could be reproducible for any migration with a similar fix.

answered on Stack Overflow Feb 12, 2021 by Sergey

User contributions licensed under CC BY-SA 3.0