LINQ 表达式返回属性值? [英] LINQ Expression to return Property value?

问题描述

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

I'm trying to create a generic function to help me select thousands of records using LINQ to SQL from a local list. SQL Server (2005 at least) limits queries to 2100 parameters and I'd like to select more records than that.

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

Here would be a good example usage:

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

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

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

Here is my (non-working) implementation:

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

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

I have seen plenty of examples of building predicates using expressions. In this case I only want to execute the delegate to return the value of the current ProductNumber. Or rather, I want to translate this into the SQL query (it works fine in non-generic form).

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

I know that compiling the Expression just takes me back to square one (passing in the delegate as Func) but I'm unsure of how to pass a parameter to an "uncompiled" expression.

感谢您的帮助！

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

**** ** Let me clarify further:

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

Here is a working example of what I want to generalize:

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 代码的结果:

Results in this SQL code:

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)

推荐答案

最简单的方法:使用 LINQKit(免费、非限制性许可)

Easiest way to do this: Use LINQKit (Free, non-restrictive license)

代码的工作版本:

public static IEnumerable<T> SelectByParameterList<T, PropertyType>(this Table<T> items, IEnumerable<PropertyType> parameterList, Expression<Func<T, PropertyType>> propertySelector, int blockSize) where T : class
{
    var groups = parameterList
        .Select((Parameter, index) =>
            new
            {
                GroupID = index / blockSize, //# of parameters per request
                Parameter
            }
        )
        .GroupBy(x => x.GroupID)
        .AsEnumerable();

    var selector = LinqKit.Linq.Expr(propertySelector);

    var results = groups
    .Select(g => new { Group = g, Parameters = g.Select(x => x.Parameter) } )
    .SelectMany(g => 
        /* AsExpandable() extension method requires LinqKit DLL */
        items.AsExpandable().Where(item => g.Parameters.Contains(selector.Invoke(item)))
    );

    return results;
}

示例用法:

    Guid[] local_refill_ids = Refills.Select(r => r.Id).Take(20).ToArray();

    IEnumerable<Refill> results = Refills.SelectByParameterList(local_refill_ids, r => r.Id, 10); //runs 2 SQL queries with 10 parameters each

再次感谢您的帮助！

这篇关于LINQ 表达式返回属性值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！