Using Entity Framework Core (5.0.17) code first I'm having trouble implementing a class that has two references to another class.

this is my structure:

public class Beneficio
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    
    public int ComercioBeneficioId { get; set; }
    public int ComercioOtorgadorId { get; set; }

    [ForeignKey("ComercioBeneficioId")]
    public Comercio ComercioBeneficio { get; set; }

    [ForeignKey("ComercioOtorgadorId")]
    public Comercio ComercioOtorgador { get; set; }
}

public class Comercio
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; } 

The Required attributes on the location references makes it impossible to update the table on the database . This is the error message I get:

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

CodePudding user response：

The model has multiple cascade path :

Beneficio -> ComercioBeneficio
          -> ComercioOtorgador

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

BeneficioA -> (Beneficio) ComercioA
           -> (Otorgador) ComercioA

In the BeneficioA, the Beneficio and the Otorgador is the same ComercioA. Then when BeneficioA is removed, by cascade ComercioA is removed two times.

But this seems legit, just need to ComercioA 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 disable the delete cascade behavior by setting the foreign key and navigation property nullable :

public class Beneficio
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public int? ComercioBeneficioId { get; set; }
    public int? ComercioOtorgadorId { get; set; }

    [ForeignKey("ComercioBeneficioId")]
    public Comercio? ComercioBeneficio { get; set; }
    [ForeignKey("ComercioOtorgadorId")]
    public Comercio? ComercioOtorgador { get; set; }
}

CodePudding user response：

You can use fluent api to configure the relationship

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Comercio>()
       .HasMany<Beneficio>()
       .WithOne(g => g.ComercioBeneficio)
       .HasForeignKey(j => j.ComercioBeneficioId).OnDelete(DeleteBehabiour.SetNull);
 }

Do this on both properties. And as per mentioned @vernou, you need to make the foreign key nullable so that the relationship is optional