4

I have 4 tables. Cities, Providers, ProviderAdmins, AspNetUsers.

I want to setup following behaviour:

  • If i delete Cities entry - City was deleted.
  • If i delete AspNetUsers entry - User was deleted -> ProviderAdmin was deleted
  • If i delete ProviderAdmins entry - ProviderAdmin was deleted -> User was deleted
  • If i delete Provider entry - Provider was deleted -> ProviderAdmin was deleted -> User was deleted.

Now i even can't create my db from my model, because getting following issue: Introducing FOREIGN KEY constraint 'FK_ProviderAdmins_Providers_ProviderId' on table 'ProviderAdmins' 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.

My entities:

public class City
{
    public long Id { get; set; }

    public string Region { get; set; } = string.Empty;

    public string Name { get; set; } = string.Empty;

    public virtual ICollection<ProviderAdmin> ProviderAdmins { get; set; }
}

public class User : IdentityUser
{
    public string LastName { get; set; }

    public string MiddleName { get; set; }

    public string FirstName { get; set; }

    public virtual ProviderAdmin ProviderAdmin { get; set; }
}

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

    public virtual ICollection<ProviderAdmin> ProviderAdmins { get; set; }
}
public class ProviderAdmin
{
    public long Id { get; set; }

    [Required]
    [ForeignKey("UserId")]
    public string UserId { get; set; }

    public virtual User User { get; set; }

    [Required]
    public long ProviderId { get; set; }

    public virtual Provider Provider { get; set; }

    public long? CityId { get; set; }

    public virtual City City { get; set; }
}

Fluent Api:

builder.Entity<Provider>()
    .HasMany(pa => pa.ProviderAdmins)
    .WithOne(p => p.Provider)
    .OnDelete(DeleteBehavior.Cascade);

builder.Entity<City>()
    .HasMany(pa => pa.ProviderAdmins)
    .WithOne(p => p.City)
    .OnDelete(DeleteBehavior.NoAction);

builder.Entity<User>()
    .HasOne(u => u.ProviderAdmin)
    .WithOne(p => p.User)
    .HasForeignKey<ProviderAdmin>(p => p.UserId)
    .OnDelete(DeleteBehavior.Cascade);

Please, give me advice about how can i fix error mentioned above, and how can i setup behaviour which i want to get.

Thank you so much for your attention.

UPD: I made relationship PAs - AspNetUsers to be one or zero to one. It is still not working, i still missing something. I know that problem is in relationship between provideradmin and users but i really don't know why it not allowed.

Mister D
  • 51
  • 4
  • You have a cascade cycle between ProviderAdmin and User which is not allowed. This is based on the 1-1 reference cycle between these two, which is allowed but may be suspect. Is it really true that every User must have exactly one ProviderAdmin? It seems like a user might not be an Admin anywhere and also that a User might be an admin for more than one Provider? – RBarryYoung Nov 02 '21 at 15:41
  • @RBarryYoung You are right, user might not be Admin. User can't be admin for more that one provider. Thank you for commonet. What should i change? – Mister D Nov 02 '21 at 16:04
  • Remove the cascade from ProviderAdmin to User (which I cannot find in your code). – RBarryYoung Nov 02 '21 at 16:06
  • Honestly, I do not know EF well enough to tell you the details of how to implement it. I know SQL and foreign Keys extremely well, and I am familiar with the error from the database side. – RBarryYoung Nov 02 '21 at 16:10
  • @RBarryYoung Thank you for your attention and advice. I will try! – Mister D Nov 02 '21 at 16:14

0 Answers0