LINQ to Entities does not recognize the method (on a related entity)

Question

I am trying to make some code more readable and am not quite grasping how to structure the extension method and/or expression to do it. We currently have many entities that have a RecordStatusTypeId on them (implemented from an interface of IRecordStatus)

public interface IRecordStatus
{
    int RecordStatusTypeId { get; set; }
    RecordStatusType RecordStatusType { get; set; }
}

The goal here is to replace a statement like .Where(RecordStatusTypeId != (int)RecordStatusTypes.Deleted) with an extension method like .ActiveRecords()

I'm able to accomplish this with the following Extension method:

public static IQueryable<T> ActiveRecords<T>(this DbSet<T> entitySet)
    where T : class, IRecordStatus
{
    return entitySet.Where(e => e.RecordStatusTypeId != (int)RecordStatusTypes.Deleted);
}

*I have this extension method for DbSet<T>, IQueryable<T>, ICollection<T>, and IEnumerable<T>

This works great for statements like MyDbContext.Entities.Where(e => e.RecordStatusTypeId != (int)RecordStatusTypes.Deleted), but I get the error "LINQ to Entities does not recognize the method" if I try to replace something like:

MyDbContext.Entities.Where(e => e.RelatedEntity.Where(re => re.RecordStatusTypeId != (int)RecordStatusTypes.Deleted));

with what I'd like to do:

MyDbContext.Entities.Where(e => e.RelatedEntity.ActiveRecords().Any());

How can I change my Extension Methods (or add an Expression) so that I can filter for active records on the DbSet as well as on a related entity inside a linq clause?

Answer 1

It looks like you may have done your conversion wrong. Should:

MyDbContext.Entities.Where(e => e.RelatedEntity.Where(re => re.RecordStatusTypeId != (int)RecordStatusTypes.Deleted));

convert to this:

MyDbContext.Entities.Where(e => e.RelatedEntity.ActiveRecords().Any());

Answer 2

You are getting the exception because Queryable.Where expects an expression that can be translated to SQL, and ActiveRecords cannot be translated to SQL.

What you need to do is to update the expression to expand the call to ActiveRecords to a call to .Where(e => e.RecordStatusTypeId != (int)RecordStatusTypes.Deleted).

I am going to provide a draft for a solution that works. It is very specific to the example you provided. You should probably work on it to make it generic.

The following expression visitor will basically change the call to ActiveRecords to the call to Where with the appropriate expression as an argument:

public class MyVisitor : ExpressionVisitor
{
    protected override Expression VisitMethodCall(MethodCallExpression m)
    {
        if (m.Method.Name == "ActiveRecords")
        {
            var entityType = m.Method.GetGenericArguments()[0];
            var whereMethod = genericWhereMethod.MakeGenericMethod(entityType);

            var param = Expression.Parameter(entityType);
            var expressionToPassToWhere =
                Expression.NotEqual(
                    Expression.Property(param, "RecordStatusTypeId"),
                    Expression.Constant((int)RecordStatusTypes.Deleted));

            Expression newExpression =
                Expression.Call(
                    whereMethod,
                    m.Arguments[0],
                    Expression.Lambda(
                        typeof(Func<,>).MakeGenericType(entityType, typeof(bool)),
                        expressionToPassToWhere,
                        param));

            return newExpression;
        }

        return base.VisitMethodCall(m);
    }

    //This is reference to the open version of `Enumerable.Where`
    private static MethodInfo genericWhereMethod;

    static MyVisitor()
    {
        genericWhereMethod = typeof (Enumerable).GetMethods(BindingFlags.Public | BindingFlags.Static)
            .Where(x => x.Name == "Where" && x.GetGenericArguments().Length == 1)
            .Select(x => new {Method = x, Parameters = x.GetParameters()})
            .Where(x => x.Parameters.Length == 2 &&
                        x.Parameters[0].ParameterType.IsGenericType &&
                        x.Parameters[0].ParameterType.GetGenericTypeDefinition() == typeof (IEnumerable<>) &&
                        x.Parameters[1].ParameterType.IsGenericType &&
                        x.Parameters[1].ParameterType.GetGenericTypeDefinition() == typeof (Func<,>))
            .Select(x => x.Method)
            .Single();
    }
}

You could then create a special WhereSpecial method to visit the expression before passing it to the real Where method of Queryable:

public static class ExtentionMethods
{
    public static IQueryable<T> WhereSpecial<T>(this IQueryable<T> queryable, Expression<Func<T,bool>> expression )
    {
        MyVisitor visitor = new MyVisitor();

        var newBody = visitor.Visit(expression.Body);

        expression = expression.Update(newBody, expression.Parameters);

        return queryable.Where(expression);
    }
}

And then you can use it like this:

var result = MyDbContext.Entities.WhereSpecial(e => e.RelatedEntity.ActiveRecords().Any());