使用 Expression<Func<TSource, TKey>>带有可查询的 [英] Using Expression&lt;Func&lt;TSource, TKey&gt;&gt; with IQueryable

问题描述

我正在尝试编写一个函数，该函数使用键选择器和SQL或内存中的集合(如果集合大于特定阈值)来过滤 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
}

它可以在内存中"的情况下编译和工作，但不适用于Sql Server的情况.我得到:

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

不支持方法'System.Object DynamicInvoke(System.Object [])'转换为SQL

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

我怀疑我需要将其更改为 Expression< Func< TSource，TKey>> ，但是不确定如何使用它.任何帮助表示赞赏.

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

推荐答案

您在这里所做的就是将一个函数组合在另一个函数中.对于委托，这很容易，因为您可以调用一个然后将结果作为参数传递给另一个.编写表达式要稍微复杂一些；您需要用该参数组成的表达式替换该参数使用的所有实例.幸运的是，您可以将此逻辑提取到其自己的方法中:

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

现在您可以写:

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
}

另一方面，如果您的过滤器集足够大，除了将整个集合带入内存之外，您还有其他选择.您可以做的是将过滤器集合分成多个批次，从数据库中获取每个批次，然后合并结果.这可以绕过 IN 子句中最大项目数的限制，同时仍然可以在数据库端完成工作.视数据的具体情况而定，可能会更好，也可能不会更好，但这是要考虑的另一种选择:

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

这篇关于使用 Expression<Func<TSource, TKey>>带有可查询的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！