Linq-to-SQL How to prevent the use of Delete methods?

Question

By convention our DB only alows the use of stored procedures for INSERT, UPDATE and DELETE. For some tables / types there is no DELETE stored procedure, because it is not allowed to delete rows. (You can only update the status of such a type to "deleted"). e.g. a customer may be marked as deleted but is never really removed from the DB.

How do I prevent the use of Delete() for certain types in the Data Access Layer = in the DMBL?

The "Default Methods" for Insert and Update are mapped to the corresponding stored procedure. But for Delete it says "use runtime". I would like to set it to "not allowed".

Is there a way to achieve this on the DB model layer?

Many thanks

Answer 1

Implement a partial class for each such entity and implement the OnValidate partial method. It takes a ChangeAction as a parameter. When the ChangeAction is ChangeAction.Delete, throw an exception that indicates that the operation is disallowed (IllegalOperationException, maybe).

Answer 2

The deletion API is not generated, by which I mean an option on a generated table cannot remove the ability to mark an item as deleted. DeleteOnSubmit is part of the Table<TEntity> class.

If it is always an error to delete an entity, OnValidate should throw an InvalidOperationException as tvanfosson suggests.

I tried mapping Linq to Sql deletes to stored procedures which simply set the field to true. It gets weird because the DataContext removes instances after "deleting", but they are legal domain entities and should still be in the DataContext after submitting.

Answer 3

I had the same constraint while developing one of my application. You can always condifure the delete action to use a certain stored procedure instead of the framework generating a sql command for the same. In my case when I say delete we just wanted to mark a particular row as deleted but not physically deleting it. So our update stored procedure was reused in the delete command to simply mark the isDeleted col value as true. Besides that you may want to build some kind of wrapper around the classes built by DBML and suppress the delete methods. Right now I do not see any speciall setting to have the framework only generate create and update methods. Partial classes may be one more alternative.

Answer 4

How about to configure rights for the user you're using to connect to DB? You can set Deny for Delete operation for this particular user, so it wouldn't be possible to use DELETE statement on DB level.