Problem regarding Many to Many relationship in EF code first

Question

I have Products table and Customers table. Thus there is many to many relationship between them. This is my code to create that relationship using ModelBuilder:

  modelBuilder.Entity<Customer>().
                    HasMany(c => c.ProductsPurchased).
                        WithMany(p => p.Customers).Map(m =>
                                            m.MapLeftKey("CustomerId").
                                            MapRightKey("ProductId").
                                            ToTable("CustomersXProducts"));

Problem here is the Join table contains primary key of CustomerId and ProductId. This essentially means one customer can purchase the same product only one time. How can I resolve this issue? I don't want CustomerId to be a primary key in my join table.

Answer 1

You cannot resolve the issue with your database. You generally need some additional unique column to be primary key of your junction table or you need some addition data column to form composite key with both CustomerId and ProductId. That will lead to change in your model. You will need to expose junction table as an entity - Customer and Product entities must be related to the new entity.

Perhaps you will need this anyway. It is not very common to have such relation without any additional data. Perhaps your model needs bigger change. For example customer tracking system usually uses some form of entities like Customer, Order, OrderItem, Product so there is no relation between Customer and Product directly.