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

When working with ADO.NET Entity Data Model, its often common that we generate entity schema for more than a single table from our Database.  With Entity Model generation automated by Visual Studio, it becomes even more tempting to create and work with entity models to achieve an object mapping relationship.

One of the errors that you might hit while trying to update an entity set either programmatically using context.SaveChanges or while using the automatic insert/update code generated by GridView etc., is “Unable to update the EntitySet <ENTITY NAME> because it has a DefiningQuery and no <UpdateFunction> element exists in the <ModificationFunctionMapping> element to support the current operation”

While the description is pretty lengthy, the immediate thing that would come to our mind is to open our the entity model generated code and see if you can update it accordingly.

However, the first thing to check is that, if the Entity Set is generated from a table, whether the table has a primary key definition.  Most of the times, we create tables with primary keys.  But some reference tables and tables which don’t have a primary key cannot be updated using the context of Entity and hence it would throw this error.  Unless it is a View, in which case, the default model is read-only, most of the times the above error occurs since there is no primary key defined in the table.

Once you add a primary key constraint to the table in the database design, there are couple of steps to do until you can resolve the issue

1. Open the EDMX file in the design mode

2. Right click on the design view and select "Modal Browser"

3. The Modal Browser window opens on the right hand side

4. Select the <ModelName>.Store and right click

5. Select "Update Model from Database"

6. It will give you different options to add Tables, Delete Tables and a Refresh tab which mentions a list of database types that would be refreshed

7. If you are just getting the updated model after adding the primary key constraint as in our example, you can directly click "Finish"

8. At this point, we would expect everything should work fine since the primary key constraint is added and the build should be fine to go through

9.  But if you build the solution/project you would receive a "3002 Error" describing that the storage models and conceptual models are not compatible.

10.  You need to manually open the Model1.edmx again.  Right click on the Model1.edmx and select "Open with"

11. Then select "XML (Text) Editor"

12. It will ask you confirmation to close the existing open model.  Confirm by pressing "Yes"

13. This would present you an XML view of the model (I dont know a better way of directly coming to this view, if any post, in comments)

14. Find the section <edmx:ConceptualModels>

15. Under that go the EntityTypes section with the name of the table which you updated with primary key

16.  You will find that under the <Key> collection, additional columns defined as PropertyRef

17. You need to the remove the additional columns and just have the Primray key column within the <Key> section

18.  This is updated in the StorageModel section automatically but not in the conceptualmodel as of today.

19.  Once you make this change and save, you should be able to build the project successfuly and also be able to update it programmatically by code

20. As I write this, I am also raising it in Microsoft Connect as to why the "update model" doesn't do a clean job of updating the schema.  Would post clarifications any, if I receive.

There are other reasons why this error could popup which I am not going into for the sake of simplicity of the post.  If you find something new, please feel free to share it in comments.

Hope this helps.

Cheers !!!

Print | posted on Monday, May 31, 2010 1:12 AM

Comments on this post

No comments posted yet.

Your comment:

 (will show your gravatar)