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 explore how to use soft-delete (or logical delete) easily. I mean implementing soft-delete in a transparency way, e.g. as if you were deleting rows.
To implement soft delete, you need to add a column to indicate if the row is logically deleted. You can use a boolean column, or a date column if you want to know when the row was deleted. The second part is to change all queries to use this column to filter the result set. You also need to replace delete statements with update statements.
Let's see how to do these 2 things with Entity Framework Core!
#Add the IsDeleted column
Entity Framework Core provides a very flexible mapping. In the previous post about tracking columns, you'll find the 3 ways to map a column. In the introduction, I said soft delete should be transparent. So I decide to not expose the IsDeleted in the 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; }
}
Now, we need to indicate Entity Framework Core that there is an 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 stores all the changes using the ChangeTracker. You can modify the entries of the ChangeTracker just before EF generates the queries and executes them on 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
The insert and delete requests are handled. You now have to change all the select queries.
#Change the select queries
Entity Framework Core 2.0 has introduced a new concept: Query Filters. A query filter is a WHERE clause that is always added to the generated queries. This means, you can declare the filter for an entity once during the model creation, and then, this filter is implicitly added to the generated queries that use the table.
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 used for including relations:
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 so easy 😃
#Querying soft-deleted rows
If you want to restore deleted rows, you must be able to select them. This means you need to remove the query filters temporarily. EF has added a new method IgnoreQueryFilters to indicate you don't want query filters to be used for the current query.
C#
var deletedPosts = context.Posts.IgnoreQueryFilters()
.Where(post => EF.Property<bool>(post, "IsDeleted") == true);
Restoring deleted posts is a little verbose. You need to query them and update the IsDeleted property 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 the soft-delete to all entities of the model, you can iterate over all entity types and add the property and the query filter. The tricky part of the code is the creation of the lambda expression. Indeed, you cannot use the classic syntax because you don't know the type of entities at compile type. So, you have to create it by hand and it requires a few more lines.
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 very easy and can be transparent. You can add soft-delete to an existing model without changing your LINQ code.
Do you have a question or a suggestion about this post? Contact me!
