53

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.

user1027620
  • 2,745
  • 5
  • 37
  • 65
  • 2
    Please do not use a M2M on a single table if this is going to be production software, it can and does work, but it is such a pain to maintain, I'm at work now but if you haven't got an answer by the time I get home I will show you a suitable solution to your question. – Chazt3n Sep 07 '12 at 15:46
  • Are you asking how to add to the product_related table using entity framework? – Andrew Walters Sep 07 '12 at 19:50
  • @Chazt3n What would be the alternative? – Max Mar 26 '18 at 13:17

2 Answers2

91

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");
            });
    }
}
Slauma
  • 175,098
  • 59
  • 401
  • 420
  • 3
    What about with Code-First? How do you specify the relationship (with Fluent API)? – drzaus Jun 13 '13 at 17:17
  • 4
    @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 :) – Slauma Jun 13 '13 at 17:35
  • Hey @Slauma... I think this one http://stackoverflow.com/q/17752271/114029 I asked few instants ago goes in the same thinking you've put here. Nice answer! :) – Leniel Maccaferri Jul 19 '13 at 18:04
  • 3
    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. – Funka Jun 27 '14 at 21:44
  • 1
    @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! – tenbits Sep 18 '15 at 14:53
  • 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. – letsgetsilly Jan 31 '17 at 22:20
  • 2
    Shouldn't that be: .HasMany(p => p.RelatedProducts) ? – Richard Pawson Sep 08 '17 at 14:04
  • 2
    Hey. Any idea why I'm keep getting error about CollectionNavigationBuilder does not contain a definition for WithMany() ? am I missing some reference to a library or something? – pjrki May 05 '19 at 14:09
  • 1
    What can you do if you need some additional columns in `ProductRelations` table? – Sam Carlson Jul 18 '19 at 06:29
0

Lets take your Example:

Related table

  Related_id      PK
  Related_name
  Date

Product Table

  Product_id      PK
  Related_id      FK
  Product_Name
  Date

How to Represent it in EF

Related Model Class named as RelatedModel

  [Key]
  public int Related_id { get; set; }

  public string Related_name {get;set}

  public Datetime Date{get;set;}

Product Model Class named as ProductModel

   [Key]
  public int Product_id { get; set; }

  public string Product_name {get;set}

  public string Related_id {get;set}

  public Datetime Date{get;set;}

  [ForeignKey("Related_id ")]      //We  can also specify here Foreign key
  public virtual RelatedModel Related { get; set; } 

In this way we can Create Relations between Two table

Now In Case of Many to Many Relation I would like to take another Example here

Suppose I have a Model Class Enrollment.cs

public class Enrollment
{
    public int EnrollmentID { get; set; }
    public int CourseID { get; set; }
    public int StudentID { get; set; }
    public decimal? Grade { get; set; }
    public virtual Course Course { get; set; }
    public virtual Student Student { get; set; }
}

Here CourseID and StudentId are the two foreign Keys

Now I Have another Class Course.cs where we will create Many to Many Relation.

public class Course
{
    public int CourseID { get; set; }
    public string Title { get; set; }
    public int Credits { get; set; }
    public virtual ICollection<Enrollment> Enrollments { get; set; }
}

Hope This will help!!!

RL89
  • 1,866
  • 5
  • 22
  • 39
  • 2
    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. – Slauma Sep 12 '12 at 13:37