CODEWITHSUNDEEP
c#entity-framework-coreef-core-7.0
Matthew Verstraete
Matthew Verstraete

Reputation: 6781

Entity Framework Core 7, one to many causes mutiple cascade paths, never had a problem in older version. What am I doing wrong now?

I am rewriting an old .NET Core/EF Core 2 web app in .NET / EF Core 7.

I have the following two entities which, as far as I remember, worked fine in the old app but now I get an error

Microsoft.Data.SqlClient.SqlException (0x80131904): Introducing FOREIGN KEY constraint 'FK_ReleaseDates_Sections_SectionID' on table 'ReleaseDates' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints

I am not sure if I have always been doing something wrong in the entities and fixed it somewhere else I can't see it or if something has changed over the years and I am now doing it completely wrong.

public class Section
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    public string Title { get; set; }

    [Column(TypeName = "varchar(15)")]
    public string Abbreviation { get; set; }

    public int TypeID { get; set; }
    public int? LogoFileID { get; set; }
    public string Synopsis { get; set; }
    public ICollection<ReleaseDate> ReleaseDates { get; set; }

    [ForeignKey("LogoFileID")]
    public SiteFile LogoFile { get; set; }

    [ForeignKey("TypeID")]
    public SectionType Type { get; set; }
}

public class SectionType
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    public string Name { get; set; }
}

public class ReleaseDate
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    public string Name { get; set; }
    public int VersionNumber { get; set; }
    public int TypeID { get; set; }
    public DateTime? Released { get; set; }
    public string Platform { get; set; }
    public string Region { get; set; }
    public string Note { get; set; }
    public int SectionID { get; set; }

    [ForeignKey("SectionID")]
    public Section Section { get; set; }

    [ForeignKey("TypeID")]
    public SectionType Type { get; set; }
}

Am I supposed to be fixing this via FluentAPI's Cascade command or is there something I am screwing up in the entities that I should be doing?

Upvotes: 0

Views: 195

Answers (1)

vernou
vernou

Reputation: 7590

Note : This model don't work in .NET Core 2.1 and EF Core 2.1 with SQL Server. I tested and reproduced the same error.

The model has multiple cascade path :

Section -> ReleaseDate
Section -> SectionType -> ReleaseDate

And SQL Sever don't allow this. To understand, see this data :

SectionA -> ReleaseDateA
         -> SectionTypeA -> ReleaseDateA

When the SectionA is removed, by cascading this remove also ReleaseDateA and SectionTypeA . And remove SectionTypeA, by cascading this remove again ReleaseDateA... ReleaseDateA is removed two times.

But this seems legit, just need to ReleaseDateA one time. Other DBMS like MySQL manage this. Then why not SQL Server?

I don't know and found no official information about this. Just found this :

SQL Server - Why can't we have multiple cascade paths?

...so instead of fixing it, the implementation avoids it by preventing the definition of duplicate cascade paths. It's clearly a short-cut...

SQL Server has this constraint and we need to live with. A solution is to avoid multiple cascade path. In your case, you can :

public class ReleaseDate
{
    ...
    public int? TypeID { get; set; }
    [ForeignKey("TypeID")]
    public SectionType? Type { get; set; }
}

Upvotes: 2

Related Questions