elsa-workflows / elsa-workflows/elsa-core
Wrong Schema in EF Migrations Designer
- 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
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