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
– Yuliam Chandra, Commented Aug 19, 2014 at 8:47

Community · Accepted Answer · 2017-05-23 12:13:21Z

2

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.

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Aug 19, 2014 at 8:43

JotaBe

39.3k9 gold badges102 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related