Dynamic predicates for Linq-to-Entity queries

问题:

The following Linq-to-Entities query works fine:

var query = repository.Where(r => r.YearProp1.HasValue &&
                                  r.YearProp1 >= minYear &&
                                  r.YearProp1 <= maxYear);

My database has a dozen or so columns that all report year-related information (short? data type). I want to reuse the same Linq-to-Entities logic for all these columns. Something like:

Func<RepoEntity, short?> fx = GetYearPropertyFunction();
var query = repository.Where(r => fx(r).HasValue &&
                                  fx(r) >= minYear &&
                                  fx(r) <= maxYear);

This results in the error:

LINQ to Entities does not recognize the method 'System.Nullable`1[System.Int16] fx(RepoEntity)' method, and this method cannot be translated into a store expression.

I understand why I am getting the error, but am wondering if there is a workaround that doesn't involve duplicating code a dozen times just to change the property on which the SQL query is operating.

I would be reusing the function in more than one query, so I guess the general version of my question is: Is there a way to convert a simple property-getter lambda function to an Expression that can be consumed by Linq-to-Entities?

回答1:

Building off of Raphaël Althaus' answer, but adding the generic selector you were originally looking for:

public static class Examples
{
    public static Expression<Func<MyEntity, short?>> SelectPropertyOne()
    {
        return x => x.PropertyOne;
    }

    public static Expression<Func<MyEntity, short?>> SelectPropertyTwo()
    {
        return x => x.PropertyTwo;
    }

    public static Expression<Func<TEntity, bool>> BetweenNullable<TEntity, TNull>(Expression<Func<TEntity, Nullable<TNull>>> selector, Nullable<TNull> minRange, Nullable<TNull> maxRange) where TNull : struct
    {
        var param = Expression.Parameter(typeof(TEntity), "entity");
        var member = Expression.Invoke(selector, param);

        Expression hasValue = Expression.Property(member, "HasValue");
        Expression greaterThanMinRange = Expression.GreaterThanOrEqual(member,
                                             Expression.Convert(Expression.Constant(minRange), typeof(Nullable<TNull>)));
        Expression lessThanMaxRange = Expression.LessThanOrEqual(member,
                                          Expression.Convert(Expression.Constant(maxRange), typeof(Nullable<TNull>)));

        Expression body = Expression.AndAlso(hasValue,
                      Expression.AndAlso(greaterThanMinRange, lessThanMaxRange));

        return Expression.Lambda<Func<TEntity, bool>>(body, param);
    }
}

Could be used somewhat like the original query you were looking for:

Expression<Func<MyEntity, short?>> whatToSelect = Examples.SelectPropertyOne;

var query = Context
            .MyEntities
            .Where(Examples.BetweenNullable<MyEntity, short>(whatToSelect, 0, 30));

回答2:

A predicate is a filter in itself that should evaluate to bool (for whether or not to include it in the results). You can rework your method to look like this and it should work:

public static Expression<Func<RepoEntity, bool>> FitsWithinRange(int minYear, int maxYear)
{
    return w => w.HasValue && w >= minYear && w <= maxYear;
}

Edit: Oh and to use it:

var query = repository.Where(Repository.FitsWithinRange(minYear, maxYear));

回答3:

You could do something like that (not sure if it will work "as is" in linq2 entities, but if you have a problem... just tell)

usage

var query = <your IQueryable<T> entity>.NullableShortBetween(1, 3).ToList();

function

public static IQueryable<T> NullableShortBetween<T>(this  IQueryable<T> queryable, short? minValue, short? maxValue) where T: class
        {
            //item (= left part of the lambda)
            var parameterExpression = Expression.Parameter(typeof (T), "item");

            //retrieve all nullable short properties of your entity, to change if you have other criterias to get these "year" properties
            var shortProperties = typeof (T).GetProperties().Where(m => m.CanRead && m.CanWrite && m.PropertyType == typeof(short?));

            foreach (var shortProperty in shortProperties)
            {
                //item (right part of the lambda)
                Expression memberExpression = parameterExpression;
                //item.<PropertyName>
                memberExpression = Expression.Property(memberExpression, shortProperty);
                //item.<PropertyName>.HasValue
                Expression firstPart = Expression.Property(memberExpression, "HasValue");
                //item.<PropertyName> >= minValue
                Expression secondPart = Expression.GreaterThanOrEqual(memberExpression, Expression.Convert(Expression.Constant(minValue), typeof (short?)));
                //item.<PropertyName> <= maxValue
                var thirdPart = Expression.LessThanOrEqual(memberExpression, Expression.Convert(Expression.Constant(maxValue), typeof (short?)));
                //item.<PropertyName>.HasValue && item.<PropertyName> >= minValue
                var result = Expression.And(firstPart, secondPart);
                //item.<PropertyName>.HasValue && item.<PropertyName> >= minValue && item.<PropertyName> <= maxValue
                result = Expression.AndAlso(result, thirdPart);
                //pass the predicate to the queryable
                queryable = queryable.Where(Expression.Lambda<Func<T, bool>>(result, new[] {parameterExpression}));
            }
            return queryable;
        }

EDIT : another solution, based on "simple" reflection, which "looks" as the one you want

public static short? GetYearValue<T>(this T instance)
        {
            var propertyInfo = typeof(T).GetProperties().FirstOrDefault(m => m.CanRead && m.CanWrite && m.PropertyType == typeof(short?));
            return propertyInfo.GetValue(instance, null) as short?;
        }

usage

var result = list.Where(item => item.GetYearValue() != null && item.GetYearValue() >= 1 && item.GetYearValue() <= 3).ToList();