CODEWITHSUNDEEP
c#sql-serverentity-framework-core
user2129013
user2129013

Reputation: 92

EF Core - Foreign Key causes cycles or multiple cascade paths while having many to many relationships

I am trying to create an author entity which has a relationship to a country entity (country of origin of the author) and relationship to a set of countries which are visited by the author. The following is my code

public class Author
{
    [Key]
    public int Id { get; set; }

    [Required]
    public string Name { get; set; }

    public int CountryOfOriginId { get; set; }

    [ForeignKey("CountryOfOriginId")]
    public Country CountryOfOrigin { get; set; }

    public ICollection<AuthorCountry> VisitedCountries { get; set; }
}

public class Country
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
}

public class AuthorCountry
{
    public int AuthorId { get; set; }
    [ForeignKey("AuthorId")]
    public Author Author { get; set; }

    public int CountryId { get; set; }
    [ForeignKey("CountryId")]
    public Country Country { get; set; }
}

AuthorCountry exist to keep the M:N relationship and has the composite key defined as below

modelBuilder.Entity<AuthorCountry>()
    .HasKey(c => new
    {
        c.CountryId,
        c.AuthorId
    });

I am able to add the migration, however when I try to update the database I am getting the following error:

Failed executing DbCommand (23ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [AuthorCountry] (
    [AuthorId] int NOT NULL,
    [CountryId] int NOT NULL,
    CONSTRAINT [PK_AuthorCountry] PRIMARY KEY ([CountryId], [AuthorId]),
    CONSTRAINT [FK_AuthorCountry_Author_AuthorId] FOREIGN KEY ([AuthorId]) REFERENCES [Author] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_AuthorCountry_Countries_CountryId] FOREIGN KEY ([CountryId]) REFERENCES [Countries] ([Id]) ON DELETE CASCADE
);
Microsoft.Data.SqlClient.SqlException (0x80131904): 
Introducing FOREIGN KEY constraint 'FK_AuthorCountry_Countries_CountryId' on table 'AuthorCountry' 
may cause cycles or multiple cascade paths. 
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

I found this question in which someone reports the similar behaviour, however my case is different that in the Author entity I need to have a single Country relationship to keep the CountryOfOrigin and also need to keep the M:N relationship to keep the list of visited countries.

How can I solve the problem?

Fix: (thanks for the hint @lrpe)

I edited the AuthorCountry as below (removed the ForiegnKey attribute)

public class AuthorCountry
{
    public int AuthorId { get; set; }
    //[ForeignKey("AuthorId")] : Handled by Fluent API - to override default delete behaviour
    public Author Author { get; set; }

    public int CountryId { get; set; }
    [ForeignKey("CountryId")]
    public Country Country { get; set; }
}

Finally added the below fluent api config to over-ride the default cascading delete behaviour

modelBuilder.Entity<AuthorCountry>()
    .HasOne(a => a.Country)
    .WithMany()
    .HasForeignKey(a => a.CountryId)
    .OnDelete(DeleteBehavior.Restrict);

Upvotes: 0

Views: 2657

Answers (1)

lrpe
lrpe

Reputation: 799

You receive this error message because in SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. The tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree.

(Source)

I'm assuming that CountryOfOrigin is also a cascading relationship.

Suppose that you delete a Country. The deletion will cascade to Author and AuthorCountry, and will cascade again from Author to AuthorCountry. There are thus two paths to AuthorCountry from the one DELETE statement, which is forbidden.

To fix the problem, one of your foreign keys must be changed to a non-cascading referential action.

Besides, does it really make sense for countries to be deletable if they are referenced by other records? Unless the purpose of the Country table is something other than containing a permanent list of countries that exist in the world, I would suggest making foreign keys that reference this table non-cascading.

Upvotes: 1

Related Questions