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
- 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)
- 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.