How skip some migrations in ef core?

Question

I had some changes in my ef context and I added new Migraions

dotnet ef migrations add changed98112601

then I run the below command

dotnet ef database update

I have the following output. It seems that want to apply Initial migrations. The Poems table is in database because it was created by applying Initial migrations. How should I prevent this? How should I skip Initial migrations when I want to update?

Build started...
Build succeeded.
Applying migration '20191225133128_Initial'.
Failed executing DbCommand (159ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [Poems] (
    [Id] int NOT NULL IDENTITY,
    [Number] int NOT NULL,
    [Meaning] nvarchar(max) NULL,
    CONSTRAINT [PK_Poems] PRIMARY KEY ([Id])
);
Microsoft.Data.SqlClient.SqlException (0x80131904): There is already an object named 'Poems' in the database.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1591
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlInternalConnection.cs:line 618
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\TdsParser.cs:line 1169
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\TdsParser.cs:line 1719
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite) in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 2857
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName) in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 1395
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery() in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 974
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   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.UpdateDatabaseImpl(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:b1027e70-d231-407a-87a0-5b53d06d2782
Error Number:2714,State:6,Class:16
There is already an object named 'Poems' in the database.

Looks like you are updating the database and there is already an object Poems in the database. — jdweng, Feb 15 '20 at 11:49
yes.the Poems table is in database.because it was created by applying Initioal migraions — mohsen, Feb 15 '20 at 12:09
Was the poems table part of a dotnet ef migration too? Could you manually add that migration to the __EFMigrationsHistory table? — nickfinity, Feb 15 '20 at 12:30
Have you try this ? https://stackoverflow.com/questions/3600175/the-model-backing-the-database-context-has-changed-since-the-database-was-crea/6143116#6143116 — Tony Ngo, Feb 15 '20 at 23:24
You could always walk around it that by changing the generated codes within your migration file under `Migrations/` folder: Remove the related codes of `CreateTable(name:'Poems',...)` within `Up()` methods. Note: if you want to do this, please sync the changes when you change the database manually in future. — itminus, Feb 17 '20 at 07:20

Patrick Koorevaar · Answer 1 · 2022-04-20T16:28:16.577

I know this question is asked 2 years ago, so I guess mohsen doesn't need it anymore, but maybe it is useful for somebody else.

In my situation I had to check if a table already existed at the point of executing the migration scripts because the first migration script was not executed and therefor not present in the __EFMigrationsHistory table. The table from the first migration script was present in the database (maybe created manually). I wanted to keep the migrations intact, so when the database is created from scratch, the migrations are executed as normally, if the table already exists then first migration script is skipped.

Updated to your situation:

public class DbInitializer
{
    private const string CreateMigrationsTable = @"
                    IF NOT EXISTS(SELECT 1 FROM [INFORMATION_SCHEMA].[TABLES]
                    WHERE TABLE_NAME = '__EFMigrationsHistory')
                    CREATE TABLE [dbo].[__EFMigrationsHistory](
                        [MigrationId] [nvarchar](150) NOT NULL,
                        [ProductVersion] [nvarchar](32) NOT NULL,
                     CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY CLUSTERED 
                    (
                        [MigrationId] ASC
                    )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
                    ) ON [PRIMARY]
                ";
                
    private const string InsertInitialMigration = @"
                    IF EXISTS(SELECT 1 FROM [INFORMATION_SCHEMA].[TABLES]
                    WHERE TABLE_NAME = 'Poems')
                    INSERT INTO [dbo].[__EFMigrationsHistory] (MigrationId, ProductVersion)
                    VALUES ('20191225133128_Initial', '5.0.8')
                ";
    private const string InsertSecondMigration = @"
                    IF EXISTS(SELECT 1 FROM [INFORMATION_SCHEMA].[TABLES]
                    WHERE TABLE_NAME = 'Poems')
                    INSERT INTO [dbo].[__EFMigrationsHistory] (MigrationId, ProductVersion)
                    VALUES ('20220420132739_Second', '5.0.8')
                ";

    public static void Initialize(DbContext context)
    {
        var pendingMigrations = context.Database.GetPendingMigrations().ToList();

        if (pendingMigrations.Any())
        {                
            if (pendingMigrations.Contains("20191225133128_Initial"))
            {
                // If the tabel doesn't exists, it needs to be created
                if (context.Database.EnsureCreated())
                {                                                
                    context.Database.ExecuteSqlRaw(CreateMigrationsTable);

                    // If it is created all migrations need to be added in the migrations table
                    context.Database.ExecuteSqlRaw(InsertInitialMigration);
                    context.Database.ExecuteSqlRaw(InsertSecondMigration);
                }
                else
                {
                    context.Database.ExecuteSqlRaw(CreateMigrationsTable);
                    context.Database.ExecuteSqlRaw(InsertInitialMigration);
                }
            }

            // If the table was already there, a call to Migrate will add the second migration
            context.Database.Migrate();                
        }            
    }
}

How skip some migrations in ef core?

1 Answers1