How can I convert a custom function to a sql expression for Entity Framework Core 3.1

Question

I am looking for some guidance/help on how to approach my problem as I'm running out of ideas. I am trying to take a custom extension function and pass it to the database via Entity Framework (EF Core 3.1) using Linq to Entities but I am getting a "{method} could not be translated" error regardless of what I do. I have tried using HasDbFunction along with HasTranslation using this link Thinktecture - Entity Framework Core - Custom Functions (using HasDbFunction) to no avail. I have also tried to registera custom DbCommandInterceptor with this link Medium - Interception in Entity Framework Core and it never hits the breakpoint or logs any of my debug statements. I'm not sure what to try next but I'm looking for help on what I'm doing wrong or guidance on what to research next.

For some context with my problem I am using the class setup below:

namespace Rebates.Models
{
    public class Rebate
    {
        public int Id { get; set; }
        public string ProductName { get; set; }
        public ActiveDateRange ActiveDateRange { get; set; }

        public decimal Discount { get; set; }
    }

    public class ActiveDateRange
    {
        public int StartMonth { get; set; }
        public int EndMonth { get; set; }
    }
}

DbContext:

namespace Rebates.DB
{
    public class RebateContext : DbContext
    {
        public DbSet<Rebate> Rebates { get; set; }
    }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlServer("Server=(local);Database=Rebates;Trusted_Connection=True;");
    }
}

Extension Method I'm trying to turn into a SQL statement:

namespace Rebates.ExtensionMethods
{
    public static class Extensions
    {
        public static bool IsActive(this Rebate rebate, DateTime date)
        {
            return date.Month >= rebate.ActiveDateRange.StartMonth && date.Month <= rebate.ActiveDateRange.EndMonth;
        }
    }
}

Call I'm attempting to make in my program:

using (var db = new RebateContext())
{
    var rebates = db.Rebates.Where(x => x.IsActive(DateTime.Now));
}

Error Received:

System.InvalidOperationException: 'The LINQ expression 'DbSet .Where(c => EF.Property(c, "ActiveDateRange").IsActive(DateTime.Now))' 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.'

I stripped out all of my failing code to hopefully make this cleaner but I can post my failed attempts as well if it will help, I just didn't want to clutter this already lengthy request. I have read a little on how to build expression trees and how this translates in EF Core but I'm honestly lost as to where I would even intercept said expression tree to modify it for my goal. Any help here would be greatly appreciated.

Answer 1

An interesting question, that I wanted to solve so I could use it too.

UPDATE; in EF Core 7 I believe you could replace this with an IQueryExpressionInterceptor service.

First we need to add a hook early in the query compilation pipeline. Note that this bit may break in future if the internals of EF Core change;

    public class QueryCompilationFactory : IQueryCompilationContextFactory
    {
        private readonly QueryCompilationContextDependencies dependencies;

        public QueryCompilationFactory(QueryCompilationContextDependencies dependencies)
        {
            this.dependencies = dependencies;
        }

        public QueryCompilationContext Create(bool async) => new QueryCompilation(dependencies, async);
    }

    public class QueryCompilation : QueryCompilationContext
    {
        public QueryCompilation(QueryCompilationContextDependencies dependencies, bool async) : base(dependencies, async)
        {
        }

        public override Func<QueryContext, TResult> CreateQueryExecutor<TResult>(Expression query)
        {
            // TODO, modify the query here
            return base.CreateQueryExecutor<TResult>(query);
        }
    }

    // in startup...
    services.AddDbContextPool<..>(o =>
    {
        o.ReplaceService<IQueryCompilationContextFactory, QueryCompilationFactory>();
        // ...
    }

Ok, so now we want a general way to replace a function call with an equivalent expression. That way the rest of the query pipeline can turn that into sql for us. Lets write an ExpressionVisitor that replaces method calls by inlining another LambdaExpression. Replacing the lambda parameters with the call arguments wherever they appear. Then we can use the visitor to replace the TODO above.

    private static Dictionary<MethodInfo, LambdaExpression> replacementExpressions = new Dictionary<MethodInfo, LambdaExpression>();

    public static void ReplaceMethod<T>(T method, Expression<T> replacement) where T : Delegate =>
        replacementExpressions.Add(method.Method, replacement);

    public class ArgumentVisitor : ExpressionVisitor
    {
        private readonly Dictionary<ParameterExpression, Expression> parameters;
        public ArgumentVisitor(Dictionary<ParameterExpression, Expression> parameters)
        {
            this.parameters = parameters;
        }

        protected override Expression VisitParameter(ParameterExpression node)
        {
            if (parameters.TryGetValue(node, out var replacement))
                return replacement;
            return base.VisitParameter(node);
        }
    }

    public class MethodVisitor : ExpressionVisitor
    {
        protected override Expression VisitMethodCall(MethodCallExpression node)
        {
            if (replacementExpressions.TryGetValue(node.Method, out var lambda))
            {
                var args = new Dictionary<ParameterExpression, Expression>();
                for (var i = 0; i < lambda.Parameters.Count; i++)
                    args[lambda.Parameters[i]] = node.Arguments[i];
                return new ArgumentVisitor(args).Visit(lambda.Body);
            }
            return base.VisitMethodCall(node);
        }
    }

    public override Func<QueryContext, TResult> CreateQueryExecutor<TResult>(Expression query)
    {
        query = new MethodVisitor().Visit(query);
        return base.CreateQueryExecutor<TResult>(query);
    }

Now we can define any static methods we want, and define a replacement expression to use when converting that method call into sql.

    public static bool IsActive(this Rebate rebate, DateTime date) =>
        date.Month >= rebate.ActiveDateRange.StartMonth && date.Month <= rebate.ActiveDateRange.EndMonth;
    
    static Extensions(){
        QueryCompilation.ReplaceMethod<Func<Rebate,DateTime,bool>>(IsActive, (Rebate rebate, DateTime date) =>
            date.Month >= rebate.ActiveDateRange.StartMonth && date.Month <= rebate.ActiveDateRange.EndMonth);
    }

Answer 2

Expanding on Jeremy Lakeman's answer, it's not that difficult to expand this to work for instance methods as well. An additional ReplaceMethod<T> method needs to be added to QueryCompilation like so:

public static void ReplaceMethod<T>(Expression<T> expression, Expression<T> replacement)
    where T : Delegate
{
    if (expression.Body is not MethodCallExpression methodCall)
        throw new ArgumentException("Not a method call", nameof(expression));
    if (methodCall.Object != null && !expression.Parameters[0].Type.IsAssignableFrom(methodCall.Object.Type))
        throw new ArgumentException("Instance method is for wrong type", nameof(expression));
    ReplacementExpressions.Add(methodCall.Method, replacement);
}

This could then be used for situations where you want to rewrite a method on an existing class within a query that Entity Framework does not recognize itself:

QueryCompilation.ReplaceMethod<Func<DateRange, DateTime, bool>>(
    (range, time) => range.Includes(time),
    (range, time) => range.Start >= time && range.End <= time
);