Override behaviors in Entity Framework

Question

We have a data model that has some requirements. I would like to find a way to make these requirements be as transparent as possible while using EF.

First, the model must support soft-delete. I've seen some questions around this and I think it would be relatively straight forward.

Second, We have an "insert only" policy. That means no updates. When an update is made, a new entry must be inserted instead. I would like to be able to treat the operation as an update, and have the framework change it to an insert behind the scenes.

Third, Due to #2 when we query, we need to order by descending on the identity column and return the first record only. Even when doing a query that returns many records. Essentially, this creates a version history.

Fourth, we don't want to have to implement this logic in each query. It would be nice to have the framework do it for us so we can treat each query as if it were a normal CRUD type transaction.

Has anyone implemented such a data model in EF? What techniques did you use?

I'm aware that some of this can be done in Views and/or Sprocs, but if you use views then you have to maintain all relationships manually (EF can't read the relationships through the views). Triggers are also a possibility, but our DBA's want as few triggers as possible, and have a very extensive review policy on all triggers that takes a long time to accomplish. I'd rather not use a trigger if I don't have to.

I work primarily in a database first approach, but i use dbcontext.

EDIT:

Given Ladislav's comments below, I'd also be interested in any comments about other ORM's that may be able to handle these requirements.

Answer 1

1. This is possible with conditional mapping where additional column will be used to differ deleted records and custom SQL command / mapped stored procedure for every entity requiring soft delete to perform Update instead of Delete.
2. I doubt EF will handle this transparently. If you modify attached instance loaded from database it will perform update. You can again map stored procedure to perform insert instead of update but that change will not be reflected in your application logic. You will have to dispose context and reload data with a new context instance to see changes correctly. Better option is simply force your application somewhere to clone entity and insert the clone as a new one.
3. EF will not allow you adding such condition to autogenerated queries transparently unless you map it as a view or custom SQL query. Once you use view or SQL query you must also use SQL commands or stored procedures from insert, update and delete operations. Using custom SQL query has same disadvantages as using views.
4. If you don't use SQL query, you will have to write query yourself for example in form of custom reusable extension method and use it everywhere but be aware that eager or lazy loading will not reflect this. In case of eager and lazy loading you will always get all versions.

Stored procedure mapping requires EDMX. Custom SQL command and query mapping requires EDMX and without any other commercial tool you will also have to manually maintain EDMX.