CODEWITHSUNDEEP
c#linqlinq-to-sqllinq-to-objects
Magnus
Magnus

Reputation: 46947

Using Expression<Func<TSource, TKey>> with IQueryable

Im trying to write a function that filters an IQueryable datasource using a key selector and a collection either in SQL or in memory if the collection is bigger than a specific threshold value.

This is what I have right now.

public static IEnumerable<TSource> SafeFilter<TSource, TKey>(this IQueryable<TSource> source, Func<TSource, TKey> keySelector, HashSet<TKey> filterSet, int threshold = 500)
{    
     if (filterSet.Count > threshold)
         return source.AsEnumerable().Where(x => filterSet.Contains(keySelector(x))); //In memory
     return source.Where(x => filterSet.AsEnumerable().Contains(keySelector(x)));     //In SQL
}

It compiles and works for the "In memory" case but not for the Sql server case. I get:

Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL

I suspect I need to change it to Expression<Func<TSource, TKey>> but is unsure how to use it. Any help appreciated.

Upvotes: 0

Views: 2972

Answers (1)

Servy
Servy

Reputation: 203827

What you're doing here is composing one function within another. For delegates this is easy, as you can invoke one and then pass the result as a parameter to another. To compose expressions is slightly more involved; you need to replace all instances of the use of that parameter with the expression it is composing. Fortunately you can extract this logic into its own method:

public static Expression<Func<TFirstParam, TResult>>
    Compose<TFirstParam, TIntermediate, TResult>(
    this Expression<Func<TFirstParam, TIntermediate>> first,
    Expression<Func<TIntermediate, TResult>> second)
{
    var param = Expression.Parameter(typeof(TFirstParam), "param");

    var newFirst = first.Body.Replace(first.Parameters[0], param);
    var newSecond = second.Body.Replace(second.Parameters[0], newFirst);

    return Expression.Lambda<Func<TFirstParam, TResult>>(newSecond, param);
}

This uses the following method to replace all instances of one expression with another:

public static Expression Replace(this Expression expression,
    Expression searchEx, Expression replaceEx)
{
    return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
}
internal class ReplaceVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public ReplaceVisitor(Expression from, Expression to)
    {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
}

Now you can write:

public static IEnumerable<TSource> SafeFilter<TSource, TKey>
    (this IQueryable<TSource> source,
    Expression<Func<TSource, TKey>> keySelector,
    HashSet<TKey> filterSet,
    int threshold = 500)
{
    if (filterSet.Count > threshold)
    {
        var selector = keySelector.Compile();
        return source.AsEnumerable()
            .Where(x => filterSet.Contains(selector(x))); //In memory
    }
    return source.Where(keySelector.Compose(
        key => filterSet.AsEnumerable().Contains(key)));     //In SQL
}

On a side note, if your filter set is sufficiently large you have another option besides bringing the entire collection into memory. What you can do is break your filter set into batches, fetching each batch from the database and combining the results. This gets around the limitations on the max number of items in an IN clause while still letting the work be done on the database end. It may or may not be better, depending on the specifics of the data, but it's another option to consider:

public static IEnumerable<TSource> SafeFilter<TSource, TKey>
    (this IQueryable<TSource> source,
    Expression<Func<TSource, TKey>> keySelector,
    HashSet<TKey> filterSet,
    int batchSize = 500)
{
    return filterSet.Batch(batchSize)
            .SelectMany(batch => source.Where(keySelector.Compose(
                key => batch.Contains(key))));
}

Upvotes: 2

Related Questions