Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

The context

We're building a web application using Entity Framework 5.0. One of the requirements is that it should be possible for the administrators to link related products so that when someone browses to a product we can render a list "You might also like these products:".

Since a product can be linked to many products we need a many-to-many relationship for this.

The table in the database could look something like this:

 _________________________
| LinkedProducts          |
|-------------------------|
| ProductId1 | ProductId2 |
|------------|------------|
|     1      |     2      |
|------------|------------|
|     1      |     3      |
|------------|------------|
|     2      |     4      |
|------------|------------|

An additional requirement is that the linking should be bidirectional. This means that with the sample data from the table above, when you browse to product 2, you should get product 1 and 4 in the list. So for a given ProductId, you should be able to get its linked products from column ProductId1 and ProductId2.

What I've come up with

The product entity:

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Product> References { get; set; }
    public virtual ICollection<Product> ReferencedBy { get; set; }

    public Product()
    {
        References = new List<Product>();
        ReferencedBy = new List<Product>();
    }
}

The product mapping:

public class ProductMapping : EntityTypeConfiguration<Product>
{
    public ProductMapping()
    {
        HasKey(t => t.ProductId);

        Property(t => t.Name).IsRequired().HasMaxLength(150);

        ToTable("Products");
        Property(t => t.ProductId).HasColumnName("ProductId");
        Property(t => t.Name).HasColumnName("Name");

        HasMany(x => x.References).WithMany(x => x.ReferencedBy).Map(map =>
            {
                map.ToTable("LinkedProducts");
                map.MapLeftKey("ProductId1");
                map.MapRightKey("ProductId2");
            });
    }
}

This all works. To display the list of linked products for a certain product, I can just get the union of References and ReferencedBy:

var product = db.Find(2);
var linkedProducts = product.References.Union(product.ReferencedBy);

The problem

As I said, this works, but I don't really like it and I was wondering if there is a better way to deal with a situation like this when working with Entity Framework.

The solution

I liked Ryathal's suggestion to add two records to the database when linking products. So, when linking product 1 to product 2, I now insert two records: { 1, 2 } and { 2, 1 }.

This also allows me to remove the ReferencedBy collection in the Product class so that only one collections remains: References.

To link to products:

product1.References.Add(product2);
product2.References.Add(product1);

To remove the link between products:

product1.References.Remove(product2);
product2.References.Remove(product1);

Here is my new Product class with the mapping:

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Product> References { get; set; }

    public Product()
    {
        References = new List<Product>();
    }
}

The product mapping:

public class ProductMapping : EntityTypeConfiguration<Product>
{
    public ProductMapping()
    {
        HasKey(t => t.ProductId);

        Property(t => t.Name).IsRequired().HasMaxLength(150);

        ToTable("Products");
        Property(t => t.ProductId).HasColumnName("ProductId");
        Property(t => t.Name).HasColumnName("Name");

        HasMany(x => x.References).WithMany().Map(map =>
            {
                map.ToTable("LinkedProducts");
                map.MapLeftKey("ProductId1");
                map.MapRightKey("ProductId2");
            });
    }
}
share|improve this question
    
I've got practically this exact same schema as the original you posted, where I have a table referencing itself through a secondary many-to-many mapping table just like you have. The issue I'm having with EF6.1 is that unless I specifically remember to use the Include("References").Include("ReferencedBy") hints every single time I query the DbContext, both the References & ReferencedBy collections are always empty. But, since you said your (first) example worked for you, I'm having a hard time figuring out what's different in my case---or did you have to use these include hints as well? –  Funka Jun 27 at 20:53
    
Found it! Needed to have virtual keyword on my Navigation properties. Your example helped me figure out what mine was missing. Sooo much happier now. –  Funka Jun 27 at 21:09

2 Answers 2

up vote 3 down vote accepted

Follow good database design and your problems will start getting easier, don't think about entity framework when designing your database structures. Create your Product table and create you related product table with source product and related product ids. The requirement for linking to be bi directional is just a business rule to be handled with the application on creating related products to always insert two rows into your mapping table. Everything stays nice and simple this way.

share|improve this answer
    
I know what the table in the database should look like, but it still needs to be mapped through Entity Framework somehow. But if I understand you correctly, you say that when I want to link Product 1 to Product 2, I should insert two records? { 1, 2 } and { 2, 1 }? –  Kristof Claes Apr 3 '13 at 13:11
1  
@KristofClaes yes, you have 2 relations you need two records. trying to make one row mean more than one thing is extremely bad. –  Ryathal Apr 3 '13 at 14:17
    
Thanks. I've updated my question to include the solution based on your answer. –  Kristof Claes Apr 4 '13 at 9:56
    
One advantage of this solution (i.e., thinking about this model as two separate one-way links) is that you may actually have cases where this is wanted. For example, the toy robot product may want to relate to a certain type of batteries. Whereas the battery products may want to relate to something different such as battery chargers or larger size/bulk packages. You also benefit from the clustered index on the junction/relationship table making this query faster. (Although you can still index the other solution for "reverse" link queries I suppose...) –  Funka Jun 27 at 21:30

I have always felt weird for creating explicit entities for my many-to-many tables. EF tries to associate many-to-many tables with some parent table. However, this usually wasn't "good enough". Either it didn't make sense for properties to appear in the parent entity or my many-to-many tables were entities in their own right.

I was constantly fighting EF, having to removing associations every time I updated my model based on the database. However, I think it was worth it.

This might be a case where you making the ProductaMapping table an explicit entity is good design.

share|improve this answer
    
I've found that as long as the relationship/junction table has two (any only two!) columns in it (to represent the left key and the right key), with them both comprising the composite primary key on the table, Entity Framework won't need nor want to create this as a "many-to-many relationship entity". As soon as you add a third column to this relationship table however, it does get promoted to a separate entity---otherwise, there wouldn't be any place to represent that new property! –  Funka Jun 27 at 21:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.