LINQ to Entities does not recognize the method (for a linked object)

Question

I am trying to make the code more readable and do not quite understand how to structure the extension method and / or expression to do this. Currently, we have many objects that have RecordStatusTypeId on them (implemented from the IRecordStatus interface)

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

The goal here is to replace an expression of type .Where(RecordStatusTypeId != (int)RecordStatusTypes.Deleted) with an extension method of type .ActiveRecords()

I can 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 is great for statements like MyDbContext.Entities.Where(e => e.RecordStatusTypeId != (int)RecordStatusTypes.Deleted) , but I get the error message “LINQ to Entities does not recognize the method” if I try to replace that something like:

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

with what i would 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 active records in DbSet , as well as on a related object inside the linq clause?

Answer 1

It looks like you may have mistaken your conversion. If:

 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 get an exception because Queryable.Where expects an expression that can be translated into SQL, and ActiveRecords cannot be translated into SQL.

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

I am going to provide a project for a solution that works. This is very specific to the example you provided. You should probably work on this to make it general.

The following visitor expression will basically change the ActiveRecords call to the Where call with the corresponding 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(); } } 

Then you can create a special WhereSpecial method to view the expression before passing it to the real Where Queryable method:

 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 as follows:

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

Answer 3

Try making the bulk of the IQueryable query, then tag the where clause ...

Sort of:

 IQueryable temp = from x in MyDbContext.Entities select x; temp = temp.Where(e => e.RelatedEntity.ActiveRecords().Any()); 

or evaluate the main part, and then run the where in memory command.