– It has a DefiningQuery but no InsertFunction element… err

The Question :

124 people think this question is useful

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:

int idA (identity, primary key)

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:

  1. Right click on the edmx file, select Open with, XML editor
  2. Locate the entity in the edmx:StorageModels element
  3. Remove the DefiningQuery entirely
  4. Rename the store:Schema=”dbo” to Schema=”dbo” (otherwise, the code will generate an error saying the name is invalid)
  5. Remove the store:Name property

I left the key as it was, since it was OK to me that both the columns are part of the key.

The Question Comments :
  • Thanks for the update – the step-by-step instructions helped this EF newb get the ASP.NET MVC tutorial app working!
  • Thnx for this! I had a problem that EF didn’t generate proper edmx file for sql server 2000 table that HAS primary key. But this instruction saved me 🙂
  • 3.5 years later, and this post is still helping ppl, in this case, ME! … To: Palantir for the descriptive step by step (it worked) and thanks to Alex >>> Bravo !
  • The solution worked for me .. Thanks
  • The secret was removing “store:” from schema definition. Very nice

The Answer 1

141 people think this answer is useful

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:

  1. And convince the EF that what it thinks is a view is really a table
  2. Or add the appropriate modification functions

In your case I recommend (1).

The Answer 2

13 people think this answer is useful

Just Add a primary key to the table. That’s it. Problem solved.


The Answer 3

6 people think this answer is useful

I was missing a primary key on my table and got this error message. One thing I noted was after I added the key to the table, I needed to clear the table from the edmx using the designer, save the edmx, then update it again to add the table back in. It wasn’t picking up the key since it was already assigned as a view. This didn’t require editing the edmx manually.

The Answer 4

5 people think this answer is useful

Add primary key to table, delete the model from the edmx model, then select update from database, build and run…… works

The Answer 5

1 people think this answer is useful

@Palantir. Verify that both of you tables have Primary Keys set, and be careful with multiple primary keys set in a table.

The Answer 6

0 people think this answer is useful
  1. You need to manually open the .EDMX file in notepad or notepad++ or in any text editor of your choice.
  2. Locate the entry in edmx:StorageModels in file opened in step1.
  3. Find the DefiningQuery element and remove this tag entirely.
  4. Find the store:Schema=”dbo” to Schema=”dbo” (if you skip this step it will generate error of the name is invalid).
  5. Save and close the file.

Hope it will solve the problem.

Add a Comment