entity-framework-core - Multiple relations to same table with one required and others optional in EF Core

Question

I have a table named Provider with three relations to another table State. Of these relations one is required and the other two are optional. See the relationship in the diagram below:

Here are the entities along with the fluent configurations for each.

Provider

public class Provider
{
    public int Id { get; set; }

    [Required]
    public int PrimaryStateId { get; set; }
    public virtual State PrimaryState { get; set; }

    public int? BillingStateId { get; set; }
    public virtual State BillingState { get; set; }

    public int? ShippingStateId { get; set; }
    public virtual State ShippingState { get; set; }
}

class ProviderConfig : IEntityTypeConfiguration<Provider>
{
    public void Configure(EntityTypeBuilder<Provider> entity)
    {
        entity.HasOne(x => x.PrimaryState)
              .WithMany(x => x.ProvidersPrimary)
              .IsRequired(true)
              .OnDelete(DeleteBehavior.Restrict);

        entity.HasOne(x => x.BillingState)
              .WithMany(x => x.ProvidersBilling)
              .IsRequired(false)
              .OnDelete(DeleteBehavior.SetNull);

        entity.HasOne(x => x.ShippingState)
              .WithMany(x => x.ProvidersShipping)
              .IsRequired(false)
              .OnDelete(DeleteBehavior.SetNull);
    }
}

State

public class State
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Provider> ProvidersPrimary { get; set; } = new List<Provider>();
    public virtual ICollection<Provider> ProvidersBilling { get; set; } = new List<Provider>();
    public virtual ICollection<Provider> ProvidersShipping { get; set; } = new List<Provider>();
}

class StateConfig : IEntityTypeConfiguration<State>
{
    public void Configure(EntityTypeBuilder<State> entity)
    {
        entity.Property(x => x.Name).IsRequired();
        entity.HasIndex(x => x.Name).IsUnique();
    }
}

As you can see, I want to set DeleteBehavior.Restrict for the PrimaryState, and DeleteBehavior.SetNull for the other two relations. However, this throws error on update-database with the following message:

Introducing FOREIGN KEY constraint 'FK_Provider_State_ShippingStateId' on table 'Provider' 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.

However, if I get rid of one of the optional relations, it works fine. That is, it works when I have one required relation and one optional relation, but not when I have one required and two optional relations. Also, it works fine if I get rid of OnDelete(DeleteBehavior.SetNull) from the optional relations, but then in the database the delete rule for foreign key BillingStateId ends up being Set Null, but that for ShippingStateId is No Action. No idea why it is different for two similarly configured optional relations.

Questions:

1. Why does update-database fail with that error? I don't understand how cycles or multiple cascade paths may be caused.
2. Why getting rid of OnDelete(DeleteBehavior.SetNull) creates a foreign key with Set Null delete rule for one optional relation but No Action for the other?
3. What is the correct way to configure this relationship? That is, one required relation and two optional relations, and the foreign keys for the optional relations should have a delete rule of Set Null.

VS Solution link: click

标签： entity-framework-coreforeign-keysrelational-database