This thing is driving me crazy, and the error is quite meaningless to me:
Unable to update the EntitySet 'TableB' because it has a DefiningQuery and no element exists in the element to support the current operation.
My tables are put like this:
TableA int idA (identity, primary key) ... TableB int idA (FK for TableA.idA) int val
TableB has no defined primary key in the SQL server. The Entity Framework has imported the table and the association and set both fields as key. But it will output that error when I try to do an insert into the table!
What's wrong??
Edit: As suggested by Alex, the solution was this:
I left the key as it was, since it was OK to me that both the columns are part of the key.
Well when a table is encountered without a PrimaryKey it is treated as a View.
And views show up in the EDMX file (open in an XML editor to see) in the StorageModel\EntitySet[n]\DefiningQuery element.
When you have a DefiningQuery the Entity becomes readonly unless you add modification functions. You need 3 modifications functions (aka Stored Procedures) one for each of Insert, Update and Delete.
But you have two options:
Change the key definion:
In your case I recommend (1).
Just Add a primary key to the table. That's it. Problem solved.
ALTER TABLE <TABLE_NAME>
ADD CONSTRAINT <CONSTRAINT_NAME> PRIMARY KEY(<COLUMN_NAME>)