Entity Framework Many-to-Many Relationships

I was 0wn3d.  Entity Framework was kicking my butt around and I couldn’t figure out why it couldn’t recognise a many-to-many relationship in my database using an association table.  Just a error 3002 over and over…

HorizontalRuleWide

I created my database first, and was generating my model from the database.  I build up the database in Management Studio and a Visual Studio Database project, and I was all set to create my entity model from the database.  This is what I was shown as the model:

broken model

That’s not right!  I wanted a many-to-many relationship between Trip and Location!

After digging around on the internet, the entity model, and my database schema, I discovered a small error in the database that I missed:

baddefinition

I had only two columns that had foreign keys to the related tables (storing addition data in the association table will cause other problems), but I had allowed one association column to allow NULLs.

For the entity framework to recognise an association table, the association columns cannot allow NULL values.

After regenerating the model, things looked a lot better…

partialfix 

…but it is not many-to-many relationship.  I attempted to change the relationship to many-to-many, but that caused an error:

Error 3002: Problem in mapping fragments starting at line 139:Potential runtime violation of table Trips_Locations's keys (Trips_Locations.TripId): Columns (Trips_Locations.TripId) are mapped to EntitySet Trips_Locations's properties (Trips_Locations.Trips.Id) on the conceptual side but they do not form the EntitySet's key properties (Trips_Locations.Locations.Id, Trips_Locations.Trips.Id).

This error is basically telling me that my model does not match my database.  After digging some more, I realised that there was another error in the database schema: I constructed the primary key on only one association column!

In an association table, the primary key must consist of both association columns.

After correcting this error, I finally arrived at what I was looking for:

correct

Pingbacks and trackbacks (1)+