Entity Framework Logical Delete Part Seven: Filtering deleted records using Row-Level Security

So in the previous post, I gave some solutions that can be used for filtering the soft deleted entities. The EF Core solution is kinda neat, but the others' have at least one problem: you have to include the IsDeleted column in the model, which leads to abstraction leaking. How cool would it be if we could keep that column in the database? Here's a solution to do just that (if don't use EF Core yet, then read on; if you already do, this might be interesting to you as well).

Of course, if we do that, we have to do the filtering on the database side as well. We do not have an SP-based option like we had for the CUD operations, so we have to come up with something else.

That something else is a relatively new feature of SQL Server, called Row-Level Security. Here's the gist of it: you define a function. When a query wants to access an entity, this function is run for every record for the result set of query, acting as a filter. This query can be anything (usually used in multi-tenant application for filtering for a tenant id or filtering for a role), like our IsDeleted flag.

Implementing Row-Level Security for soft delete

So, you need to do two things. First, you have to create a function, like this:

CREATE FUNCTION PersonActivePredicate(@IsDeleted AS bit)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS accessResult
WHERE @IsDeleted=0;

Then, you have to create the security policy:

CREATE SECURITY POLICY PersonActiveFilter
ADD FILTER PREDICATE PersonActivePredicate(IsDeleted)
ON People
WITH(STATE = ON);

This will pipe every Person record into the PersonActivePredicateFilter function, passing the value of the IsDeleted record into the @IsDeleted parameter. The function will only let through the records where the IsDeleted is 0.

And that's it basically. You can go ahead and query the People table, and just watch how deleted records are automatically filtered out. This also works for loading related entities, the queries remain nice and simple. And the best part: there's no need to add the IsDeleted column to the model. The only thing that's missing is proper automation. But all we have to do is amend the custom SQL generator written in a previous post to generate the function and the policy for every table that has the SoftDelete annotation:

protected override void Generate(CreateTableOperation createTableOperation)
{
  if (createTableOperation.Annotations.TryGetValue("SoftDelete", out object hasSoftDeleteObject))
  {
    if (bool.TryParse(hasSoftDeleteObject.ToString(), out bool hasSoftDeleteResult) && hasSoftDeleteResult)
    {
       createTableOperation.Columns.Add( 
                 new ColumnModel(PrimitiveTypeKind.Boolean)  {                                                                       
                                      Name = "IsDeleted",
                                      IsNullable = false,
                                      DefaultValueSql ="0" });
       base.Generate(createTableOperation);
       using (var writer = Writer())
       {
         writer.WriteLine( 
        $"CREATE FUNCTION 
         {createTableOperation.Name.ToLower()}Predicate(@IsDeleted AS bit)" + 
        "RETURNS TABLE" + Environment.NewLine +
        "WITH SCHEMABINDING" + Environment.NewLine +
        "AS" + Environment.NewLine +
        "RETURN SELECT 1 AS accessResult" + Environment.NewLine +
        "WHERE @IsDeleted=0;" + Environment.NewLine
      );
    
        writer.WriteLine(
         $"CREATE SECURITY POLICY {createTableOperation.Name}Filter" + Environment.NewLine +
        $"ADD FILTER PREDICATE {createTableOperation.Name.ToLower()}Predicate(IsDeleted)" + Environment.NewLine +
        $"ON {createTableOperation.Name}" + Environment.NewLine +
        "WITH(STATE = ON);" + Environment.NewLine
      );
      Statement(writer);
    }
  }
}
else
{
   base.Generate(createTableOperation);
}

This little code snippet automatically generates the filtering function and the policy if the annotation is present (and also adds the IsDeleted column to the table, like it did before).

And that's the end of my blog series about soft delete with EF. I hope you liked it and found it useful. Like always, I created a Github repository, where you will find the SQL generator, the extension method to enable soft deletion for the entity and another
extension method to help you enable the SQL generator easier in the configuration. I also included a little sample project to help you experiment.