Why can't Entity Framework handle cascade deletes on a maybe child

This is for a political campaign system. In it we have the campaign model. The campaign can be national (President) or local. A local campaign is owned by the appropriate State. So a Campaign has exactly 1 parent, but it can be a Country or State.

public class Campaign : IOrganization
{
	public int Id { get; private set; }

	public State? State { get; set; }
	public int? StateId { get; set; }

	public Country? Country { get; set; }
	public int? CountryId { get; set; }
}

And State & Country have their collections of the Campaigns they each own.

public class Country : IOrganization
{
	public int Id { get; private set; }

	public ICollection<Campaign>? Campaigns { get; private set; }
}

public class State : IOrganization
{
    public int Id { get; private set; }

	public ICollection<Campaign>? Campaigns { get; private set; }
}

When I run update-database I get:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Campaigns] (
          [Id] int NOT NULL IDENTITY,
          [UniqueId] nvarchar(450) NOT NULL,
          [Name] nvarchar(450) NOT NULL,
          [News] nvarchar(max) NULL,
          [Address_Address1] nvarchar(max) NULL,
          [Address_Address2] nvarchar(max) NULL,
          [Address_City] nvarchar(max) NULL,
          [Address_State] nvarchar(max) NULL,
          [Address_Country] nvarchar(max) NULL,
          [Address_PostalCode] nvarchar(max) NULL,
          [Address_Location] geography NULL,
          [StateId] int NULL,
          [CountryId] int NULL,
          [Enabled] bit NOT NULL,
          [Closed] bit NOT NULL,
          [Deleted] bit NOT NULL,
          [Created] datetime2 NOT NULL DEFAULT (getutcdate()),
          [RowVersion] rowversion NOT NULL,
          CONSTRAINT [PK_Campaigns] PRIMARY KEY ([Id]),
          CONSTRAINT [FK_Campaigns_Countries_CountryId] FOREIGN KEY ([CountryId]) REFERENCES [Countries] ([Id]) ON DELETE CASCADE,
          CONSTRAINT [FK_Campaigns_States_StateId] FOREIGN KEY ([StateId]) REFERENCES [States] ([Id]) ON DELETE CASCADE
      );
Failed executing DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [Campaigns] (
    [Id] int NOT NULL IDENTITY,
    [UniqueId] nvarchar(450) NOT NULL,
    [Name] nvarchar(450) NOT NULL,
    [News] nvarchar(max) NULL,
    [Address_Address1] nvarchar(max) NULL,
    [Address_Address2] nvarchar(max) NULL,
    [Address_City] nvarchar(max) NULL,
    [Address_State] nvarchar(max) NULL,
    [Address_Country] nvarchar(max) NULL,
    [Address_PostalCode] nvarchar(max) NULL,
    [Address_Location] geography NULL,
    [StateId] int NULL,
    [CountryId] int NULL,
    [Enabled] bit NOT NULL,
    [Closed] bit NOT NULL,
    [Deleted] bit NOT NULL,
    [Created] datetime2 NOT NULL DEFAULT (getutcdate()),
    [RowVersion] rowversion NOT NULL,
    CONSTRAINT [PK_Campaigns] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Campaigns_Countries_CountryId] FOREIGN KEY ([CountryId]) REFERENCES [Countries] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_Campaigns_States_StateId] FOREIGN KEY ([StateId]) REFERENCES [States] ([Id]) ON DELETE CASCADE
);
Microsoft.Data.SqlClient.SqlException (0x80131904): Introducing FOREIGN KEY constraint 'FK_Campaigns_States_StateId' on table 'Campaigns' 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, 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:e68c158d-e2e8-4149-9d65-7f6fa23bf360
Error Number:1785,State:0,Class:16
Introducing FOREIGN KEY constraint 'FK_Campaigns_States_StateId' on table 'Campaigns' 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.

I can avoid this with the following fluent:

// one for Country too
public void Configure(EntityTypeBuilder<State> builder)
{
	builder.HasMany(x => x.Campaigns)
			.WithOne(x => x.State).OnDelete(DeleteBehavior.ClientSetNull);
}

And I then in my DbContext.SaveChangesAsync() override figure out what campaigns need to be deleted and delete them there.

Is there some way to tell EF to not worry, that it can cascade delete and there will not be any cases where a Campaign has both a State and a Country parent? Because I figure it's much better to have the DB enforce a cascading delete.

Update: I posted it on MSDN just now to see if they can answer.

The following works and passes all my unit tests. But I'll feel better about this solution if/when someone who knows EF comments on this and says this is right. (Or posts an answer saying this is wrong and providing the correct answer.)

public class StateConfiguration : IEntityTypeConfiguration<State>
{
	public void Configure(EntityTypeBuilder<State> builder)
	{
		builder.HasMany(x => x.Campaigns)
			.WithOne(x => x.State).OnDelete(DeleteBehavior.ClientCascade);
	}
}

public class CountryConfiguration : IEntityTypeConfiguration<Country>
{
	public void Configure(EntityTypeBuilder<Country> builder)
	{
		builder.HasMany(x => x.Campaigns)
			.WithOne(x => x.Country).OnDelete(DeleteBehavior.ClientCascade);
	}
}

From reading a couple of sites I think the difference is ClientCascade has EF perform the cascading delete. Having the DB do it is likely more efficient, but having EF do it is likely more efficient than doing it myself. So I'll take it.

The error was documented here.

But it's strange that you get this error, given the fact that you have a single foreign key at most per target tables. So, maybe it's some bug in SQL Server. An alternate way to solve it is to create a before delete trigger. Sample example:

DELIMITER $$

CREATE TRIGGER trigger_name
    BEFORE DELETE
    ON table_name FOR EACH ROW
BEGIN
    -- statements
END$$    

DELIMITER ;

Let's apply the idea to your table:

DELIMITER $$

CREATE TRIGGER Delete_Country
    BEFORE DELETE
    ON Countries FOR EACH ROW
BEGIN
    DELETE FROM campaigns where CountryId = OLD.Id;
END$$    

CREATE TRIGGER Delete_State
    BEFORE DELETE
    ON States FOR EACH ROW
BEGIN
    DELETE FROM campaigns where StateId = OLD.Id;
END$$    

DELIMITER ;