c# - LINQ 表达式返回属性值？

Question

我正在尝试创建一个通用函数来帮助我使用 LINQ to SQL 从本地列表中选择数千条记录。 SQL Server(至少 2005)将查询限制为 2100 个参数，我想选择比这更多的记录。

这是一个很好的示例用法:

var some_product_numbers = new int[] { 1,2,3 ... 9999 };

Products.SelectByParameterList(some_product_numbers, p => p.ProductNumber);

这是我的(非工作)实现:

public static IEnumerable<T> SelectByParameterList<T, PropertyType>(Table<T> items, 

IEnumerable<PropertyType> parameterList, Expression<Func<T, PropertyType>> property) where T : class
{
    var groups = parameterList
        .Select((Parameter, index) =>
            new
            {
                GroupID = index / 2000, //2000 parameters per request
                Parameter
            }
        )
        .GroupBy(x => x.GroupID)
        .AsEnumerable();

    var results = groups
    .Select(g => new { Group = g, Parameters = g.Select(x => x.Parameter) } )
    .SelectMany(g => 
        /* THIS PART FAILS MISERABLY */
        items.Where(item => g.Parameters.Contains(property.Compile()(item)))
    );

    return results;
}

我见过很多使用表达式构建谓词的例子。在这种情况下，我只想执行委托(delegate)以返回当前 ProductNumber 的值。或者更确切地说，我想将其转换为 SQL 查询(它在非通用形式下运行良好)。

我知道编译表达式只会让我回到原点(将委托(delegate)作为 Func 传递)，但我不确定如何将参数传递给“未编译”表达式。

感谢您的帮助!

**** 编辑:** 让我进一步澄清:

这是我想要概括的工作示例:

var local_refill_ids = Refills.Select(r => r.Id).Take(20).ToArray();

var groups = local_refill_ids
    .Select((Parameter, index) =>
        new
        {
            GroupID = index / 5, //5 parameters per request
            Parameter
        }
    )
    .GroupBy(x => x.GroupID)
    .AsEnumerable();

var results = groups
.Select(g => new { Group = g, Parameters = g.Select(x => x.Parameter) } )
.SelectMany(g => 
    Refills.Where(r => g.Parameters.Contains(r.Id))
)
.ToArray()
;

此 SQL 代码的结果:

SELECT [t0].[Id], ... [t0].[Version]
FROM [Refill] AS [t0]
WHERE [t0].[Id] IN (@p0, @p1, @p2, @p3, @p4)

... That query 4 more times (20 / 5 = 4)

Answer 1

我想出了一种将查询分 block 的方法——即你给它 4000 个值，所以它可能会执行 4 个请求，每个请求 1000 个；带有完整的 Northwind 示例。请注意，由于 Expression.Invoke，这在 Entity Framework 上可能不起作用 - 但在 LINQ to SQL 上没问题:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace ConsoleApplication5 {
    /// SAMPLE USAGE
    class Program {
        static void Main(string[] args) {
            // get some ids to play with...
            string[] ids;
            using(var ctx = new DataClasses1DataContext()) {
                ids = ctx.Customers.Select(x => x.CustomerID)
                    .Take(100).ToArray();
            }

            // now do our fun select - using a deliberately small
            // batch size to prove it...
            using (var ctx = new DataClasses1DataContext()) {
                ctx.Log = Console.Out;
                foreach(var cust in ctx.Customers
                        .InRange(x => x.CustomerID, 5, ids)) {
                    Console.WriteLine(cust.CompanyName);
                }
            }
        }
    }

    /// THIS IS THE INTERESTING BIT
    public static class QueryableChunked {
        public static IEnumerable<T> InRange<T, TValue>(
                this IQueryable<T> source,
                Expression<Func<T, TValue>> selector,
                int blockSize,
                IEnumerable<TValue> values) {
            MethodInfo method = null;
            foreach(MethodInfo tmp in typeof(Enumerable).GetMethods(
                    BindingFlags.Public | BindingFlags.Static)) {
                if(tmp.Name == "Contains" && tmp.IsGenericMethodDefinition
                        && tmp.GetParameters().Length == 2) {
                    method = tmp.MakeGenericMethod(typeof (TValue));
                    break;
                }
            }
            if(method==null) throw new InvalidOperationException(
                "Unable to locate Contains");
            foreach(TValue[] block in values.GetBlocks(blockSize)) {
                var row = Expression.Parameter(typeof (T), "row");
                var member = Expression.Invoke(selector, row);
                var keys = Expression.Constant(block, typeof (TValue[]));
                var predicate = Expression.Call(method, keys, member);
                var lambda = Expression.Lambda<Func<T,bool>>(
                      predicate, row);
                foreach(T record in source.Where(lambda)) {
                    yield return record;
                }
            }
        }
        public static IEnumerable<T[]> GetBlocks<T>(
                this IEnumerable<T> source, int blockSize) {
            List<T> list = new List<T>(blockSize);
            foreach(T item in source) {
                list.Add(item);
                if(list.Count == blockSize) {
                    yield return list.ToArray();
                    list.Clear();
                }
            }
            if(list.Count > 0) {
                yield return list.ToArray();
            }
        }
    }
}