because it has a DefiningQuery and no element exists in the element

Question

I'm using EF in my application.

I try to save\insert a new record to a mapping table

and get the following error:

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

Should I define it in the edmx myself? How?

please read this post. https://stackoverflow.com/questions/7583770/unable-to-update-the-entityset-because-it-has-a-definingquery-and-no-updatefu — Emam, May 13 '19 at 05:33

score 109 · Accepted Answer · answered Mar 10 '13 at 15:13

109

My many-to-many mapping table was missing PK

added, and the issue is solved.

answered Mar 10 '13 at 15:13

Elad Benda

35,076
87
265
471

2

I have the same problem, but I am not sure which column to select as primary key, because in all articles it is said that mapping table has to be table with just two foreign keys, adding some columns just ruins this many-to-many relationship. I have for example two tables Projects and Clients and Project_Client table where are two columns with foreign keys one to ProjectId second on ClientId. So where i have to set primary key? – GeekyNuns Aug 02 '16 at 13:41
Yes, a bit delayed as a comment, but this answer and the following helped me out. https://stackoverflow.com/a/217952/2850898 – Esaith Mar 01 '19 at 08:50

score 50 · Answer 2 · edited Sep 07 '19 at 00:17

50

Agreed with the accepted answer. Just providing the reason behind it...

When EF mapping is done with a table which does not have a primary key, it is treated as a view. Since views are logical entities, they can't be updated.

So either add the missing primary key to your table or consider them as a view & don't perform any update operation on them.

edited Sep 07 '19 at 00:17

tster

17,883
5
53
72

answered Mar 26 '14 at 00:31

Biki

2,518
8
39
53

1

exactly , so if you want to insert or update such table(Audit table) use trigger instead – Snziv Gupta Nov 07 '17 at 22:40
thanks man! you define the actual reason for the problem. really appreciate for the description. – Talha Rafique Jul 16 '20 at 08:02

score 28 · Answer 3 · edited Dec 18 '15 at 21:40

28

If your view is updatable you can simply remove the 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 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:

Right click on the edmx file, select Open with, XML editor
Locate the entity in the edmx:StorageModels element
Remove the DefiningQuery entirely
Rename the store:Schema="dbo" to Schema="dbo" (otherwise, the code will generate an error saying the name is invalid)

edited Dec 18 '15 at 21:40

Mahesh

3,727
1
39
49

answered Jan 21 '15 at 07:19

kavitha Reddy

3,303
24
14

Also consider changing `edmx:StorageModels/Schema/EntityContainer@Name` and `edmx:Mappings/Mapping/EntityContainerMapping@StorageEntityContainer` attributes on actual database schema - this helped in my case. – stop-cran Oct 10 '17 at 10:03
Thank you, I would not have gotten this in the time i needed it done. Not all tables need to have primary keys. – gsirianni Jan 28 '18 at 07:11

score 12 · Answer 4 · answered Jul 11 '17 at 14:47

12

I came across this problem on an updatable view. Found this article solved my problem.

So the underlying table of my view does have primary key defined but EF does not know which columns are in the PK as the entity was built on the view. The trick here is to 'convince' EF that your view can be updated as a table. Steps are almost the same as mentioned by above answers:

Right click on the edmx file, select Open with, XML editor
Locate the entity in the edmx:StorageModels element
Remove the <DefiningQuery> section entirely
Rename the store:Schema="dbo" to Schema="dbo"
Change store:Type="Views" to store:Type="Tables"

answered Jul 11 '17 at 14:47

wctiger

921
12
22

Thanks for doing this! I wish someone could explain how to create the insert/update function though also... – JDPeckham Sep 29 '17 at 22:19
Can I give you a +50 for this, been ignoring this issue for years, sometimes my tables after an "Update From Database" get stuffed up so I revert the change, never had to figure it out until today, wish I knew this years ago... – Chris Schaller May 22 '18 at 05:47

score 4 · Answer 5 · edited Dec 24 '15 at 12:31

4

Right click on the edmx file, select Open with, XML editor
Locate the entity in the edmx:StorageModels element
Remove the DefiningQuery entirely
Rename the store:Schema="dbo" to Schema="dbo" (otherwise, the code will generate an error saying the name is invalid) These steps worked for me

edited Dec 24 '15 at 12:31

Jamie Rees

7,973
2
45
83

answered Dec 24 '15 at 12:27

user3452580

37
2

score 4 · Answer 6 · answered Feb 02 '18 at 11:02

4

My table didn't have primary key in sql table design. Added it and solved.

answered Feb 02 '18 at 11:02

Rajdeep

788
7
26

Duncan Kuffar · Answer 7 · 2017-01-07T21:05:31.790

2

Make sure your foreign key tables do not have more than one column names that match the columns in your referring table; only primary key should match between your ref table with refereed table. 0..1 should have distinguished column name, develop naming convention for your columns that will ensure referring and refereed tables do not have more than one matching columns names. Courtesy of ALMwConsult.net

edited Jan 07 '17 at 21:05

answered Jan 07 '17 at 18:24

Duncan Kuffar

31
3

score 2 · Answer 8 · answered May 15 '17 at 15:49

2

**Recheck your tables doesn't have PK :** just add Primary Key and then remove and re-add table to edmx then it should work

answered May 15 '17 at 15:49

Moji

5,720
2
38
39

score 1 · Answer 9 · answered May 21 '17 at 14:52

1

If you are not handling primary key in your table then this issue will definitely occur in MVC while updating and inserting record database

DbUpdate Exception was unhandled by user

answered May 21 '17 at 14:52

bajran

1,433
14
23

score 0 · Answer 10 · answered Oct 12 '17 at 14:43

0

Working on db first mvc application. The problem was i forget to define primary key for the table. Solved by adding primary key using alter command and updating the DAL(edmx)

answered Oct 12 '17 at 14:43

Hafte Nigus

33
6

What is meant by updating the DAL(edmx). How we should do it – Asis Feb 06 '20 at 06:30

because it has a DefiningQuery and no element exists in the element

10 Answers10

Linked