I have two classes, Product and Invoice, with many to many relationship between them

Entity framework has created three tables for me, Products, Invoices, and a join table called, InvoiceProducts (or something like that)

As I have learned, by default, entity framework creates a composite primary key on the fields InvoiceId and ProductId in the join table, so together, those foreign keys, must be unique.

Well, this is an issue, cause sometimes a person wants to order two of the same item, in the same invoce.

I saw this answer, but it only helps if you are creating the join table in C# as well. But when using entity framework, I no longer create a join table, I just Create my classes as the following:

public class Invoice
{
    public int Id { get; set; }
    public string InvoiceNumber { get; set; }
    public double TotalPrice { get; set; }
    public List<Product> Products { get; set; }
}
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public double Price { get; set; }
    public List<Invoice> Invoices { get; set; }
}
public class DB : DbContext
{
    public DB() : base("MyConnectionstringName") { }

    public DbSet<Invoice> Invoices { get; set; }
    public DbSet<Product> Products { get; set; }
}

So, any thoughts?

CodePudding user response：

This is the default, convenient mapping for many-to-many tables where you just want to associate the two tables with one another. Something like what you describe could involve adding something like a "Quantity" to the InvoiceProducts table. (rather than duplicating the same ProductId InvoiceId combination)

This would involve adding an InvoiceProduct entity:

public class InvoiceProduct
{
    [Key, ForeignKey(nameof(Invoice), Column(Order=0)]
    public int InvoiceId { get; set; } 
    [Key, ForeignKey(nameof(Product), Column(Order=1)]
    public int ProductId { get; set; } 

    public int Quantity { get; set; }

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

}

The navigation properties in your Invoice and Product entities need to be changed to InvoiceProducts rather than Products and Invoices respectively. From there you can still access the respective collection, but it's a little less convenient because the collection will be typed to InvoiceProduct which allows you to get to the Quantity column for the relationship. For instance to list the Products for an invoice you would need to use invoice.InvoiceProducts.Select(x => x.Product); rather than invoice.Products.

The default many-to-many mapping is more convenient, but limited to just representing the relationship. Mapping the joining table entity gives you more flexibility for requirements like this.

CodePudding user response：

adding a quantity column seems architecturally wrong to me, doesn't it to you?

No, but it's your data model. If you want that then the model would be something like

public class InvoiceLine
{
    public int InvoiceId { get; set; } 
    public int InvoiceLineId { get; set; } 

    public int ProductId{ get; set; } 

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

}

Configured like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Invoice>()
        .HasMany(i => i.Products)
        .WithMany(p => p.Invoices)
        .UsingEntity<InvoiceLine>(
          e => e.HasOne( il => il.Product)
                .WithMany()
                .HasForeignKey(il => il.ProductId),
          e => e.HasOne(il => il.Invoice)
                .WithMany()
                .HasForeignKey(il => il.InvoiceId),
          e => e.HasKey(e => new { e.InvoiceId, e.InvoiceLineId })
        );


    base.OnModelCreating(modelBuilder);
}

Note how you still have a Many-to-Many and don't have to directly deal with the InvoiceLine entities. And it creates the table like

  CREATE TABLE [InvoiceLine] (
      [InvoiceId] int NOT NULL,
      [InvoiceLineId] int NOT NULL,
      [ProductId] int NOT NULL,
      CONSTRAINT [PK_InvoiceLine] PRIMARY KEY ([InvoiceId], [InvoiceLineId]),
      CONSTRAINT [FK_InvoiceLine_Invoice_InvoiceId] FOREIGN KEY ([InvoiceId]) REFERENCES [Invoice] ([Id]) ON DELETE CASCADE,
      CONSTRAINT [FK_InvoiceLine_Product_ProductId] FOREIGN KEY ([ProductId]) REFERENCES [Product] ([Id]) ON DELETE CASCADE
  );


  CREATE INDEX [IX_InvoiceLine_ProductId] ON [InvoiceLine] ([ProductId]);