Question 277459 in ASP.NET Question posted in
The official documentation can be found here.

Asp.net – Soft Delete with computed IsDeleted column in EF Core, without manually using SQL statement

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.

Tags:

2

Answers


  1. 0

    Yes, it’s possible to have a computed property in your model that isn’t mapped to the database. However, there are a few things to consider:

    1. Ignore the Computed Property: First, make sure EF Core knows not to map your computed property to a database column.

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

    2. Use Direct Field in Query Filter: EF Core sometimes struggles with translating computed properties into SQL. Instead, base your query filter directly on the DeleteTime field, as this is directly mappable to a database column.

      modelBuilder.Entity<MyModelName>().HasQueryFilter(c => c.DeleteTime == null);

    The combination of these two steps will allow you to have a computed property for your application’s logic and also a soft delete filter that works at the database level.

    Remember, the IsDeleted property is great for in-app logic where you’re working directly with the entity objects, but when constructing LINQ queries that get translated to SQL, it’s usually best to work directly with the database-mapped properties.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search