I’ve just spent a rather pleasant and sunny afternoon indoors trying to fix a problem with inserting entities that form many-to-many relationships in the .net Entity Framework.
I’ve been googling this error message:
Unable to update the EntitySet because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation.
all afternoon, trawling through the O’Reilly book and generally losing my hair.
Many-to-many relationships are characterised by what we call link or junction tables:
Here the MovieActors table is the junction table.
The key to getting many-to-many relationships is those little keys on the MovieActors table. They’re a pair of Foreign Keys linking back to their respective primary tables with a Primary Key over the top of them both.
To get many-to-many relationships to work in the Entity Framework, you need a primary key on the junction table! Or you can roll your own stored procedure insert statement, stick it in the SSDL designer mess and pray that no-one refreshes the model and overwrites it.
(Obviously you should have these keys in place anyway, but sometimes, in the big bad real world, the database design was created 20 years ago by a man in a hurry and changing the design may make the company walls crumble and the corporate empire fall. Gladly, here it was fine. Even more obviously, how did I expect the Entity Framework to magically realise what it was supposed to do on insert without these keys? Lessons have been learned.)