CODEWITHSUNDEEP
c#linqentity-framework-core
Kasbolat Kumakhov
Kasbolat Kumakhov

Reputation: 731

Extending EF Core 'where' clause with custom expression

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.

Upvotes: 7

Views: 9030

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205899

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.

Upvotes: 8

Related Questions