SQL Server Soft Delete with computed IsDeleted column in EF Core, without manually using SQL statement

92dk7w1h  于 12个月前  发布在  其他
关注(0)|答案(1)|浏览(93)

I have a model, which is containing a DeleteTime property. I want to implement a soft delete for it. I can simply use DeleteTime to check whether it's null or not. The problem is : I was wondering, if there is some way to have a computed IsDeleted field in the model, which is not mapped to any column in the database. but it gets evaluated through checking the value of DeleteTime . What I tried ,was defining such property in Model:

public bool IsDeleted { get { return DeleteTime != null; } }

and then at OnModelCreating of db context:

modelBuilder.Entity<MyModelName>().HasQueryFilter(c => !c.IsDeleted);

But it throws error while trying to translate it to SQL. I read solutions to similar problems. But I do not intend to create a new computed column directly in the database via SQL statements; As I said I don't want it to map to any existing column. Just wondering whether such thing is possible or not.

iyfamqjs

iyfamqjs1#

The query filters and any other Linq operations that translate expressions to SQL cannot include custom functions or properties since SQL cannot execute that code. So you either need to write your filters in a way that can be directly translated, like Svyatoslav's comment, or if you want to associate them to an entity or something like a base Entity class:

public abstract class SoftDeleteEntity
{
     public int Id { get; protected set; }
     public DateTime? DeleteTime { get; set; } = null;

     public static Expresssion<Func<TEntity, bool>> IsDeletedExpr => (e) => e.DeletedTime != null;
}

Assuming you have a base class for your entities that support soft deleting, otherwise this can be put in the entity(ies) directly.

Then when you want to apply the query filter or a Where clause:

modelBuilder.Entity<MyModelName>()
    .HasQueryFilter(SoftDeleteEntity.IsDeletedExpr);

And that should do the trick if you want the filter more encapsulated.

相关问题