Extending EF Core 'where' clause with custom expression

Question

I have a bunch of entities which have an active period defined like 'StartDate' and 'EndDate' fields. Most of the time i need to query them checking their active period against some custom values. The code pretty much looks like this:

public static Expression<Func<T, bool>> IsPeriodActive<T>(DateTime checkPeriodStart, DateTime checkPeriodEnd, Func<T, DateTime> entityPeriodStart, Func<T, DateTime> entityPeriodEnd) =>
    entity =>
        (checkPeriodEnd >= entityPeriodStart(entity) && checkPeriodEnd <= entityPeriodEnd(entity))
        || (checkPeriodStart >= entityPeriodStart(entity) && checkPeriodEnd <= entityPeriodEnd(entity))
        || (entityPeriodStart(entity) >= checkPeriodStart && entityPeriodStart(entity) <= checkPeriodEnd)
        || (entityPeriodEnd(entity) >= checkPeriodStart && entityPeriodEnd(entity) <= checkPeriodEnd)
        || (entityPeriodStart(entity) >= checkPeriodStart && entityPeriodStart(entity) <= checkPeriodEnd);

The problem is that Func.Invoke() can't be translated to SQL, which is obvious. How do i extend EF Core to add this kind of 'where' condition for any entity type? I can't use Filters, since sometimes i need to query raw data or with just one period check (not both) and also some entities have these fields named differently.

score 8 · Accepted Answer · answered Dec 19 '18 at 09:54

You need to change the Func<T, DateTime> arguments to Expression<Func<T, DateTime>> and incorporate them in the desired expression.

Unfortunately neither C# compiler nor BCL helps with the later task (expression composition from other expressions). There are some 3rd party packages like LinqKit, NeinLinq etc. which address the issue, so if you are planning to use expression composition intensively, you might consider using one of these libraries.

But the principle is one and the same. At some point a custom ExpressionVisitor is used to replace parts of the original expression with another expressions. For instance, what I'm using for such simple scenarios is to create compile time lambda expression with additional parameters used as placeholders, which then are replaced with the actual expressions pretty much the same way as string.Replace.

In order to do that, I use the following helper method for replacing lambda expression parameter with another expression:

public static partial class ExpressionUtils
{
    public static Expression ReplaceParameter(this Expression expression, ParameterExpression source, Expression target)
    {
        return new ParameterReplacer { Source = source, Target = target }.Visit(expression);
    }

    class ParameterReplacer : ExpressionVisitor
    {
        public ParameterExpression Source;
        public Expression Target;
        protected override Expression VisitParameter(ParameterExpression node)
            => node == Source ? Target : base.VisitParameter(node);
    }
}

and the method in question could be like this:

public static Expression<Func<T, bool>> IsPeriodActive<T>(
    DateTime checkPeriodStart,
    DateTime checkPeriodEnd,
    Expression<Func<T, DateTime>> entityPeriodStart,
    Expression<Func<T, DateTime>> entityPeriodEnd)
{
    var entityParam = Expression.Parameter(typeof(T), "entity");
    var periodStartValue = entityPeriodStart.Body
        .ReplaceParameter(entityPeriodStart.Parameters[0], entityParam);
    var periodEndValue = entityPeriodEnd.Body
        .ReplaceParameter(entityPeriodEnd.Parameters[0], entityParam);

    Expression<Func<DateTime, DateTime, bool>> baseExpr = (periodStart, periodEnd) =>
        (checkPeriodEnd >= periodStart && checkPeriodEnd <= periodEnd)
        || (checkPeriodStart >= periodStart && checkPeriodEnd <= periodEnd)
        || (periodStart >= checkPeriodStart && periodStart <= checkPeriodEnd)
        || (periodEnd >= checkPeriodStart && periodEnd <= checkPeriodEnd)
        || (periodStart >= checkPeriodStart && periodStart <= checkPeriodEnd);

    var periodStartParam = baseExpr.Parameters[0];
    var periodEndParam = baseExpr.Parameters[1];

    var expr = baseExpr.Body
        .ReplaceParameter(periodStartParam, periodStartValue)
        .ReplaceParameter(periodEndParam, periodEndValue);

    return Expression.Lambda<Func<T, bool>>(expr, entityParam);
}

Note that you need to rebind (using the same ReplaceParameter helper method) the bodies of the passed Expression<Func<T, DateTime>> expressions to a common parameter to be used in the result expression.

The code can be simplified by adding more helper methods like here Entity Framework + DayOfWeek, but again, if you are planning to use this a lot, a better choice would be to use some ready library because at the end you would start reinventing what these libraries do.

Thank you for detailed explanation. Played around with LinqKit and NeinLinq and picked the latter since it had less issues with subentities and a bit more clear usage. — Kasbolat Kumakhov, Dec 19 '18 at 10:19
@IvanStoev Trying to wrap my head around this. Could you please also post a sample of usage, i.e. a sample of how to use IsPeriodActive in your LINQ query (and how to set it's last two parameters). — hhtech1, Dec 07 '20 at 14:08
@hhtech1 It works only for top level queries. Imagine you have an entity class `Foo` with two `DateTime` type properties called `StartDate` and `EndDate`. Also you have two `DateTime` variables called `checkPeriodStart` and `checkPeriodEnd`. You can use the above method as follows : `dbContext.Foos.Where(SomeStaticClass.IsPeriodActive(checkPeriodStart, checkPeriodEnd, x => x.StartDate, x.EndDate))`. The last two parameters are lambda expressions similar to when you do `.OrderBy(x => x.StartDate)` or `.Select(x => new { x.StartDate, x.EndDate })` etc. — Ivan Stoev, Dec 07 '20 at 14:56

Extending EF Core 'where' clause with custom expression

1 Answers1