Update , Insert into Entity Framework Using View?

Question

I have a mistake in my project when trying to give a Update or a Insert via the Entity Framework, where view database.

I read in English stackoverflow that I can’t perform update or Insert via view because there is no Primary key definite.

I also saw that you can change this, but I couldn’t find where. Does anyone know how to fix this error? Where I can configure the .edmx to accept Updates even don’t try a Primary key definite?

Or should I map a table?

Answer 1

I’m having an error in my project when trying to give an Update or an Insert via Entity Framework, where I’ve formatted a database view.

According to the official SQL Server documentation (see Updatable Views):

It is possible to modify the data of an underlying table through a display, provided that the following conditions are true:

• All modifications, including the UPDATE, INSERT and DELETE instructions, must reference columns from only one base table.

• The columns to be modified in the view should directly reference the underlying data of the table columns. Columns cannot be derived in any other way, as in the following:

  • An aggregation function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR and VARP.

  • A computation. The column cannot be computed from an expression that uses other columns. The columns formed with the use of the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT and INTERSECT result in a computation and are also not upgradable.

• Modified columns are not affected by GROUP BY, HAVING or DISTINCT clauses.

• TOP is not used anywhere in the display select_statement along with the WITH CHECK OPTION clause.

If your view does not follow these conditions, it will not work. And this does not depend on the Entity Framework.

I read in English stackoverflow that I can’t perform update or Insert via view because there is no Primary key defined. I also saw that you can change this, but I could not find where. Does anyone know how to fix this error? Where I can configure the .edmx to accept Updates even don’t try a Primary key definite?

This is not a mistake. It is an essential condition for the framework work. The error is all yours.

To work, you need to define at least one field as Primary key. Could be more than one, by the way.

The EDMX modeler is not the most suitable tool for this. You will have to decorate your Model generated by EDMX with primary key attributes. See here an example.