Adding a custom function for server side execution using SqlFragment

[SuheylZ]

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 =>
            {
 if (cb is IRelationalDbContextOptionsBuilderInfrastructure infrastructure)
               {
                  var etx = infrastructure.OptionsBuilder.Options
                                     .FindExtension<MyContextOptionsExtension>()
                                  ?? new MyContextOptionsExtension();

                  if (infrastructure.OptionsBuilder is IDbContextOptionsBuilderInfrastructure dbctxOB)
                  {
                     dbctxOB.AddOrUpdateExtension(etx);
                  }
               }
            });
           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.

[smitpatel]

• SqlFragmentExpression is not for that purpose.
• Have you looked at query filters? The code you are trying to use has a lot of internal components and highly likely to break in unexpected way. Simply, don't use it that way.

[SuheylZ]

Yes but what exactly am I doing wrong here? Why it's not able to identify and do the translation?

[smitpatel]

There can be a lot of things which would be going wrong. The very least, following a post about 2.1 version in version 5.0 which has changed quite a lot internally. Only customer facing APIs and UX were maintained, nothing internal.
The very first error you are hitting that MethodCallTranslators cannot have entity instance as argument. It has to be a scalar value which can be mapped by database provider. Few more errors to articulate, IsDeletedExpression is of condition type rather than value so SqlServer will generate invalid SQL unless you modify that code too. SqlFragmentExpression is wrong type of expression to use for this.

We would be glad to help you out if things are not working for supported scenario. Since this is not a supported use-case, you shouldn't be using it.

[SuheylZ]

I'm not using 5.0 but the version is 3.1.5. Stakeholders do not want to migrate to 5.0 now. My objective is to execute this condition on the server side.

SELECT ..... WHERE (DeletedOn IS NULL) 

OR

SELECT ... WHERE (DeletedOn is not null)

I don't want to bring those millions of records on client side and also sometimes I want to apply condition on the server side so that only necessary data is received. so what you say scalar values, that means a string or number that should be passed to IsDeleted function? You see it's not really about a particular value but for a column against which I need filtering.

[smitpatel]

Please use https://docs.microsoft.com/en-us/ef/core/querying/filters

[SuheylZ]

Okay, let me try this but in this case I need to define Query Filter for every entity and we have almost a large number of them.

[smitpatel]

I looked at the referenced article and all the code above, there are multiple errors in both article and above user attempt. Let me try to describe all of them.

• Article defines a customer Expression - RowNumberExpression in order to print during SQL generation, it uses SqlFragmentExpression which is wrong. SqlFragmentExpression is used for tokens in SQL which does not correspond to anything specific, e.g. * in COUNT(*).
• The article utilizes the fact that arguments passed to function are translated by EF Core into columns but never mentions that they are required to be column.
  In above user code,
• Article showed 2 ways to add a translation of a custom function - through DbFunction or through IMethodCallTranslatorPlugin. Above code is trying to use both in the same app. It does not work that way.
• IsDeletedExpression is intercepting IQuerySqlGeneratorFactory in Accept method. IQuerySqlGeneratorFactory is not an expression visitor and it would never be found.
• Following missing piece from article about translation of component, this function uses entity itself as argument rather than a column, which is not supported and never worked. Article skipped over this fact.
• Printing [DeletedOn] IS NULL in SQL from IsDeletedExpression is ambiguous. Which table the column is coming from? What happens if the query did a join before putting the function in the predicate and both of the entities in the join had DeletedOn column. The generated SQL would be invalid.

A right way to do this translation would be just use like normal property.

// class
public class Employee : IActiveEntity
{
    public bool IsDeleted {get; set; } // Mapped to IsDeleted column in the table
// other properties on the entities.
}

// In the query
var query = context.Employees.Where(e => !e.IsDeleted).Max(e => e.Age);

You can apply IsDeleted filter directly in the query when using it without using any custom function mapping or any additional code just by mapping the property to the column you already have in the database.
This is possible for the Employees query you writing, if they are introduced by navigation then you may not be able to add the predicate always that is where global query filter works by adding the predicate for you internally whenever the db set for Employee is used.
I am closing this as the final customer scenario is supported as mentioned above and the code shared is wrong and it will not be supported.