elsa-workflows / elsa-workflows/elsa-core

Wrong Schema in EF Migrations Designer

Open
#6,721 0 comments 0 reactions 0 assignees View on GitHub
triaged
Dominant language
C#
Stars
7.9k
Forks
1.5k
Avg merge
15h 22m
Merged PRs (30d)
114

Description

## Description

The MS SQL EF Migration `20240329200626_V3_1` migration fails at run-time.

## Steps to Reproduce

1. **Detailed Steps**:

Add

```cs
ElsaDbContextBase.ElsaSchema = "MY_CUSTOM_SCHEMA";
```

Right before

```cs
services.AddElsa(...)
```

2. **Code Snippets**: `n/a`

3. **Attachments**:
- **Workflow JSON**: `n/a`
- **Sample Project**: `n/a`

4. **Reproduction Rate**: `every time`

5. **Video/Screenshots**: `n/a`

6. **Additional Configuration**:
- As seen in `src/apps/Elsa.Server.Web`

## Expected Behavior
EF Migrations to work out-of-the-box for any given custom schema name

## Actual Behavior

Throws an exception:

```
info: 5/16/2025 10:06:28.210 RelationalEventId.MigrationApplying[20402] (Microsoft.EntityFrameworkCore.Migrations)
Applying migration '20240329200626_V3_1'.
info: 5/16/2025 10:06:28.226 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
EXEC sp_rename N'[MY_CUSTOM_SCHEMA].[AlterationPlans].[SerializedWorkflowInstanceIds]', N'SerializedWorkflowInstanceFilter', 'COLUMN';
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (14ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @var sysname;
SELECT @var = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[MY_CUSTOM_SCHEMA].[AlterationPlans]') AND [c].[name] = N'Status');
IF @var IS NOT NULL EXEC(N'ALTER TABLE [MY_CUSTOM_SCHEMA].[AlterationPlans] DROP CONSTRAINT [' + @var + '];');
ALTER TABLE [MY_CUSTOM_SCHEMA].[AlterationPlans] ALTER COLUMN [Status] nvarchar(450) NOT NULL;
fail: 5/16/2025 10:06:28.244 RelationalEventId.CommandError[20102] (Microsoft.EntityFrameworkCore.Database.Command)
Failed executing DbCommand (14ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @var sysname;
SELECT @var = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[MY_CUSTOM_SCHEMA].[AlterationPlans]') AND [c].[name] = N'Status');
IF @var IS NOT NULL EXEC(N'ALTER TABLE [MY_CUSTOM_SCHEMA].[AlterationPlans] DROP CONSTRAINT [' + @var + '];');
ALTER TABLE [MY_CUSTOM_SCHEMA].[AlterationPlans] ALTER COLUMN [Status] nvarchar(450) NOT NULL;
fail: Elsa.Common.Multitenancy.TenantEventsManager[0]
Error occurred while processing tenant activated event.
Microsoft.Data.SqlClient.SqlException (0x80131904): The index 'IX_AlterationPlan_Status' is dependent on column 'Status'.
ALTER TABLE ALTER COLUMN Status failed because one or more objects access this column.
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, SqlCommand command, 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.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
at Microsoft.Data.SqlClient.SqlCommand.<>c.b__193_1(IAsyncResult asyncResult)
at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteAsync(IReadOnlyList`1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean beginTransaction, Boolean commitTransaction, Nullable`1 isolationLevel, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteAsync(IReadOnlyList`1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean beginTransaction, Boolean commitTransaction, Nullable`1 isolationLevel, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IReadOnlyList`1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean commitTransaction, Nullable`1 isolationLevel, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateImplementationAsync(DbContext context, String targetMigration, MigrationExecutionState state, Boolean useTransaction, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateImplementationAsync(DbContext context, String targetMigration, MigrationExecutionState state, Boolean useTransaction, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.<>c.<b__22_1>d.MoveNext()
--- End of stack trace from previous location ---
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)
at Elsa.EntityFrameworkCore.RunMigrationsStartupTask`1.ExecuteAsync(CancellationToken cancellationToken) in F:\NotMine\elsa-core\src\modules\Elsa.EntityFrameworkCore.Common\RunMigrationsStartupTask.cs:line 20
at Elsa.Common.Multitenancy.TaskExecutor.ExecuteInternalAsync(ITask task, Func`1 action, CancellationToken cancellationToken) in F:\NotMine\elsa-core\src\modules\Elsa.Common\Multitenancy\Implementations\TaskExecutor.cs:line 35
at Elsa.Common.Multitenancy.TaskExecutor.ExecuteInternalAsync(ITask task, Func`1 action, CancellationToken cancellationToken) in F:\NotMine\elsa-core\src\modules\Elsa.Common\Multitenancy\Implementations\TaskExecutor.cs:line 35
at Elsa.Common.Multitenancy.TaskExecutor.ExecuteTaskAsync(ITask task, CancellationToken cancellationToken) in F:\NotMine\elsa-core\src\modules\Elsa.Common\Multitenancy\Implementations\TaskExecutor.cs:line 13
at Elsa.Common.Multitenancy.EventHandlers.RunStartupTasks.TenantActivatedAsync(TenantActivatedEventArgs args) in F:\NotMine\elsa-core\src\modules\Elsa.Common\Multitenancy\EventHandlers\RunStartupTasks.cs:line 17
at Elsa.Common.Multitenancy.TenantEventsManager.TenantActivatedAsync(TenantActivatedEventArgs args) in F:\NotMine\elsa-core\src\modules\Elsa.Common\Multitenancy\Implementations\TenantEventsManager.cs:line 13
ClientConnectionId:291dcb5e-b515-4d60-9565-433bc09efb65
Error Number:5074,State:1,Class:16
```

## Screenshots
`n/a`

## Environment
- **Elsa Package Version**: `3.3.5`
- **Operating System**: `Windows 10`
- **Browser and Version**: `n/a`

## Log Output
`n/a`

## Troubleshooting Attempts
I've tried was to make a few manual adjustments that just worked
and there were no other complains

```diff
namespace Elsa.EntityFrameworkCore.SqlServer.Migrations.Alterations
{
[DbContext(typeof(AlterationsElsaDbContext))]
[Migration("20240329200626_V3_1")]
partial class V3_1
{
///
protected override void BuildTargetModel(ModelBuilder modelBuilder)
{
#pragma warning disable 612, 618
modelBuilder
.HasDefaultSchema("Elsa")
.HasAnnotation("ProductVersion", "7.0.14")
.HasAnnotation("Relational:MaxIdentifierLength", 128);

SqlServerModelBuilderExtensions.UseIdentityColumns(modelBuilder);

modelBuilder.Entity("Elsa.Alterations.Core.Entities.AlterationJob", b =>
{
- b.ToTable("AlterationJobs", "Elsa");
+ b.ToTable("AlterationJobs", _schema.Schema);
});

modelBuilder.Entity("Elsa.Alterations.Core.Entities.AlterationPlan", b =>
{
- b.ToTable("AlterationPlans", "Elsa");
+ b.ToTable("AlterationPlans", _schema.Schema);
});
#pragma warning restore 612, 618
}
}
}

```

Note that `.HasDefaultSchema("Elsa")` was left as it is.

## Additional Context

So my main question is:
Should all instances of the hardcoded `Elsa` string be replaced with `_schema.Schema` too for consistency and future proof purposes?

cc: @jdevillard
Side note: I see that there is no support (yet) for `.Designer.cs` files in the `ef‐migration‐runtime‐schema` tool.

Second question:
How can we mitigate this without adding custom implementations for `IDesignTimeServices` and `IMigrationsCodeGenerator`?

## Related Issues
#5022

Contributor guide

Open the contributing guide

Research direction

Start by inspecting the 20240329200626_V3_1 migration and its designer model, then trace migration execution from src/modules/Elsa.EntityFrameworkCore.Common/RunMigrationsStartupTask.cs:20. Reproduce the failure with ElsaDbContextBase.ElsaSchema set to MY_CUSTOM_SCHEMA and compare the generated SQL and hardcoded schema references. Done means the migration runs successfully against a custom schema without the dependent-index error.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.