Entity Framework Logical Delete Part Three: Using stored procedures

In Part 1 of this series I discussed what logical delete is, in Part 2 I gave a solution that is kinda good for cases when you don't have cascading requirements. But in that post I pointed out the solutions inadequacy to handle the cascading situation. Here in Part 3, I will discuss how to handle the cascading situation elegantly, with no performance problems or abstraction leaking and finally attempt to automatize the whole solution.

Stored procedures to the rescue

Using stored procedures for data access is so 1998. Over the years, ORMs became the dominant way of accessing data in a data-driven object-oriented application. But stored procedures can still be useful, you just have to find the right way to integrate them into this modern, more efficient approach.

EF does just that. You have the opportunity to create your own CUD stored procedures and you can instruct EF to invoke those stored procedures for these operations instead of generating the insert-update-delete statements.

Using the Person class as an example from the last posts, if you remove an entity and call .SaveChanges(), the query that is generated looks like this:

exec sp_executesql N'DELETE [dbo].[People]
WHERE ([PersonId] = @0)',N'@0 int',@0=1

But you can map the CUD operations with a simple method call for an entity like this:

public class MyCtx : DbContext
{

  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Person>().MapToStoredProcedures();   
  }
}

Using this simple mapping, EF will generate a stored procedure for every CUD operation. The delete procedure looks like this:

CREATE PROCEDURE [dbo].[Person_Delete]
    @PersonId [int]
AS
BEGIN
    DELETE [dbo].[People]
    WHERE ([PersonId] = @PersonId)
END

And now, if you remove an entity and call .SaveChanges(), EF uses this stored procedure to do the actual delete:

exec [dbo].[Person_Delete] @PersonId=1

And with that, an idea forms: all you have to do is modify the stored procedure to do the logical delete instead of the delete operation:

CREATE PROCEDURE [dbo].[Person_Delete]
    @PersonId [int]
AS
BEGIN
    UPDATE [dbo].[People]
    SET IsDeleted = 1
    WHERE ([PersonId] = @PersonId)
END

And now instead of a delete, an update is performed on the entity. How awesome is that?

Handling cascade delete

And with that in mind, cascade delete is also easy to handle, you just have to add the deletion to the stored procedure. For example, if you have a Cars table and you want to delete the person's car, you can do this (of course, you should add some transaction handling, even if EF also wraps the .SaveChanges() into a transaction call):

CREATE PROCEDURE [dbo].[Person_Delete]
    @PersonId [int]
AS
BEGIN
    UPDATE [dbo].[People]
    SET IsDeleted = 1
    WHERE ([PersonId] = @PersonId)
    UPDATE [dbo].[Cars]
    SET IsDeleted = 1 
    WHERE ([PersonId] = @PersonId)
END

Abstraction leaking and performance

Here's a bonus: you don't even need the IsDeleted property in the entity itself. So there's no abstraction leaking during business modelling: no extra interfaces, no extra properties. Check :) And as for the performance, it is easy to see, that there's not much we could optimize further: it is not needed to load the related entities into memory for setting the flag, there are no extra operations, just the updates.

Automating the solution

If you want to automate the solution, here's what you have to do:

1. Map the CUD operations using the .MapToStoredProcedures() method.
2. Add an IsDeleted column to every entity table where the represented entity has the custom annotation.
3. Modify the stored procedure to do the update.

The process is very well-defined and completely independent from the entity itself. This gives a good opportunity to create an automated solution. But I will discuss it in a next blog post :) Stay tuned!