C# LINQ 表达式返回属性值？

LINQ Expression to return Property value?

提问by kwcto

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.

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

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

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).

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

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.

我知道编译表达式只会让我回到第一个（将委托作为 Func 传递），但我不确定如何将参数传递给“未编译”的表达式。

Thanks for your help!

谢谢你的帮助！

**** EDIT:** 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()
;

Results in this SQL code:

此 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)

采纳答案by kwcto

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

最简单的方法：使用LINQKit（免费、非限制性许可）

Working version of code:

代码的工作版本：

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

Example usage:

用法示例：

    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

Thanks again for all your help!

再次感谢你的帮助！

回答by Marc Gravell

LINQ-to-SQL still works via standard SQL parameters, so writing a fancy expression isn't going to help. There are 3 common options here:

LINQ-to-SQL 仍然通过标准 SQL 参数工作，因此编写花哨的表达式无济于事。这里有 3 个常用选项：

• pack the ids into (for example) csv/tsv; pass down as a varchar(max)and use a udf to split it (at the server) into a table variable; join to the table variable
• use a table-valued-parameter in SQL Server 2008
• have a table on the server that you could push the ids into (perhaps via SqlBulkCopy) (perhaps with a "session guid" or similar); join to this table

• 将 id 打包到（例如）csv/tsv 中；传递为 avarchar(max)并使用 udf 将其（在服务器上）拆分为表变量；加入表变量
• 在 SQL Server 2008 中使用表值参数
• 在服务器上有一个表，您可以将 id 推送到其中（可能通过 SqlBulkCopy）（可能带有“会话 guid”或类似的）；加入这个表

The first is the simplest; getting a "split csv udf" is trivial (just search for it). Drag the udf onto the data-context and consume from there.

第一个是最简单的；获得“拆分 csv udf”是微不足道的（只需搜索它）。将 udf 拖到数据上下文上并从那里使用。

回答by Marc Gravell

I've come up with a way to chunk the query into pieces - i.e. you give it 4000 values, so it might do 4 requests of 1000 each; with full Northwind example. Note that this might not work on Entity Framework, due to Expression.Invoke- but is fine on LINQ to SQL:

我想出了一种将查询分块的方法——即你给它 4000 个值，所以它可能会做 4 个请求，每个请求 1000；完整的 Northwind 示例。请注意，这可能不适用于实体框架，因为Expression.Invoke- 但在 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();
            }
        }
    }
}

回答by Muhammad Adnan

Pass IQuerableto the Containsfunction instead of list or array. please see the below example

传递IQuerable给Contains函数而不是列表或数组。请看下面的例子

var df_handsets = db.DataFeed_Handsets.Where(m => m.LaunchDate != null).
                  Select(m => m.Name);
var Make = (from m in db.MobilePhones
    where (m.IsDeleted != true || m.IsDeleted == null)
        && df_handsets.Contains(m.Name)
    orderby m.Make
    select new { Value = m.Make, Text = m.Make }).Distinct();

when you pass list or array it is passed in form of parameters and its exceed the counts when the list items count is greater than 2100.

当您传递列表或数组时，它以参数的形式传递，当列表项计数大于 2100 时，它会超过计数。

回答by Zero

You can create your own QueryProvider

您可以创建自己的 QueryProvider

public class QueryProvider : IQueryProvider
{
    // Translates LINQ query to SQL.
    private readonly Func<IQueryable, DbCommand> _translator;

    // Executes the translated SQL and retrieves results.
    private readonly Func<Type, string, object[], IEnumerable> _executor;

    public QueryProvider(
        Func<IQueryable, DbCommand> translator,
        Func<Type, string, object[], IEnumerable> executor)
    {

        this._translator = translator;
        this._executor = executor;
    }

    #region IQueryProvider Members

    public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
    {
        return new Queryable<TElement>(this, expression);
    }

    public IQueryable CreateQuery(Expression expression)
    {
        throw new NotImplementedException();
    }

    public TResult Execute<TResult>(Expression expression)
    {
        bool isCollection = typeof(TResult).IsGenericType &&
            typeof(TResult).GetGenericTypeDefinition() == typeof(IEnumerable<>);
        var itemType = isCollection
            // TResult is an IEnumerable`1 collection.
            ? typeof(TResult).GetGenericArguments().Single()
            // TResult is not an IEnumerable`1 collection, but a single item.
            : typeof(TResult);
        var queryable = Activator.CreateInstance(
            typeof(Queryable<>).MakeGenericType(itemType), this, expression) as IQueryable;

        IEnumerable queryResult;

        // Translates LINQ query to SQL.
        using (var command = this._translator(queryable))
        {
            var parameters = command.Parameters.OfType<DbParameter>()
                .Select(parameter => parameter)
                .ToList();

            var query = command.CommandText;
            var newParameters = GetNewParameterList(ref query, parameters);

            queryResult = _executor(itemType,query,newParameters);
        }

        return isCollection
            ? (TResult)queryResult // Returns an IEnumerable`1 collection.
            : queryResult.OfType<TResult>()
                         .SingleOrDefault(); // Returns a single item.
    }       

    public object Execute(Expression expression)
    {
        throw new NotImplementedException();
    }

    #endregion

     private static object[] GetNewParameterList(ref string query, List<DbParameter> parameters)
    {
        var newParameters = new List<DbParameter>(parameters);

        foreach (var dbParameter in parameters.Where(p => p.DbType == System.Data.DbType.Int32))
        {
            var name = dbParameter.ParameterName;
            var value = dbParameter.Value != null ? dbParameter.Value.ToString() : "NULL";
            var pattern = String.Format("{0}[^0-9]", dbParameter.ParameterName);
            query = Regex.Replace(query, pattern, match => value + match.Value.Replace(name, ""));
            newParameters.Remove(dbParameter);
        }

        for (var i = 0; i < newParameters.Count; i++)
        {
            var parameter = newParameters[i];
            var oldName = parameter.ParameterName;
            var pattern = String.Format("{0}[^0-9]", oldName);
            var newName = "@p" + i;
            query = Regex.Replace(query, pattern, match => newName + match.Value.Replace(oldName, ""));
        }      

        return newParameters.Select(x => x.Value).ToArray();
    }
}


    static void Main(string[] args)
    {
        using (var dc=new DataContext())
        {
            var provider = new QueryProvider(dc.GetCommand, dc.ExecuteQuery);

            var serviceIds = Enumerable.Range(1, 2200).ToArray();

            var tasks = new Queryable<Task>(provider, dc.Tasks).Where(x => serviceIds.Contains(x.ServiceId) && x.CreatorId==37 && x.Creator.Name=="12312").ToArray();

        }

    }