Efcore: Self referencing many to many relationship

[Lg0enga]

I want to create a many to many relationship in EF Core. My entity Product has many Product as childs. I have created a Lookup table with a ProductId and a ProductParentId and a reference to the Product Entity, but ProductParentId reference is null.

How can I also reference ProductParentId to a Product?

Product Entity

```C#
public class Product
{
[Key]
public int Id { get; set; }
public string Naam { get; set; }
public string Omschrijving { get; set; }
public decimal ConsumentPrijs { get; set; }
public decimal HorecaPrijs { get; set; }
public bool OpWebsite { get; set; }
public decimal? StukGewicht { get; set; }
public string FotoPad { get; set; }
public DateTime? DeletedOn { get; set; }
public Eenheid StandaardEenheid { get; set; }
public virtual Btw Btw { get; set; }
public Product ProductParent { get; set; }

    public virtual List<ProductEenheid> ProductEenheden { get; set; }
    public virtual List<ProductGroepProduct> ProductGroepen { get; set; }
    public virtual List<ProductChild> ProductChilderen { get; set; }
}

#### ProductChild Look up table
```C#
   public class ProductChild
    {
        public int ProductId { get; set; }
        public int ProductParentId { get; set; }

        public virtual Product Product { get; set; }
        public virtual Product ProductParent { get; set; }
    }

OnModelCreating

```C#
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity()
.HasKey(u => new { u.ProductId, u.ProductParentId });

        modelBuilder.Entity<Product>()
            .HasMany(u => u.ProductChilderen)
            .WithOne(f => f.Product)
            .HasForeignKey(f => f.ProductId);

        modelBuilder.Entity<Product>()
            .HasMany(u => u.ProductChilderen)
            .WithOne(f => f.ProductParent)
            .HasForeignKey(f => f.ProductParentId);
    }

```

[ajcvickers]

@Lg0enga A many-to-many relationships means that each Product has many ProductChild and each ProductChild has many Product. From the code above, it looks like you might want a many-to-one relationship, where every Produce can have many ProductChild, but each ProductChold only has one parent Product. Can you confirm which mapping is what you are looking for?

[Lg0enga]

@ajcvickers I make a mistake. The Product entity has two list of product. One list of Product childs and one list of product parents. the parents and the childs are types of Product.

That are two self referencing many to many relations ships. How can I get two lists of Product in the Product Entity?

The Navigation property is now only set on ProductId and not to ProductParent or ProductChildId. Now there is a loop on Product

ProductParents lookup table

```C#
public class ProductParents
{
//Id of the selected Product
public int ProductId { get; set; }
public int ProductParentId { get; set; }

    public Product ProductParent { get; set; }
}

#### ProductChilds lookup table
```C#
    public class ProductChilderen
    {
        //Id of the selected Product
        public int ProductId { get; set; }
        public int ProductChildId { get; set; }

        public Product ProductChild { get; set; }
    }

Product entity

```C#
public class Product
{
[Key]
public int Id { get; set; }
public string Naam { get; set; }
public string Omschrijving { get; set; }
public decimal ConsumentPrijs { get; set; }
public decimal HorecaPrijs { get; set; }
public bool OpWebsite { get; set; }
public decimal? StukGewicht { get; set; }
public string FotoPad { get; set; }
public DateTime? DeletedOn { get; set; }
public Eenheid StandaardEenheid { get; set; }
public virtual Btw Btw { get; set; }

    public virtual List<ProductEenheid> ProductEenheden { get; set; }
    public virtual List<ProductGroepProduct> ProductGroepen { get; set; }
    public virtual List<ProductChilderen> ProductChilderen { get; set; }
    public virtual List<ProductParents> ProductParents { get; set; }
}

```

[ajcvickers]

@Lg0enga Something like this:
```C#
public class Product
{
[Key]
public int Id { get; set; }
public string Naam { get; set; }
public string Omschrijving { get; set; }
public decimal ConsumentPrijs { get; set; }
public decimal HorecaPrijs { get; set; }
public bool OpWebsite { get; set; }
public decimal? StukGewicht { get; set; }
public string FotoPad { get; set; }
public DateTime? DeletedOn { get; set; }

public virtual List<ProductProduct> ProductChilderen { get; set; }
public virtual List<ProductProduct> ProductParents { get; set; }

}

public class ProductProduct
{
public int ProductChildId { get; set; }
public int ProductParentId { get; set; }

public virtual Product ProductChild { get; set; }
public virtual Product ProductParent { get; set; }

}

modelBuilder.Entity()
.HasKey(u => new {u.ProductChildId, u.ProductParentId});

modelBuilder.Entity()
.HasMany(u => u.ProductChilderen)
.WithOne(f => f.ProductParent)
.HasForeignKey(f => f.ProductParentId);

modelBuilder.Entity()
.HasMany(u => u.ProductParents)
.WithOne(f => f.ProductChild)
.HasForeignKey(f => f.ProductChildId);
```
This is the same as a normal many-to-many mapping, as described in the docs, except that both sides are the same entity type.

[selected-pixel-jameson]

I have essentially the exact same design in my application. However, when I save a ProductProduct entity it is assigning the ParentId to both the Parent and Child Id fields. I have no idea why this is happening. I'm looking at the reference in debugger before it's saved / updated and it looks correct, but after it updates it is incorrect in the database. Any ideas?

[ajcvickers]

@selected-pixel-jameson Please file a new issue including a runnable project/solution or complete code listing that demonstrates the behavior you are seeing.

[aaronhudon]

@ajcvickers is this supported in 2.0.2 ?

In the example, "WithOne(f => f.ProductParent)" doesn't provide the expression of the ProductProduct class. The expression is expecting "Product".

modelBuilder.Entity<Product>()
    .HasMany(u => u.ProductChilderen)
    .WithOne(f => f.ProductParent)
    .HasForeignKey(f => f.ProductParentId);

[ajcvickers]

@aaronhudon Yes, this is supported in 2.0.x. If you are encountering a problem, then please file a new issue with a runnable project/solution or complete code listing that demonstrates what you are seeing.

[baloghlaszlo]

@ajcvickers Aren't the two modelBuilder.Entity<Product>() statement missing the .OnDelete(DeleteBehavior.Restrict) ? Otherwise I get an exception from EnsureCreated() regarding that this can cause cycles or multiple cascade paths. I tried to mimic this solution to my problem and still have the issue, and not entirely sure if the Restriction of DeleteBehavior is the correct solution here.
Thank you in advance!

[ajcvickers]

@baloghlaszlo Not necessarily. First, this is a SQL Server limitation; other providers can handle this. Second, disabling cascade deletes entirely is often not the best way to handle this. Instead, it can be better to edit the migration to allow EF to continue doing cascade deletes while the database does not. Third, only one relationship needs to changed to prevent the cycle, and depending on how the domain model is used, this may be the best choice.

[KirannBhavaraju]

@ajcvickers I tried implementing self referencing many to many relationships with EF Core like you mentioned in your answer. But there exists a problem with the approach if we want Cascade Deleting to be functional.
It wont actually allow because enabling the DeleteBehavior or the UpdateBehavior to Cascade will cause cycles.
Also i dont know if the problem of cycles exists with the database provider. Any thoughts? I am stuck there .

[ajcvickers]

@KirannBhavaraju SQL Server doesn't support cycles like this. There isn't anything EF can do about it.

[KirannBhavaraju]

@ajcvickers Yeah, I tried it with PostgreSQL, works like a charm. Thanks.