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.

Solution

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");
            });
    }
}

Comments

[Slauma]

Your answer is really far away from the question. 1) Your first example is one-to-many, 2) the second is one-to-many too (despite of you are saying it would be many-to-many). 3) Your relationships are not between the same table and 4) he isn't using Code-First like your examples do.

[drzaus]

What about with Code-First? How do you specify the relationship (with Fluent API)?

[Slauma]

@drzaus: See my Edit. I had this as an answer actually before, but then deleted it when I noticed that DB-First was asked. Grabbed the code now from the deleted answer :)

[drzaus]

exactly what i was looking for @Slauma

[Leniel Maccaferri]

Hey @Slauma... I think this one stackoverflow.com/q/17752271/114029 I asked few instants ago goes in the same thinking you've put here. Nice answer! :)

[Funka]

Don't forget to mark the RelatedProducts property as virtual if you want lazy-loading to work --- otherwise you might find it always seems to be empty unless you specifically include/load these when you need them.

[tenbits]

@Slauma, and how can I sepcify in code first the CASCADE DELETE? It seems it is not possible, right? How then I delete a Product, for instance Foo? Should I load all Products, which contain the Foo in RelatedProducts, and then manually I should remove the Foo from theres RelatedProducts. It sounds as too much overhead. Is there any better solution? Thank you!

[letsgetsilly]

If you create a composite key on RelatedProducts, the foreign keys cannot reference only a single key, they must reference both of the composite columns.

[Richard Pawson]

Shouldn't that be: .HasMany(p => p.RelatedProducts) ?

[pjrki]

Hey. Any idea why I'm keep getting error about CollectionNavigationBuilder<Person, Person> does not contain a definition for WithMany() ? am I missing some reference to a library or something?

[Mark]

What can you do if you need some additional columns in ProductRelations table?