none
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

    Wednesday, May 28, 2008 10:48 AM

Answers

  • 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

    Friday, May 30, 2008 12:33 AM
    Moderator

All replies

  • 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

     

    Wednesday, May 28, 2008 9:29 PM

  • 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
    Thursday, May 29, 2008 6:06 PM
  • 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

    Friday, May 30, 2008 12:33 AM
    Moderator

  • 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.
    Friday, May 30, 2008 7:35 AM
  •  

    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.

    • Proposed as answer by David Storfer Tuesday, August 04, 2009 3:12 PM
    Monday, September 29, 2008 1:45 AM
  •  

    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
    • Proposed as answer by Anup Dey Friday, September 28, 2012 8:42 AM
    • Unproposed as answer by Anup Dey Friday, September 28, 2012 8:42 AM
    Monday, October 27, 2008 9:27 PM
  • 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.
    • Proposed as answer by Nimble2 Tuesday, August 13, 2013 2:31 PM
    Thursday, January 29, 2009 5:26 PM
  • 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!!!
    Tuesday, February 17, 2009 3:57 AM
  • @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. 

    • Proposed as answer by MonacoFranz Sunday, February 19, 2012 12:44 PM
    Friday, September 18, 2009 1:17 PM
  • 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.
    • Proposed as answer by Dustin Frenton Monday, January 27, 2014 10:12 PM
    Wednesday, November 11, 2009 3:18 PM
  • http://weblogs.asp.net/kencox/archive/2009/09/09/fixing-the-system-data-updateexception-definingquery-and-no-lt-insertfunction-gt-error.aspx
    Monday, November 16, 2009 12:58 AM
  • 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

    • Proposed as answer by W.S.A Saturday, November 06, 2010 3:33 PM
    Monday, April 26, 2010 3:05 AM
  • 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

    • Proposed as answer by Anup Dey Friday, September 28, 2012 8:42 AM
    Saturday, May 01, 2010 7:05 AM
  • Thank you so much,with same error massage your answer solve my problem

    :)

    Thank you!

    Wednesday, June 09, 2010 9:28 AM
  • "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
    • Proposed as answer by hanorm Tuesday, August 10, 2010 7:15 AM
    Tuesday, July 06, 2010 11:28 PM
  • I'm facing this problem. How do I know if my view is updatable?

     

    Thursday, July 15, 2010 12:02 PM
  • @hipswich: Your post really helped me out. You deserve a medal for your post. Thanks!
    Tuesday, August 10, 2010 7:15 AM
  • THANK YOU! Helpfully!
    Saturday, November 06, 2010 3:18 PM
  • hipswich,

    Buddy you are a lifesaver!!!

     

    Thanks!!

    Monday, November 15, 2010 10:07 AM
  • hipswich,

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

    Thursday, November 18, 2010 8:17 PM
  • 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
    Wednesday, January 12, 2011 3:47 AM
  • 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
    • Proposed as answer by jackmp Tuesday, October 11, 2011 10:31 PM
    Thursday, February 17, 2011 4:56 PM
  • "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

    • Edited by jimNeocasa Tuesday, March 01, 2011 6:04 AM typo
    Tuesday, March 01, 2011 6:02 AM
  • I created a primary key and it works
    Monday, April 25, 2011 11:19 PM
  • Thanks alot man...it saved me..
    Tuesday, May 03, 2011 10:29 AM
  • Sweet!  thanks for the step by step.  Worked perfectly!
    Tuesday, September 06, 2011 7:15 PM
  • 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.

     


    • Edited by tonytsunds Thursday, September 29, 2011 7:58 AM
    Thursday, September 29, 2011 7:56 AM
  • 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

     

    Thursday, January 05, 2012 2:27 PM
  • hipswich,

    I love you man!

    • Edited by Macht59 Sunday, February 26, 2012 6:04 PM
    Sunday, February 26, 2012 6:04 PM
  • Correct
    Sunday, April 01, 2012 3:11 PM
  • hi good answer 

    Jacob Michael

    • Proposed as answer by jacobsome Friday, April 06, 2012 3:36 AM
    • Unproposed as answer by jacobsome Friday, April 06, 2012 3:36 AM
    Friday, April 06, 2012 3:36 AM
  • 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
    Thursday, May 03, 2012 7:05 PM
  • 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!


    • Edited by nisharafic Wednesday, June 20, 2012 4:57 AM
    Wednesday, June 20, 2012 4:45 AM
  • Thank you so much, your answer solved my problem

    :)

    Thank you

    Monday, June 25, 2012 2:35 PM
  • Thanks a lot. I solved my problem.
    Friday, December 07, 2012 11:50 AM
  • Thank you for this.  My table did not have a primary key.  Adding one fixed the problem.
    • Edited by shinks1 Monday, December 10, 2012 4:32 PM
    Monday, December 10, 2012 4:32 PM
  • 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

    Tuesday, January 08, 2013 8:50 AM
  • Thank u I solved my problem
    Thursday, September 12, 2013 7:03 AM
  • You wrote:

    "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 suggestion, but I got the following error when I ran my app:

    Invalid object name <objectName>.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Invalid object name <objectName>.

    I'm going to attempt to create my own update stored proc and see if that helps. Keep your fingers crossed....

    Tekkno--

    Saturday, November 09, 2013 3:59 PM
  • It ended up being that my primary key was missing on a table as well.
    Monday, January 27, 2014 10:12 PM