Implementing “Triggers” and Soft Delete in FreeSQL

Introduction

Recently I started a new project, tech stack AspNetCore + FreeSQL

This ORM is really great to use, and the documentation is quite complete too. Here I’ll record two feature implementations related to “triggers”

Recording update time when modifying an entity

Model code

All of my models are derived from this ModelBase, which comes with three property fields

public abstract class ModelBase : ISoftDelete {
    public DateTime CreatedTime { get; set; } = DateTime.Now;
    public DateTime ModifiedTime { get; set; } = DateTime.Now;
    public bool IsDeleted { get; set; } = false;
}

Now I need to automatically update the ModifiedTime field whenever each entity is modified

You can use the “audit property values” feature in the AOP provided by FreeSQL

According to the official introduction: this feature can uniformly handle certain values on insert/update, such as a snowflake-algorithm value for a property, a created-time value, or even a business value.

Configure it when creating the FreeSQL instance

Check the entity type; if it is a ModelBase type, then set the ModifiedTime field to the current modification time

// 实体变化事件监听
freeSql.Aop.AuditValue += (sender, args) => {
    if (args.Object is ModelBase modelBase) {
        modelBase.ModifiedTime = DateTime.Now;
    }
};

Besides that, there’s another feature for monitoring entity modifications. I used this one at first, but found that it only executes after the entity has already been modified and saved, so it can’t implement the feature of recording the update time. Still, it’s quite useful for auditing, so I’ll note it down~

fsql.Aop.CurdAfter += (s, e) => {
    Console.WriteLine($"ManagedThreadId:{Thread.CurrentThread.ManagedThreadId};"+
    $" FullName:{e.EntityType.FullName} ElapsedMilliseconds:{e.ElapsedMilliseconds}ms, {e.Sql}");
};

Implementing soft delete

Current designs generally use soft delete to improve fault tolerance, so that data can still be recovered after accidental deletion.

The DjangoStarter framework I previously wrapped implemented soft delete itself (by overriding DjangoORM’s objects manager)

Coming to FreeSQL, according to the official documentation, you can implement soft delete directly using the filter feature.

First define an interface

public interface ISoftDelete {
    bool IsDeleted { get; set; }
}

Actually, using the ModelBase above would also work

But I saw the official docs used it this way, so I followed along~ (interfaces are more generic; some entities may not need created-time and modified-time fields)

Then configure it when creating the FreeSQL instance

freeSql.GlobalFilter.Apply<ISoftDelete>("IsDeleted", a => a.IsDeleted == false);

This way, when reading data, items whose IsDeleted field is true are automatically filtered out

When deleting, don’t execute repo.Delete directly; instead set the IsDeleted field to true

Of course, you can also intercept all delete operations the same way as the AOP above.

References

• FreeSQL Docs - AOP - https://freesql.net/guide/aop.html
• FreeSQL Docs - Filters - https://freesql.net/guide/filters.html