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

Question

By convention, our database only uses stored procedures for INSERT, UPDATE, and DELETE. For some tables / types, there is no DELETE stored procedure, since it is not allowed to delete rows. (You can update this type of status to "deleted"). for example, a client may be marked as remote, but it will never be deleted from the database.

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

“The default methods for inserting and updating 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 at the database model level?

Thank you very much

Answer 1

Implement a partial class for each such object and implement the partial OnValidate method. As a parameter, it takes the value ChangeAction. When ChangeAction is ChangeAction.Delete, throws an exception indicating that the operation is prohibited (possibly an IllegalOperationException).

Answer 2

I had the same restriction when developing one of my applications. You can always formulate the delete action to use a specific stored procedure instead of the structure generating the sql command for it. In my case, when I say delete, we just wanted to mark a specific line as deleted, but not physically deleting it. Therefore, our updated stored procedure was reused in the delete command to simply mark the value of isDeleted col as true. In addition, you can create some kind of wrapper around the classes created by DBML and suppress delete methods. Right now I do not see any special settings so that the infrastructure only creates creation and update methods. Partial classes may be another alternative.

Answer 3

How to configure the rights for the user that you use to connect to the database? You can set the Deny for Delete operation for this particular user, so it would be impossible to use the DELETE statement at the DB level.

Answer 4

The delete API is not generated, so I mean that the option in the generated table cannot remove the ability to mark the item as deleted. DeleteOnSubmit is part of the Table<TEntity> class.

If you always delete an entity, OnValidate should raise an InvalidOperationException , as tvanfosson suggests.

I tried matching Linq to Sql deletes stored procedures that simply set the field to true. This becomes strange because the DataContext deletes the instances after the “deletion”, but they are legal entities of the domain and should still be in the DataContext after the submission.