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