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