How to add a custom code to EFCore that executes on server

Problem: I'm using EFCore 3.1 ( a project recently migrated from an older version) where I have to retrieve records based on if they are soft deleted or not. Any soft deleted record is not considered for any operation performed on the table.

Soft deleted is implemented in a form of IsDeleted table column which is a bit. 1 = soft deleted, 0 = row is available. In the C#, there is an interface IActiveEntity with a bool property of IsDeleted

interface IActiveEntity
{ 
    bool IsDeleted { get; set; }
}

Certain operation implemented in generic form and therefore they check if the entity is of type IActiveEntity. I read a series of articles. https://www.thinktecture.com/en/entity-framework-core/custom-functions-using-hasdbfunction-in-2-1/

But it's not working quite the way as mentioned in article. There is also not enough documentation available.

I implemented the function extensions for EF.Functions:

public static class FunctionsExtension
    {
       public static MethodInfo GetIsDeletedMethodInfo()
       {
          var  methods = typeof(FunctionsExtension)
             .GetMethods()
             .Where(x=> x.Name == "IsDeleted" && !x.GetParameters().Any())
             .ToList();
          
          return methods.FirstOrDefault();
       }
       
        public static bool IsDeleted(this DbFunctions sys, object entity)
        {
           throw new InvalidOperationException("This method is for use with Entity Framework Core only and has no in-memory implementation.");
        }
       
        public static bool IsDeleted()
        {
           throw new InvalidOperationException("This method is for use with Entity Framework Core only and has no in-memory implementation.");
        }
    }

Then I created corresponding class to translate the expression into correct sql code.

    public class IsDeletedExpression : SqlExpression
    {
       public IsDeletedExpression() : base(typeof(object), new BoolTypeMapping("MSSQLSERVER", DbType.Boolean))
       {
       }

      protected override Expression Accept(ExpressionVisitor visitor)
      {
         if(visitor is IQuerySqlGeneratorFactory fac)
         {
            var gen = fac.Create();
            gen.Visit(new SqlFragmentExpression("([DeletedOn] IS NULL)"));

            return this;
         }

         return base.Accept(visitor);
      }
      public override bool CanReduce => false;
      public override void Print(ExpressionPrinter printer) => printer.Print(this);
      protected override Expression VisitChildren(ExpressionVisitor visitor)=> base.VisitChildren(visitor);
      
    }

Then the method call translator for the IsDeleted expression:

    public class IsDeletedTranslator : IMethodCallTranslator
    {
        public SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList<SqlExpression> arguments)
        {
         if(method.Name == "IsDeleted")
            return new IsDeletedExpression();

         return null;
        }
    }

A class to register the Method call translator

    public sealed class IsDeletedMethodCallTranslatorPlugin : IMethodCallTranslatorPlugin
    {
       public IEnumerable<IMethodCallTranslator> Translators =>
          new List<IMethodCallTranslator> {new IsDeletedTranslator()};

    }

After this these two classes to add to DBContext

public sealed class MyContextOptionsExtension : IDbContextOptionsExtension
    {
       public void ApplyServices(IServiceCollection services) => services.AddSingleton<IMethodCallTranslatorPlugin, IsDeletedMethodCallTranslatorPlugin>();
       public void Validate(IDbContextOptions options){}
       public DbContextOptionsExtensionInfo Info => new MyContextExtensionInfo(this);
    }

    public sealed class MyContextExtensionInfo : DbContextOptionsExtensionInfo
    {
        public MyContextExtensionInfo(IDbContextOptionsExtension extension) : base(extension){}
        public override long GetServiceProviderHashCode() => 0;
        public override bool IsDatabaseProvider => true;
        public override string LogFragment => "([DeletedOn] IS NULL)";       
        public override void PopulateDebugInfo(IDictionary<string, string> debugInfo){}
    }

And this is how add it to DbContext

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder
        .HasDbFunction(FunctionsExtension.GetIsDeletedMethodInfo())
        .HasTranslation((list) =>  new IsDeletedExpression());
    }


        public static MyContext GetInstance(string cnn)
        {
            var optionsBuilder = new DbContextOptionsBuilder<MyContext>();
            optionsBuilder.UseSqlServer(cnn, cb =>
            {
            });

            var extension = optionsBuilder.Options.FindExtension<MyContextOptionsExtension>() ?? new MyContextOptionsExtension();
            optionsBuilder.Options.WithExtension(extension);
            var context = new MyContext(optionsBuilder.Options);

            return context;
        }

When I run the code it calls the Extension method IsDeleted and throws exception instead of performing any sort of translation. What am I missing so that EF Core start recognizing the call to be translated into SQL and not execute the local function.

Update: This is how I use the code:

  var maxAge = employees.Where(x => !EF.Functions.IsDeleted(x)).Max(x=> x.Age)

this is the error I get:

The LINQ expression

    .Where(n => __Functions_0
        .IsDeleted(n))

could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.