Using Expression<Func<T,bool>> as parameter for new expression

Question

I have some code that currently builds up an In statement in SQL. I build an expression, it returns;

value(generic(list[T])).Contains(x => x.Id)

This works fine, so if I have a list of objects;

public class ObjectToSearch 
{
  public int IdToSearchOn {get;set;}
}

And I want to search for ids 1, 2, 3, it works just fine. My SQL query is great.

Now, I have a need to search a list of nested objects, so I might have;

public class ParentObjectToSearch 
{
  public IEnumerable<ObjectToSearch> Objects {get;set;}
}

So, looking at some code I found (How do I create an expression tree calling IEnumerable<TSource>.Any(...)?) I figured I could adapt the method, and wrap a call to Any or All, and it would work. This worked great, until I actually came to test against the database, I get;

Cannot compare elements of type 'System.Collections.Generic.ICollection`1'. Only primitive types (such as Int32, String, and Guid) and entity types are supported.

var collectionType = GetIEnumerableImpl( forCollection.Type );

Type elementType = collectionType.GetGenericArguments( )[0];

MethodInfo method = BaseFilter.GetType( ).GetMethod( "FilterWith" );

MethodInfo genericMethod = method.MakeGenericMethod( new[] { elementType } );

return (genericMethod.Invoke( BaseFilter, null ) as LambdaExpression);

FilterWith is the method I'm calling on the original filter, in the hope of getting back my expression. So, it looks like my inner expression is being evaluated incorrectly when combined with the outer expression. What I'm basically aiming for is (I believe);

x => x.Collection.Contains( y => new { 1, 3, 3}.Contains( y.Id));

If I test the inner filtering separately, it works fine, so I assume it's just how I'm trying to combine the elements, and if I try and use Contains instead of Any or All, I still get the same error.

I've put Entity Framework in the tags, because this is being evaluated as expressions against an entity set, and someone may have experience of doing this.

Update Having a night to think about it, I think I have a better question;

How do I build a Where expression, so I can build;

x => x.Collection.Where( y => new[] { 1, 3 }.Contains( y.Id)).Count( ) > 0

Answer 1

The original error was actually being caused by the fact that I was trying to do check of null against the collection, well, that certainly can't be done in SQL.

Then, Any and All can't be converted to SQL expressions, so;

Expression<Func<TEntity, bool>> result = Expression.Lambda<Func<TEntity, bool>>(
            Expression.GreaterThan(
                Expression.Call( CountMethod( elementType ),
                                Expression.Call( WhereMethod( elementType ),
                                                theCollectionWeAreSearching,
                                                filter ) ),
                Expression.Constant( 0 ) ), param );

The elementType is the type of the element within the collection. The filter is an expression that tests my list. The Count and Where methods retrieved as follows;

public MethodInfo GetMethodFromEnumerable(string methodName, params Func<MethodInfo, bool>[] filters)
        {
            var methods = typeof( Enumerable )
                .GetMethods( BindingFlags.Static | BindingFlags.Public )
                .Where( mi => mi.Name == methodName );

            methods = filters.Aggregate( methods, (current, filter) => current.Where( filter ) );

            return methods.First( );
        }

        public MethodInfo WhereMethod(Type collectionType)
        {
            // Get the Func<T,bool> version
            var getWhereMethod = GetMethodFromEnumerable( "Where",
                                    mi => mi.GetParameters( )[1].ParameterType.GetGenericArguments( ).Count( ) == 2 );

            return getWhereMethod.MakeGenericMethod( collectionType );
        }

        public MethodInfo CountMethod(Type collectionType)
        {
            var getCountMethod = GetMethodFromEnumerable( "Count" ); // There can be only one

            return getCountMethod.MakeGenericMethod( collectionType );
        }

I think that what happened was the introduction of so much new code led me to look for problems where there weren't any!

Answer 2

I think that the problem here is that EF thinks that you are asking it to ship the ObjectToSearch to the database and compare on that. In other words, I think you are asking SQL Server whether any values in some field are equal to an instance of some class, which clearly won't work:

// This won't work because it is asking EF to generate a SQL value equivalent to some class instance
((List<ParentObjectToSearch>)someList).Contains(x => x.Id)

I can't be sure- it seems like a concrete example of this in use is missing from the question. If this sounds right, try flattening the set of values that you want to search on before generating the query:

// Assuming var outerList = some List<ParentObjectToSearch>
// this un-nests the IDs, so they can be sent to SQL Server as integers
// (which can be converted to a CONTAINS or = clause)
var listOfUnNestedIDs = outerList.SelectMany(po=>po.Objects.Select(o=>o.IdToSearchOn));