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…
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:
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:
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…
…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:
