Unable to update the EntitySet ... because it has a DefiningQuery and no <DeleteFunction> element exists in the

Question

Hello,

I have an entity that's mapped to a table and a view (the view returns the primary key along with a computed name). When deleting an entity (using DynamicData scaffolding) I receive the following error:

Unable to update the EntitySet 'TestInstanceName' because it has a DefiningQuery and no <DeleteFunction> element exists in the <ModificationFunctionMapping> element to support the current operation.

where TestInstanceName is the name of the view. I've added StoreGeneratedPattern=Computed to the view column.

Please help!
Thanks

Answer 1

Unfortunately, when you generate a model including a view, the mapping to that view gets setup with a DefiningQuery which is a way to signal to the framework that standard DML operations to the view won't work (because in the general case views don't support DML).  The normal expectation is that you would write stored procedures for the create, update and delete operations and map them to the entityset so that the view can be used for reads and the stored procedures for writes.

 

- Danny

 

Answer 2

Thanks ...

What I am trying to do actually is having a column precalculated using info from related tables. I was computing that column using a view.
Is there another way to do this ?

Thanks again

Answer 3

As Danny says, the Entity Framework doesn't know whether a given view is updatable or not, so it adds the <DefiningQuery> element in order to safegaurd against having the framework attempt to generate queries against a non-updatable view.

 

If your view is updatable you can simply remove the <DefiningQuery> element from the EntitySet definition for your view inside of the StorageModel section of your .edmx, and the normal update processing will work as with any other table. 

 

If your view is not updatable, you will have to provide the update logic yourself through a "Modification Function Mapping".  The Modification Function Mapping calls a function defined in the StorageModel section of your .edmx.  That Function may contain the name and arguments to a stored procedure in your database, or you can us a "defining command" in order to write the insert, update, or delete statement directly in the function definition within the StorageModel section of your .edmx.

 

Of the two options, if your view is updatable (which it sounds like it may be) the easiest is certainly to remove the <DefiningQuery> inserted by the designer.

 

HTH,

 

-Mike

Answer 4

I've regenerated the model and now it works ! It appears the key to get it working is to not have StoreGeneratedPattern set on the view columns.

Answer 5

 

I was having the same error on a Table.  I check the DataSource and there was in fact a DefiningQuery specified.  It turned out that the table didn't have a PrimaryKey assigned.  I created a primary key on the Table and Updated the data model and success.

 

I just thought i would add this post in case anyone else has this issue.

Answer 6

 

I was really glad I found this post. I was having the same issue, and I thought that regenerating the model will solve the problem until I realize that my table didn't have a PrimaryKey assigned as well. Thanks

Answer 7

Just to make sure this appears at the top of google's ranking (!) - I had the same problem with a table. I forgot to set the primary key before generating the model. I set the primary key on the database table level, refreshed the model (I had to remove some spurious keys on the entity on the conceptual level that had previously been created), rebuilt everything and it all worked.

Answer 8

I have similar problem.  I have two tables Many to Many and 1 table with two foreign keys between them.

Problem when update relationship`: Unable to update the EntitySet 'PartnersNpf_Cities' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation.

PartnersNpf_Cities - table with foreign keys. I don't understand where is problem..... 

Help please!!!

Answer 9

@MrBlondin: Sorry to reply to your message 6 months after you need it. :)

I have solved the error, I belive. I have two tables mapped with a many-to-many table in between, which is generated by NHibernate. The many-to-many contains only two columns, which are, of course, the foreign keyes. The ADO Entity Model gets generates quite nicely, but when I start adding and removing relationships, it blows up with the error message above. 

What I did, and which worked, was to select BOTH the foreign key columns of the many-to-many table in SQL Server Manager, and right-click, and click Add Primary key, making them both the primary key in the database. Then, regenerate the ADO ENtity Model from the database. It now works.

Only took me three hours to solve this bugger. I am NOT happy with entity framework so far. 

Answer 10

I was getting the same error:

Unable to update the EntitySet 'TestInstanceName' because it has a DefiningQuery and no <DeleteFunction> element exists in the <ModificationFunctionMapping> element to support the current operation.

After reading through the answers on here, I realized that I hadn't added the primary key to my tables. I did that and went into my Entity Data Model and refreshed the models. I had to then remove the extra Entity Keys that were added for some reason. Once I did that, I ran debug again and was able to add data to my database successfully.

Answer 11

http://weblogs.asp.net/kencox/archive/2009/09/09/fixing-the-system-data-updateexception-definingquery-and-no-lt-insertfunction-gt-error.aspx

Answer 12

In case anyone comes across this thread and is having trouble with Sql Ce, you can create multi-column primary keys in Sql Ce.  Here's an example:

CREATE TABLE StackPrerequisites (
  [TargetStackID]    UNIQUEIDENTIFIER NOT NULL,
  [PrerequisiteStackID] UNIQUEIDENTIFIER NOT NULL,
  PRIMARY KEY ([TargetStackID], [PrerequisiteStackID])
);

That allows Entity Framework to properly handle the many-to-many join table case.

David Cater

Answer 13

I Had the same problem but I solved by adding the Primary key to the table and added the entity that solved the problem

VJ

Answer 14

Thank you so much,with same error massage your answer solve my problem

:)

Thank you!

Answer 15

"If your view is updatable you can simply remove the <DefiningQuery> element from the EntitySet definition for your view inside of the StorageModel section of your .edmx, and the normal update processing will work as with any other table. "

 

This is the case for me. Simply removing <DefiningQuery> resulted in another error.  I followed the steps of this post except the last one. For your convenience, I copied the 4 steps from the post   that I followed to solve the problem as following:

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)

---

hz

Answer 16

I'm facing this problem. How do I know if my view is updatable?

 

Answer 17

@hipswich: Your post really helped me out. You deserve a medal for your post. Thanks!

Answer 18

THANK YOU! Helpfully!

Answer 19

hipswich,

Buddy you are a lifesaver!!!

 

Thanks!!

Answer 20

hipswich,

How did you know that? Thanks a bunch !!!

Answer 21

Primary key did it for me thanks for the post.

I had to drop the database and recreated to change the primary key... newbie SQL person that I am.... When I scripted the drop and add it didn't bring back the table so I had to recreate it....arghhhh....

---

Javaman

Answer 22

So where do you find these spurious keys that need to be deleted? I have the same problem. But I have no idea where to look for these keys.

---

Samuel Miller

Answer 23

"If your view is updatable you can simply remove the <DefiningQuery> element from the EntitySet definition for your view inside of the StorageModel section of your .edmx, and the normal update processing will work as with any other table..."

 

I tried this but it resulted in another error.  The fix for me was adding a single primary key to the table.  The table was a mapping table and had a compound (3 columns) primary key which I guess EF doesn't like.

Jim

Answer 24

I created a primary key and it works

Answer 25

Thanks alot man...it saved me..

Answer 26

Sweet!  thanks for the step by step.  Worked perfectly!

Answer 27

This is solution!!!

 

I was having the same error on a Table.  I check the DataSource and there was in fact a DefiningQuery specified.  It turned out that the table didn't have a PrimaryKey assigned.  I created a primary key on the Table and Updated the data model and success.

 

I just thought i would add this post in case anyone else has this issue.

By David Storfer is the one i credited for this thanks man.

 

Answer 28

Hello every body,

It seems you did not have primary key in your table definition

try to examine whether your table has a primary key or not?

good luck

 

Answer 29

hipswich,

I love you man!

Answer 30

Correct

Answer 31

hi good answer 

---

Jacob Michael

Answer 32

It was incredibly frustrating to have to remove the defining queries every time I updated my schema, and this minor change solved an issue I've been working around for quite some time .   Thank you so much for posting this answer

Answer 33

Unable to update the EntitySet 'TestInstanceName' because it has a DefiningQuery and no <DeleteFunction> element exists in the <ModificationFunctionMapping> element to support the current operation.

Solution

---------------

 Include the primary key in the table and update the EntityFramework and it works fine 

Hope it helps!

Answer 34

Thank you so much, your answer solved my problem

:)

Thank you

Answer 35

Thanks a lot. I solved my problem.

Answer 36

Thank you for this.  My table did not have a primary key.  Adding one fixed the problem.

Answer 37

Even though the exception message is a bit cryptic, the issue is caused by missing primary key constraint in the database.

Set the Primary Key in the db and update the entity model, and then you must be able to do Save operations:

It is explained here:

http://dotnet-programming-solutions.blogspot.com/2013/01/unable-to-update-entityset-because-it.html