Dynamic predicates for Linq-to-Entity queries

Question

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?

score 2 · Accepted Answer · answered Jun 18 '12 at 21:16

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));

score 1 · Answer 2 · answered Jun 18 '12 at 17:34

1

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));

answered Jun 18 '12 at 17:34

Ocelot20

10,510
11
55
96

But where am I choosing on what property the predicate operates? – Dave Mateer Jun 18 '12 at 17:38
Oops, glossed over the real question and jumped to what I thought your issue was. Can you make your method an `Expression>` instead of `Func`? – Ocelot20 Jun 18 '12 at 17:49
Reason being: http://stackoverflow.com/questions/793571/why-would-you-use-expressionfunct-rather-than-funct – Ocelot20 Jun 18 '12 at 17:52
I could use `Expression>`, but then I'm not sure where to go from there. – Dave Mateer Jun 18 '12 at 18:18

Raphaël Althaus · Answer 3 · 2012-06-19T08:32:57.460

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();

Dynamic predicates for Linq-to-Entity queries

3 Answers3

Linked