c# - EntityFramework Same Table Many to Many Relationship

Question

I have a table called Products which obviously contains products. However, I need to create related products. So what I've done is create a junction table called product_related which has two PKs. ProductID from Products table and RelatedID also from Products table.

I already use EF and have set up everything on other tables. How should I add this properly in order to create a relationship with products as such: product.Products.Add(product object here). Of course here product represent a product object that I've fetched from the db using db.Products.FirstOr....

How should I do this properly ? A many to many to the same table?

Thanks.

Answer 1

In order to create a many-to-many relationship with Database-First approach you need to setup a database schema that follows certain rules:

• Create a Products table with a column ProductID as primary key
• Create a ProductRelations table with a column ProductID and a column RelatedID and mark both columns as primary key (composite key)
• Don't add any other column to the ProductRelations table. The two key columns must be the only columns in the table to let EF recognize this table as a link table for a many-to-many relationship
• Create two foreign key relationships between the two tables:
  • The first relationship has the Products table as primary-key-table with the ProductID as primary key and the ProductRelations table as foreign-key-table with only the ProductID as foreign key
  • The second relationship also has the Products table as primary-key-table with the ProductID as primary key and the ProductRelations table as foreign-key-table with only the RelatedID as foreign key
• Enable cascading delete for the first of the two relationships. (You can't do it for both. SQL Server won't allow this because it would result in multiple cascading delete paths.)

If you generate an entity data model from those two tables now you will get only one entity, namely a Product entity (or maybe Products if you disable singularization). The link table ProductRelations won't be exposed as an entity.

The Product entity will have two navigation properties:

public EntityCollection<Product> Products { get { ... } set { ... } }
public EntityCollection<Product> Products1 { get { ... } set { ... } }

These navigation collections are the two endpoints of the same many-to-many relationship. (If you had two different tables you wanted to link by a many-to-many relationship, say table A and B, one navigation collection (Bs) would be in entity A and the other (As) would be in entity B. But because your relationship is "self-referencing" both navigation properties are in entity Product.)

The meaning of the two properties are: Products are the products related to the given product, Products1 are the products that refer to the given product. For example: If the relationship means that a product needs other products as parts to be manufactured and you have the products "Notebook", "Processor", "Silicon chips" then the "Processor" is made of "Silicon chips" ("Silicon chips" is an element in the Products collection of the Processor product entity) and is used by a "Notebook" ("Notebook" is an element in the Products1 collection of the Processor product entity). Instead of Products and Products1 the names MadeOf and UsedBy would be more appropriate then.

You can safely delete one of the collections from the generated model if you are only interested in one side of the relationship. Just delete for example Products1 in the model designer surface. You can also rename the properties. The relationship will still be many-to-many.

Edit

As asked in a comment the model and mapping with a Code-First approach would be:

Model:

public class Product
{
    public int ProductID { get; set; }

    public ICollection<Product> RelatedProducts { get; set; }
}

Mapping:

public class MyContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .HasMany(p => RelatedProducts)
            .WithMany()
            .Map(m =>
            {
                m.MapLeftKey("ProductID");
                m.MapRightKey("RelatedID");
                m.ToTable("product_related");
            });
    }
}