Add a field to a relation table and access row values through code

Question

Using EF code first and Fluent, I've created two tables containing data and a third table which is the "relation table" (not sure what the 'official' word for it is?). Each row holds Id's from both tables and defines a many-to-many relation between the two.

The tables hold purchase orders and products. Now I need to specify the amount of products in a purchase order as well, so my most logical conclusion was to add a field in the relation table that specifies the quantity, so that the table will become:

orderId | productId | quantity
  83923 |    867392 |      100
  83923 |    865392 |      250
  83923 |    323392 |       50

Is this possible using EF code first? I couldn't find a way, but it's hard to search for a solution since I find it hard to concisely describe what I need to do. I thought I could add a field using the designer, which works, but I have no idea how to access the value through code now.

My tables:
The field/table names are all in Dutch, but hopefully it's understandable & readable.

-- Purchase orders
CREATE TABLE [dbo].[InkoopOrders] (
    [InkoopOrderId]         INT            IDENTITY (1, 1) NOT NULL,
    [Kenmerk]               NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_dbo.InkoopOrders] PRIMARY KEY CLUSTERED ([InkoopOrderId] ASC)
);

-- Products
CREATE TABLE [dbo].[Artikelen] (
    [ArtikelId]        INT            IDENTITY (1, 1) NOT NULL,
    [Kenmerk]          NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_dbo.Artikelen] PRIMARY KEY CLUSTERED ([ArtikelId] ASC)
);

-- Relation table
CREATE TABLE [dbo].[InkoopOrderArtikel] (
    [InkoopOrderId] INT NOT NULL,
    [ArtikelId]     INT NOT NULL,
    CONSTRAINT [PK_dbo.InkoopOrderArtikel] PRIMARY KEY CLUSTERED ([InkoopOrderId] ASC, [ArtikelId] ASC),
    CONSTRAINT [FK_dbo.InkoopOrderArtikel_dbo.InkoopOrders_InkoopOrderId] FOREIGN KEY ([InkoopOrderId]) REFERENCES [dbo].[InkoopOrders] ([InkoopOrderId]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.InkoopOrderArtikel_dbo.Artikelen_ArtikelId] FOREIGN KEY ([ArtikelId]) REFERENCES [dbo].[Artikelen] ([ArtikelId]) ON DELETE CASCADE
);

This was achieved through creating the two models, and defining the relation in Fluent:

modelBuilder.Entity<InkoopOrder>()
    .HasMany(o => o.Artikelen)
    .WithMany(a => a.InkoopOrders)
    .Map(
        m => {
            m.MapLeftKey("InkoopOrderId");
            m.MapRightKey("ArtikelId");
            m.ToTable("InkoopOrderArtikel");
        }
    );

it's called _join / junction table_ for table that maps many to many relationship — Yuliam Chandra, Aug 19 '14 at 08:47

score 2 · Accepted Answer · edited May 23 '17 at 12:13

In EF, in may to many relationships, the association table can only have the Key columns of the related tables. So, no, you cannot do that with a many to many using EF. If you wanted to do this, you'd need to specify two one-to-may relationships (from both many to many tables, to the association table). Look at this SO answer for a full explanation.

However, I don't understand why you want to use a many to many. You simply need to use the classical Order table with a child OrderDetail table that has the ProductId, UnitPrice, Units, Total, and so on. Forget about using the many to many for this case. It's much easier in this way.

Add a field to a relation table and access row values through code

1 Answers1