One to one to many causes cycles or multiple cascade paths error

I'm using Entity Framework Core 3.1.0 together with SQL Server Express during development.

I have a one to one relation and a one to many relation connected like this:

Relation --> SupplierSettings --< Conditions

So between Relation and SupplierSetting I have a one to one relation. Between SupplierSetting and Conditions I have a one to many relation.

Excerpts of the classes are like this.

public class Relation
{
    public string GLN { get; set; } 
    public string Name { get; set; }
    public string Country { get; set; } 
}

public class SupplierImportSetting 
{
    public Relation Supplier { get; set; }
    public int SupplierId { get; set; }

    public int MinimumMarginPercentage { get; set; }
    public bool OnlyImportWithConditions { get; set; }

    public ICollection<SupplierCondition> Conditions { get; set; }
}

public class SupplierCondition 
{
    public SupplierImportSetting SupplierImportSetting { get; set; }
    public int SupplierImportSettingId { get; set; }

    public string DiscountGroup { get; set; }
    public string SupplierTradeItemCode { get; set; }
    public string Description { get; set; }

    public decimal? Discount1Percentage { get; set; } // 1 = 100%
}

I configured my context like this:

// One to one where Relation is Principal and SupplierImportSetting is dependent.
modelBuilder.Entity<Relation>()
    .HasOne<SupplierImportSetting>()
    .WithOne(sis => sis.Supplier)
    .HasForeignKey<SupplierImportSetting>(sis => sis.SupplierId)
    .OnDelete(DeleteBehavior.Cascade);

// One to many with SupplierImportSetting as Principal and SupplierCondition as dependent.
modelBuilder.Entity<SupplierImportSetting>()
    .HasMany(sis => sis.Conditions)
    .WithOne(c => c.SupplierImportSetting)
    .HasForeignKey(c => c.SupplierImportSettingId)
    .OnDelete(DeleteBehavior.Cascade);

But I get this error:

Failed executing DbCommand (11ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

ALTER TABLE [SupplierConditions] ADD CONSTRAINT [FK_SupplierConditions_SupplierImportSettings_SupplierImportSettingId] FOREIGN KEY ([SupplierImportSettingId]) REFERENCES [SupplierImportSettings] ([Id]) ON DELETE CASCADE;

Microsoft.Data.SqlClient.SqlException (0x80131904): Introducing FOREIGN KEY constraint 'FK_SupplierConditions_SupplierImportSettings_SupplierImportSettingId' on table 'SupplierConditions' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index. See previous errors.

at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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, Action1 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(TaskCompletionSource1 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, IReadOnlyDictionary2 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:3563e9af-ca34-45dc-a3aa-76394f5cfcbd
Error Number:1785,State:0,Class:16
Introducing FOREIGN KEY constraint 'FK_SupplierConditions_SupplierImportSettings_SupplierImportSettingId' on table 'SupplierConditions' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index. See previous errors.

Actually, SQL Server is probably right, because when I delete a Relation entity, this will cause a cascading cascading delete. But this is what I want. Relation is the principal, so the other records should be deleted.

So I figured, maybe I need to define the other way of the one to one relation as well. When I add this to my context config, the migration runs and executes without error. But when I check the database, the constraint that has been created on SupplierImportSettings does not have ON DELETE. So, when I remove a Relation entity, the SupplierImportSettings will not be removed. That's not what I want.

// SupplierImportSetting is principal, Relation is dependent.
// Define one to one the other way to disable cascade delete in this direction.
modelBuilder.Entity<SupplierImportSetting>()
    .HasOne<Relation>(sis => sis.Supplier)
    .WithOne()
    .OnDelete(DeleteBehavior.NoAction);

No matter what I try, I can't get Entity Framework Core to create the situations that I want.

I see that the way how you have configured relations will cause the deletion of conditions when a supplierSetting is deleted. It will cause the deletion of other supplierSettings which is wrong. Probably this is the reason why it fails.

// Try this change

modelBuilder.Entity<SupplierImportSetting>()
    .HasMany(sis => sis.Conditions)
    .WithOne(c => c.SupplierImportSetting)
    .HasForeignKey(c => c.SupplierImportSettingId)
    .OnDelete(DeleteBehavior.NoAction);