Entity Framework - Join table with composite key and a primary key

Question

I am struggling with the way entity framework handles join tables, specifically because entity framework requires that a join table has a composite key composed of the primary keys on the two related entities I want the hold the relationship for. The problem here is that I need to hold a relationship to the relationship so to speak.

This may be a problem with my database design or equally due to my lack of understanding with EF. It is probably best illustrated through example (see below);

I have three tables each with a primary key:-

Table : DispatchChannel
{ *DispatchChannelID integer }


Table : Format
{ *FormatID integer }


Table : EventType
{ *EventTypeID integer }

The relationship between EventTypes and DispatchChannels is held in EventTypeDispatchChannels (see below) since this only contains a composite key it is not pulled through into our model and entity framework takes care of maintaining the relationship.

Table : EventTypeDispatchChannels
{ EventTypeID integer, DispatchChannelID integer
}

My problem now arises because for each combination of EventTypeID and DispatchChannelID I want to hold a list of available formats, this would be easy if my EventTypeDispatchChannels table had a primary key therefore my other join table would look like this;

Table : EventTypeDispatchChannelFormats
{ EventTypeDispatchChannelID integer, FormatID integer
}

The absence of a primary key on EventTypeDispatchChannels is where I am struggling to make this work, however if I had the key then entity framework no longer sees this as a linked entity.

I'm relatively new to C# so apologies if I have not explained this so well, but any advice would be appreciated.

Gert Arnold Gert Arnold · Accepted Answer · 2013-04-02T20:54:36

The moment you want to give an association a more important role than just being a piece of string between two classes, the association becomes a first-class citizen of your domain and it's justified to make it part of the class model. It's also inevitable, but that's secondary.

So you should map EventTypeDispatchChannels to a class. The table could have its own simple primary key besides the two foreign keys. A simle PK is probably easier, so your table Format can do with a simple foreign key to EventTypeDispatchChannels for the one-to-many association.

You will lose the many to many feature to simply address dispatchChannel.Events. In stead you have to do

db.DispatchChannels.Where(d => d.DispatchChannelID == 1)
                   .SelectMany(d => d.EventTypeDispatchChannels)
                   .Select(ed => ed.Event)

On the other hand you have gained the possibility to create an association by just creating an EventTypeDispatchChannel and setting its primitive foreign key values. Many-to-many associations with a transparent junction table can only be set by adding objects to a collection (add an Event to dispatchChannel.Events). This means that the collection must be loaded and you need an Event object, which is more expensive in database round trips.

Entity Framework - Join table with composite key and a primary key

1 Answers