Unable to update the EntitySet '' because it has a DefiningQuery

Question

I'm having this error in this code:

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

The code.

Ingredient ing = new Ingredient();
ing.name = ingredientVM.name;
ing.Ingredient_Type_id = ingredientVM.typeId;
ing.UOM_id = ingredientVM.uomId;
ing.is_deleted = 0;

db.Ingredients.Add(ing);
db.SaveChanges();

int latestIngredientId = ing.id;

Ingredient_Quantity iq = new Ingredient_Quantity
{
   Ingredient_id = latestIngredientId,
   quantity_as_of = DateTime.Now,
   quantity = (double)ingredientVM.quantity
};

db.Ingredient_Quantity.Add(iq);
db.SaveChanges(); // HERE IS WHERE I'M GETTING THE ERROR

From what I am seeing in the internet, it's because that my Ingredient_Quantity is seeing every column as Entity Key. I don't know if this is right.

How can I change this? Any advice?

I think you need to set one column as primary key in `Ingredient_Quantity` database table. Try adding primary key to `Ingredient_id`, remove the entity & re-add it. Is `Ingredient_Quantity` a view instead? — Tetsuya Yamamoto, Oct 03 '17 at 04:03
You mean like [this](https://imgur.com/a/anpGA)? What do you mean by "is 'Ingredient_Quantity' a view" and what's entity mapping? I'm sorry, I'm really new to this. — AyakoS, Oct 03 '17 at 04:12
Yes, one primary key like that. Is that setup working now? I just want to ask if `Ingredient_Quantity` is a database table or a database view, they're having different treatment when used in EF entity mapping. — Tetsuya Yamamoto, Oct 03 '17 at 04:14
I was having an error when I tried to make `Ingredient_id` a primary key, something with Multiplicity or something. So I tried having `quantity_as_of` a primary key too. It still doesn't work though. `Ingredient_Quantity` is a database table — AyakoS, Oct 03 '17 at 04:19
So you want to say that `Ingredient_Quantity` has foreign key in `Ingredient`? I think you have error like `Multiplicity conflicts with the referential constraint`, if this occurs try setting the foreign key as nullable type & the relationship as 0/1..n (zero/one to many). — Tetsuya Yamamoto, Oct 03 '17 at 04:24

score 3 · Answer 1 · answered Oct 03 '17 at 04:50

You have 2 issues there:

1) The most common reason from "Unable to update the EntitySet X because it has a DefiningQuery" is that entity table missing a primary key constraint in database. You can add primary key to identity column either by SSMS table designer or using this query (note that primary key should be only set to one column):

ALTER TABLE Ingredient_Quantity ADD CONSTRAINT (Ingredient_id) PRIMARY KEY (Ingredient_id)

Note that a table without primary key treated as view during entity mapping (EF uses class to define table/view name & properties to refer each table/view columns), and a view can't be inserted/updated (only SELECT allowed).

2) Multiplicity conflicts with the referential constraint usually refers to conflict between source table column definition & target table foreign key. You can fix the multiplicity settings using these steps:

a) Define the relationship first. The relationship criteria should be like this:

One-to-many relationship if you have non-nullable foreign key (e.g. int)

Zero/one-to-many relationship if you have nullable foreign key (e.g. int?)

b) Open EDMX file in designer, right-click empty design area, select "Add New" => "Association".

c) Choose "Multiplicity" setting which best fit with your foreign key configuration (see the image below as example). You can select 1 (One) or 0..1 (Zero or One), depending on non-null/nullable foreign key on second table.

Related issues:

It has a DefiningQuery but no InsertFunction element... err

Problems with multiplicity and EF 6

Entity Framework Database Update First Multiplicity conflicts

Unable to update the EntitySet '' because it has a DefiningQuery

1 Answers1