Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Migrations command in incorrect order when dealing with sequences #33130

Open
imaa opened this issue Feb 21, 2024 · 1 comment
Open

Migrations command in incorrect order when dealing with sequences #33130

imaa opened this issue Feb 21, 2024 · 1 comment

Comments

@imaa
Copy link

imaa commented Feb 21, 2024

Using the HasSequance API

            modelBuilder.HasSequence<int>("OrderNumbers").StartsAt(1);
            modelBuilder.Entity<Person>().Property(o => o.Seq).HasDefaultValueSql("NEXT VALUE FOR OrderNumbers");

Generated Migrations for creating the sequance

         public partial class init3 : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateSequence<int>(
                name: "OrderNumbers");

            migrationBuilder.AddColumn<int>(
                name: "Seq",
                table: "Persons",
                type: "int",
                nullable: false,
                defaultValueSql: "NEXT VALUE FOR OrderNumbers");
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropColumn(
                name: "Seq",
                table: "Persons");

            migrationBuilder.DropSequence(
                name: "OrderNumbers");
        }
    }

when trying to remove the sequance

    public partial class init4 : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {

            migrationBuilder.DropSequence(
                name: "OrderNumbers");
                
            migrationBuilder.AlterColumn<int>(
                name: "Seq",
                table: "Persons",
                type: "int",
                nullable: false,
                oldClrType: typeof(int),
                oldType: "int",
                oldDefaultValueSql: "NEXT VALUE FOR OrderNumbers");

        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateSequence<int>(
                name: "OrderNumbers");

            migrationBuilder.AlterColumn<int>(
                name: "Seq",
                table: "Persons",
                type: "int",
                nullable: false,
                defaultValueSql: "NEXT VALUE FOR OrderNumbers",
                oldClrType: typeof(int),
                oldType: "int");
        }
    }

The issue ef core tries to drop the sequence before altering the column.

I KNOW that I can change the order in the migration file but I can't because my case is more complex and I'm using Run time migration that can be generated by the user.

Stack trace

Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DROP SEQUENCE [OrderNumbers];
Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot DROP SEQUENCE 'OrderNumbers' because it is being referenced by object 'DF__Persons__Seq__5BE2A6F2'.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   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 connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:70ae49aa-a3d1-4f72-9df6-5cb955906070
Error Number:3729,State:1,Class:16
Cannot DROP SEQUENCE 'OrderNumbers' because it is being referenced by object 'DF__Persons__Seq__5BE2A6F2'.

EF Core version:7.0.14
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 11 22H2
IDE: Visual Studio 2022 17.7.6

@roji
Copy link
Member

roji commented Feb 21, 2024

Yeah, at least when the UseSequence() API is used, we could detect the dependency between the column and the sequence and order the operations correctly.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants