EF Code First Many to Many relationship creates a duplicate joining table?

Question

I'm trying to create a many to many relationship between Product and Category with a joining table using EF 6.4.4 and MySQL 8, but unfortunately without success?

public class AppDbContext : DbContext
{
    //Schema Tables
    public DbSet<Product> Products { get; set; }
    public DbSet<Category> Categories { get; set; }
    public DbSet<ProductCategory> ProductCategories { get; set; }
}

public abstract class BaseEntity
{
    [Key]
    public int ID { get; set; }
    public string Name { get; set; }
}
public class Product : BaseEntity
{
    public virtual IList<Category> Categories { get; set; }
}
public class Category : BaseEntity
{
    public virtual IList<Product> Products { get; set; }
}

now for the joining table I tried this:

public class ProductCategory
{
    [Key, Column(Order = 1)]
    public int ProductID { get; set; }
    [Key, Column(Order = 2)]
    public int CategoryID { get; set; }
    
    public Product Product { get; set; }
    public Category Category { get; set; }
}

and this:

public class ProductCategory : BaseEntity
{
    public int ID { get; set; }

    [ForeignKey(nameof(Product)), Column(Order = 1)]
    public int ProductID { get; set; }
    [ForeignKey(nameof(Category)), Column(Order = 2)]
    public int CategoryID { get; set; }

    public Product Product { get; set; }
    public Category Category { get; set; }
}

and also this:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    
    modelBuilder.Entity<ProductCategory>().HasKey(x => new { x.ProductID, x.CategoryID });
}

the real problem is no matter what I do EF creates a duplicate joining table?

products
categories
productcategories
productcategory1 (duplicate)

Edit: apparently you can't do this according to this post and this one two, but there is a workaround.

// a workaround for the problem
public abstract class BaseEntity
{
    [Key]
    public int ID { get; set; }
    public string Name { get; set; }
}

public class Product : BaseEntity
{
    public virtual IList<ProductCategory> ProductCategories { get; set; }
}
public class Category : BaseEntity
{
    public virtual IList<ProductCategory> ProductCategories { get; set; }
}
public class ProductCategory
{
    [Key, Column(Order = 1)]
    public int ProductID { get; set; }
    [Key, Column(Order = 2)]
    public int CategoryID { get; set; }

    public virtual Product Product { get; set; }
    public virtual Category Category { get; set; }
}

score 0 · Answer 1 · answered Mar 12 '21 at 09:26

0

I think you have created productcategory class twice. You can remove second one and add fk relationship s in first class definition

answered Mar 12 '21 at 09:26

Siva Koteswara Rao

129
1
6

I already have one ProductCategory class not two? I just tried two different ways. – Joseph Mar 12 '21 at 09:58

score 0 · Answer 2 · answered Mar 12 '21 at 10:23

You likely need to be explicit with the relationship mapping. I would get rid of the ProductCategories DbSet in the DbContext. If this is EF6 or EF Core 5 then I'd get rid of the ProductCategory entity definition all-together, especially if the table just consists of the two FKs/composite key:

EF 6

modelBuilder.Entity<Product>()
    .HasMany(x => x.Categories)
    .WithMany(x => x.Products)
    .Map(x => {
        x.ToTable("ProductCategories");
        x.MapLeftKey("ProductId");
        x.MapRightKey("CategoryId");
    });

EF Core < 5

For EF Core 2/3 the many-to-many needs to be mapped more as a many-to-one-to-many where the collections on each side need to be declared as ProductCategory entities.

public class Product
{
    // ...
    public virtual ICollection<ProductCategory> ProductCategories { get; set; } = new List<ProductCategory>();
}

public class Category
{
    // ...
    public virtual ICollection<ProductCategory> ProductCategories { get; set; } = new List<ProductCategory>();
}

EF Core 5

EF Core 5 adds UsingEntity to help define the joining table for many-to-many relationships leaving Product to have Categories (instead of ProductCategories) and Category to have Products (likewise).

modelBuilder.Entity<Product>()
    .HasMany(x => x.Categories)
    .WithMany(x => x.Products)
    .UsingEntity<ProductCategory>(
        x => x.HasOne(pc => pc.Product).WithMany().HasForeignKey(pc => pc.ProductId),
        x => x.HasOne(pc => pc.Category).WithMany().HasForeignKey(pc => pc.CategoryId));

EF Code First Many to Many relationship creates a duplicate joining table?

2 Answers2