Database – Is It Bad Practice to Insert and Update Through Database Views?

database-designentity-frameworkview

Currently we are slowly replacing a custom made ORM framework with Entity Framework (EF). One of the steps is to make sure we can update and insert records through database views with EF, just like we are doing with the current ORM. The views provide security by filtering on accessible records for the user, preventing access and the posibility to update other records.

I fail to get a solid grip on how to properly implement this approach with EF and started to ask myself whether it is good practise at all to update and insert (and sometimes delete) through these views. When I google I can't find any good/bad practise recommendations concerning this subject, only the fact that sometimes other use this approach too. Maybe my search skills aren't sophisticated enough.

To me it seems views are meant to be read-only and help the user to select data quickly from potentially multiple tables. Performing an insert or update on a views feels bad for that reason, despite that it is possible.

What are the advantages and disadvantages for using views to insert and update records?

Also are there specific problems when implenting Entity Framework?

What I found so far.
Advantages:

  • Security by filtering accessible records on database level
  • Bulk insert works for views too

Disadvantages:

  • Select statement is more or less locked when you need to update or insert records
  • Specific for EF, when using Migrations views suddenly need to be treated as a view instead of a table. (correct me if I'm wrong)

Some info:

  • We use the EF 6.13
  • It is a MSSQL database
  • There are both read-only views and CRUD views
  • Some views have terrible performance, mainly because we cannot alter them because we need to be able to execute CRUD operations on them.
  • We have about 30 views

I hope this is the right place to ask this question.

Best Answer

You can probably find everything you need to know about this subject in this Microsoft article.

Specifically:

When querying through a view, the Database Engine checks to make sure that all the database objects referenced anywhere in the statement exist, that they are valid in the context of the statement, and that data modification statements do not violate any data integrity rules. A check that fails returns an error message. A successful check translates the action into an action against the underlying table or tables.

In other words, any data modification operations you undertake on a view would still have to comply with any constraints that you have already imposed on the underlying database schema. In addition, you benefit from any permission regulations that the view itself provides. EF treats views essentially like any other table, so I see a lot of upside, and little downside.

Naturally, if you encounter problems with this approach, such as your poorly-performing views, you can evaluate whether or not you should switch back to direct tables in those cases or create better views for editing purposes.

Related Topic