Note: I use the preview version of Entity Framework Core 2.0 (2.0.0-preview2-final). Things may change after the final version is released
I continue my exploration of Entity Framework Core 2.0. Today, I'll look at how to implement soft delete (also called logical delete) in a transparent way, that is, as if you were performing real deletes.
To implement soft delete, you need to add a column indicating whether a row is logically deleted. You can use a boolean column, or a date column if you also want to record when the row was deleted. The second part is to update all queries to filter on that column, and to replace delete statements with update statements.
Let's see how to do both with Entity Framework Core!
#Add the IsDeleted column
Entity Framework Core provides very flexible mapping. In the previous post about tracking columns, I cover the three ways to map a column. Since soft delete should be transparent, I chose not to expose IsDeleted in the entity classes. The model is the following:
C#
public class Blog
{
public int BlogId { get; set; }
public string Url { get; set; }
public List<Post> Posts { get; set; }
}
public class Post
{
public int PostId { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public int BlogId { get; set; }
public Blog Blog { get; set; }
}
Next, we tell Entity Framework Core about this additional column:
C#
public class BloggingContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
public DbSet<Post> Posts { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Post>()
.Property<bool>("IsDeleted");
}
}
#Change the insert/delete queries
Entity Framework Core tracks all pending changes via the ChangeTracker. You can modify its entries just before EF generates and executes queries against the database.
C#
// Using EF Core 6+, you can now use a SaveChangesInterceptor instead of overriding SaveChanges
// You need to register it using AddInterceptors
// https://learn.microsoft.com/en-us/ef/core/logging-events-diagnostics/interceptors?WT.mc_id=DT-MVP-5003978
public class BloggingContext : DbContext
{
public override int SaveChanges(bool acceptAllChangesOnSuccess)
{
OnBeforeSaving();
return base.SaveChanges(acceptAllChangesOnSuccess);
}
public override Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default(CancellationToken))
{
OnBeforeSaving();
return base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken);
}
private void OnBeforeSaving()
{
foreach (var entry in ChangeTracker.Entries<Post>())
{
switch (entry.State)
{
case EntityState.Added:
entry.CurrentValues["IsDeleted"] = false;
break;
case EntityState.Deleted:
entry.State = EntityState.Modified;
entry.CurrentValues["IsDeleted"] = true;
break;
}
}
}
}
Now the generated query for the following code:
C#
using (var context = new BloggingContext())
{
var post = new Post { Blog = blog };
context.Posts.Add(post);
context.SaveChanges();
}
SQL
exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Posts] ([BlogId], [Content], [IsDeleted], [Title])
VALUES (@p1, @p2, @p3, @p4);
SELECT [PostId]
FROM [Posts]
WHERE @@ROWCOUNT = 1 AND [PostId] = scope_identity();
-- @p3 is 0 (false)
',N'@p1 int,@p2 nvarchar(4000),@p3 bit,@p4 nvarchar(4000)',@p1=1,@p2=NULL,@p3=0,@p4=NULL
C#
context.Posts.Remove(post);
context.SaveChanges();
SQL
exec sp_executesql N'SET NOCOUNT ON;
UPDATE [Posts] SET [BlogId] = @p0, [Content] = @p1, [IsDeleted] = @p2, [Title] = @p3
WHERE [PostId] = @p4;
SELECT @@ROWCOUNT;
',N'@p4 int,@p0 int,@p1 nvarchar(4000),@p2 bit,@p3 nvarchar(4000)',@p4=1,@p0=1,@p1=NULL,@p2=1,@p3=NULL
Insert and delete operations are now handled. The next step is to filter the select queries.
#Change the select queries
Entity Framework Core 2.0 introduced a new concept: Query Filters. A query filter is a WHERE clause that is automatically appended to every generated query. You declare it once during model creation, and EF Core applies it implicitly to all queries for that entity.
C#
public class BloggingContext : DbContext
{
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Post>()
.Property<bool>("IsDeleted");
modelBuilder.Entity<Post>()
.HasQueryFilter(post => EF.Property<bool>(post, "IsDeleted") == false);
}
}
Let's see the query filter in action:
C#
var posts = context.Posts.ToList();
SQL
SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[IsDeleted], [p].[Title]
FROM [Posts] AS [p]
WHERE [p].[IsDeleted] = 0 -- Query filter
The query filters are also applied when including related entities:
C#
var blogs = context.Blogs.Include(_ => _.Posts);
SQL
SELECT [_].[BlogId], [_].[Url]
FROM [Blogs] AS [_]
ORDER BY [_].[BlogId]
SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[IsDeleted], [p].[Title]
FROM [Posts] AS [p]
INNER JOIN (
SELECT [_0].[BlogId]
FROM [Blogs] AS [_0]
) AS [t] ON [p].[BlogId] = [t].[BlogId]
WHERE [p].[IsDeleted] = 0 -- Query filter
ORDER BY [t].[BlogId]
Query filters make soft-delete implementation straightforward.
#Querying soft-deleted rows
To restore soft-deleted rows, you first need to query them by bypassing the query filter. EF Core provides the IgnoreQueryFilters method to disable query filters for a specific query.
C#
var deletedPosts = context.Posts.IgnoreQueryFilters()
.Where(post => EF.Property<bool>(post, "IsDeleted") == true);
Restoring deleted posts is slightly verbose. You query them with filters disabled and then update IsDeleted in the change tracker.
C#
var deletedPosts = context.Posts.IgnoreQueryFilters().Where(post => EF.Property<bool>(post, "IsDeleted") == true);
foreach (var deletedPost in deletedPosts)
{
var postEntry = context.ChangeTracker.Entries<Post>().First(entry => entry.Entity == deletedPost);
postEntry.Property("IsDeleted").CurrentValue = false;
}
context.SaveChanges();
#Automatically apply soft-delete to all entities of the model
To apply soft delete to all entities in the model, iterate over all entity types and add both the IsDeleted property and the query filter. The tricky part is building the lambda expression dynamically, since the entity type is not known at compile time. You need to construct the expression tree manually.
C#
public class BloggingContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
public DbSet<Post> Posts { get; set; }
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
foreach (var entityType in builder.Model.GetEntityTypes())
{
// 1. Add the IsDeleted property
entityType.GetOrAddProperty("IsDeleted", typeof(bool));
// 2. Create the query filter
var parameter = Expression.Parameter(entityType.ClrType);
// EF.Property<bool>(post, "IsDeleted")
var propertyMethodInfo = typeof(EF).GetMethod("Property").MakeGenericMethod(typeof(bool));
var isDeletedProperty = Expression.Call(propertyMethodInfo, parameter, Expression.Constant("IsDeleted"));
// EF.Property<bool>(post, "IsDeleted") == false
BinaryExpression compareExpression = Expression.MakeBinary(ExpressionType.Equal, isDeletedProperty, Expression.Constant(false));
// post => EF.Property<bool>(post, "IsDeleted") == false
var lambda = Expression.Lambda(compareExpression, parameter);
builder.Entity(entityType.ClrType).HasQueryFilter(lambda);
}
}
public override int SaveChanges(bool acceptAllChangesOnSuccess)
{
OnBeforeSaving();
return base.SaveChanges(acceptAllChangesOnSuccess);
}
public override Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default(CancellationToken))
{
OnBeforeSaving();
return base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken);
}
private void OnBeforeSaving()
{
foreach (var entry in ChangeTracker.Entries())
{
switch (entry.State)
{
case EntityState.Added:
entry.CurrentValues["IsDeleted"] = false;
break;
case EntityState.Deleted:
entry.State = EntityState.Modified;
entry.CurrentValues["IsDeleted"] = true;
break;
}
}
}
}
#Conclusion
Implementing the soft-delete pattern with Entity Framework Core 2.0 is straightforward and fully transparent. You can add soft delete to an existing model without modifying any LINQ queries.
Do you have a question or a suggestion about this post? Contact me!
