Let me expand @bubi's answer:
By default, when you define many-to-many relationship (using attributes or FluentAPI), EF creates it (add additional table to DB) and allows you to add many products to a category and many categories to a product. But it doesn't allow you to access the linking table rows as entities.
If you need such feature, for example you what to manage these links some way like mark them as "deleted" or set a "priority", you need to:
- Create new Entity (ProductCategoryLink)
- Add it to your Context as another DbSet
- Update relations in Product and Category entities accordingly.
For you it could like:
Entities
public class Product
{
[Key]
public long ProductId { get; set; }
public string Name { get; set; }
public string Description { get; set; }
[InverseProperty("Product")]
public ICollection<ProductCategoryLink> CategoriesLinks { get; set; }
}
public class Category
{
[Key]
public long CategoryId { get; set; }
public string Name { get; set; }
[InverseProperty("Category")]
public ICollection<ProductCategoryLink> ProductsLinks { get; set; }
}
public class ProductCategoryLink
{
[Key]
[Column(Order=0)]
[ForeignKey("Product")]
public long ProductId { get; set; }
public Product Product { get; set; }
[Key]
[Column(Order=1)]
[ForeignKey("Category")]
public long CategoryId { get; set; }
public Category Category { get; set; }
}
I used attribute-way to define relations as I prefer this approach more. But you can easily replace it by a FluentAPI with two one-to-many relations:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
// Product to Links one-to-many
modelBuilder.Entity<ProductCategoryLink>()
.HasRequired<Product>(pcl => pcl.Product)
.WithMany(s => s.CategoriesLinks)
.HasForeignKey(s => s.ProductId);
// Categories to Links one-to-many
modelBuilder.Entity<ProductCategoryLink>()
.HasRequired<Category>(pcl => pcl.Category)
.WithMany(s => s.ProductsLinks)
.HasForeignKey(s => s.CategoryId);
}
Context
It's not required but most likely you'll need to save links directly to context, so let's define a DbSet for them.
public class MyContext : DbContext
{
public DbSet<Product> Products { get; set; }
public DbSet<Category > Categories{ get; set; }
public DbSet<ProductCategoryLink> ProductCategoriesLinks { get; set; }
}
Two ways of implementation
Another reason why I used attributes to define relations is that it shows (both marked with [Key] attribute (also pay attention to [Column(Order=X)] attribute])) that two FKs in ProductCategoriesLink entity become a composite PK so you don't need to define another property like "ProductCategoryLinkId" and mark it as a special PK field.
You always could find desired linking entity all you need is just both PK's:
using(var context = new MyContext)
{
var link = context.ProductCategoriesLinks.FirstOrDefault(pcl => pcl.ProductId == 1
&& pcl.CategoryId == 2);
}
Also this approach restricts any chance to save several links with the same Product and Category as they are complex key. If you prefer the way when Id is separated from FK's you'll need to add UNIQUE constraint manually.
Whichever way you choose you'll reach your aim to manipulate the links as you need and add additional properties to them if you need.
Note 1
As we defined many-to-many links as separate entity Product and Category don't have direct relation to each other anymore. So you'll need to update your code:
Instead of adding Product directly to Category or Category directly to Product now you need to define a ProductCategoryLink entity and save it using one of three ways depending on your logic's context:
public void AddProductToCategory(Product product, Company company)
{
using (var context = new MyContext())
{
// create link
var link = new ProductCategoryLink{
ProductId = product.ProductId, // you can leave one link
Product = product, // from these two
CategoryId = category.CategoryId, // and the same here
Category = category
};
// save it
// 1) Add to table directly - most general way, because you could
// have only Ids of product and category, but not the instances
context.ProductCategoriesList.Add(link);
// 2) Add link to Product - you'll need a product instance
product.CategoriesLinks.Add(link);
// 3) Add link to Category - you'll need a category instance
category.ProductLinks.Add(link);
context.SaveChanges();
}
}
Note 2
Also remember as your properties now navigate to ProductCategoryLinks (not to Products for categories and not for Categories for products) if you need to query the second linked entity you need to .Include() it:
public IEnumerable<Product> GetCategoryProducts(long categoryId)
{
using (var context = new MyContext())
{
var products = context.Categories
.Include(c => c.ProductsCategoriesLinks.Select(pcl => pcl.Product))
.FirstOrDefault(c => c.CategoryId == categoryId);
return products;
}
}
UPD:
There is a same question with detailed answer on SO:
Create code first, many to many, with additional fields in association table
