Use MemberExpression in Entity Framework query. Entity Framework IQueryable extension with MemberExpression

Question

Main task

Create IQueryable extensions for Entity Framework, where it is possible to use MemberExpression to setup fields used in query.

WhereOverlap is example. WhereOverlap build query to entities by two DateTime fields.
Orders has DateTime fields StartDate, EndDate.
Trips has DateTime fields From, To.

var dateFrom = DateTime.Now.AddDays(-30);
var dateTo = DateTime.Now.AddDays(-15);

var orders = await db.Orders
    .WhereOverlap(
        // MemberExpressions (Orders has StartDate, EndDate fields)
        fromField: es => es.StartDate,
        toField: es => es.EndDate,

        from: dateFrom,
        to: dateTo)
    .ToListAsync();

var trips = await db.Trips
    .WhereOverlap(
        // MemberExpressions (Trips has From, To fields)
        fromField: es => es.From,
        toField: es => es.To,

        from: dateFrom,
        to: dateTo)
    .ToListAsync();

To achieve what we want, we need an answer to any of the 2 questions:

or Question 1

To create such IQueryable extensions it is need to create Where extensions with the following signature:

public static class QueryableWhereOverlapExtensions
{
    static IQueryable<TEnt> Where<TEnt, TParam, TParam2>(this IQueryable<TEnt> source,
        Expression<Func<TEnt, TParam>> property,
        Expression<Func<TEnt, TParam2>> property2,
        Expression<Func<TParam, TParam2, bool>> where)
    {
        // ??? HOW TO DO THIS
        return ...
    }

    public static IQueryable<TEnt> WhereOverlap<TEnt>(this IQueryable<TEnt> source,
        Expression<Func<TEnt, DateTime>> startField,
        Expression<Func<TEnt, DateTime>> endField,
        DateTime from, DateTime to)
    {
        // example of usage Where extension
        return source.Where(startField, endField,
            (start, end) => start <= to && end >= from);
    }
}

How to implement IQueryable Where extension with shown signature?

or Question 2

It is possible to use A universal PredicateBuilder to build dynamic queries form expressions (Expression<Func<TEnt, bool>>). So if we will have such method we will be able to create WhereOverlap extension

static Expression<Func<TEnt, bool>> ApplayWhere<TEnt, TParam>(this Expression<Func<TEnt, TParam>> field, 
    Expression<Func<TParam, bool>> where) 
{
    // ??? HOW TO DO THIS
}

public static IQueryable<TEnt> WhereOverlap<TEnt>(this IQueryable<TEnt> source,
    Expression<Func<TEnt, DateTime>> startField,
    Expression<Func<TEnt, DateTime>> endField,
    DateTime from, DateTime to)
{
    // example of usage ApplayWhere
    Expression<Func<TEnt, bool>> startExpr = startField.ApplayWhere(start => start <= to);
    Expression<Func<TEnt, bool>> endExpr = endField.ApplayWhere(end => end >= from);

    return source.Where(PredicateBuilder.And(startExpr, endExpr));
}

How to implement ApplayWhere?

Answer 1

It is not exactly an aswer. But I create WhereOverlap extension, and provide a way for creating reusable linq queries where it is possible to use MemberExpression to setup fields used in query.

First we need MemberExpressionExtensions:

    public static class MemberExpressionExtensions {
        public static Expression<Func<TEnt, bool>> HasVal<TEnt, TProp>(this Expression<Func<TEnt, TProp?>> field) where TProp : struct
            => Expression.Lambda<Func<TEnt, bool>>(Expression.NotEqual(field.Body, Expression.Constant(null, typeof(TProp?))), field.Parameters);

        public static Expression<Func<TEnt, bool>> HasNoVal<TEnt, TProp>(this Expression<Func<TEnt, TProp?>> field) where TProp : struct
            => Expression.Lambda<Func<TEnt, bool>>(Expression.Equal(field.Body, Expression.Constant(null, typeof(TProp?))), field.Parameters);

        public static Expression<Func<TEnt, bool>> LessOrEqual<TEnt, TProp>(this Expression<Func<TEnt, TProp>> field, TProp val)
            => Expression.Lambda<Func<TEnt, bool>>(Expression.LessThanOrEqual(field.Body, Expression.Constant(val, typeof(TProp))), field.Parameters);

        public static Expression<Func<TEnt, bool>> Less<TEnt, TProp>(this Expression<Func<TEnt, TProp>> field, TProp val)
            => Expression.Lambda<Func<TEnt, bool>>(Expression.LessThan(field.Body, Expression.Constant(val, typeof(TProp))), field.Parameters);

        public static Expression<Func<TEnt, bool>> GreaterOrEqual<TEnt, TProp>(this Expression<Func<TEnt, TProp>> field, TProp val)
            => Expression.Lambda<Func<TEnt, bool>>(Expression.GreaterThanOrEqual(field.Body, Expression.Constant(val, typeof(TProp))), field.Parameters);

        public static Expression<Func<TEnt, bool>> Greater<TEnt, TProp>(this Expression<Func<TEnt, TProp>> field, TProp val)
            => Expression.Lambda<Func<TEnt, bool>>(Expression.GreaterThan(field.Body, Expression.Constant(val, typeof(TProp))), field.Parameters);
    }

Now we can create reusable query builder

class BigPayFilter {
    readonly decimal Limit;
    public BigPayFilter(decimal limit) {
        Limit = limit;
    }

    public Expression<Func<TEnt, bool>> Create<TEnt>(
        Expression<Func<TEnt, decimal>> field) {

        // GreaterOrEqual is extension
        return field.GreaterOrEqual(Limit);
    }
}

Usage. For example, there are Payout and Premium objects:

class Payout { 
    public decimal Total { get; set; }
}

class Premium {
    public decimal Sum { get; set; }
}

// filter to find payments greater or equal 1000
//
// you can get limit value from configuration (in this example limit is 1000),
// and put BigPayFilter to IoC-container
var bigPayFilter = new BigPayFilter(1000);


// use BigPayFilter for payouts

var payoutPredicate =
    bigPayFilter.Create<Payout>(pp => pp.Total);

var payouts = new[] {
    new Payout{ Total = 100 },
    new Payout{ Total = 50 },
    new Payout{ Total = 25.5m },
    new Payout{ Total = 1050.67m }
}
.AsQueryable()
.Where(payoutPredicate)
.ToList();


// use BigPayFilter for premiums

var premiumPredicate =
    bigPayFilter.Create<Premium>(pp => pp.Sum);

var premiums = new[] {
    new Premium{ Sum = 2000 },
    new Premium{ Sum = 50.08m },
    new Premium{ Sum = 25.5m },
    new Premium{ Sum = 1070.07m }
}
.AsQueryable()
.Where(premiumPredicate)
.ToList();

For more complex queries you have to combine MemberExpressionExtensions with "A universal PredicateBuilder" created by Pete Montgomery.

This soution fully supports Entity Framework, including Async operations.

I create NuGet and GitHub with this solution.