dotnet / dotnet/efcore

Migrations command in incorrect order when dealing with sequences

Open
#33,130 1 comment 0 reactions 0 assignees View on GitHub
area-migrations customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

### Using the HasSequance API

```C#
modelBuilder.HasSequence("OrderNumbers").StartsAt(1);
modelBuilder.Entity().Property(o => o.Seq).HasDefaultValueSql("NEXT VALUE FOR OrderNumbers");
```
### Generated Migrations for creating the sequance

```C#
public partial class init3 : Migration
{
///
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateSequence(
name: "OrderNumbers");

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

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

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

```
### when trying to remove the sequance
```C#
public partial class init4 : Migration
{
///
protected override void Up(MigrationBuilder migrationBuilder)
{

migrationBuilder.DropSequence(
name: "OrderNumbers");

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

}

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

migrationBuilder.AlterColumn(
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

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.