我正在尝试创建一个通用函数来帮助我从本地列表中使用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 SelectByParameterList(Table items,
IEnumerable parameterList, Expression> 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查询(它在非泛型形式下工作正常).
我知道编译Expression只会让我回到原点(将代理作为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)
Marc Gravell..
43
我想出了一种将查询分块的方法 - 即你给它4000个值,所以它可以做4个请求,每个1000; 完整的Northwind示例.请注意,这可能不适用于Entity Framework,因为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 InRange(
this IQueryable source,
Expression> selector,
int blockSize,
IEnumerable 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>(
predicate, row);
foreach(T record in source.Where(lambda)) {
yield return record;
}
}
}
public static IEnumerable GetBlocks(
this IEnumerable source, int blockSize) {
List list = new List(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();
}
}
}
}
kwcto..
8
最简单的方法:使用LINQKit(免费,非限制性许可)
工作版代码:
public static IEnumerable SelectByParameterList(this Table items, IEnumerable parameterList, Expression> 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 results = Refills.SelectByParameterList(local_refill_ids, r => r.Id, 10); //runs 2 SQL queries with 10 parameters each
再次感谢你的帮助!
1> Marc Gravell..：
我想出了一种将查询分块的方法 - 即你给它4000个值,所以它可以做4个请求,每个1000; 完整的Northwind示例.请注意,这可能不适用于Entity Framework,因为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 InRange(
this IQueryable source,
Expression> selector,
int blockSize,
IEnumerable 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>(
predicate, row);
foreach(T record in source.Where(lambda)) {
yield return record;
}
}
}
public static IEnumerable GetBlocks(
this IEnumerable source, int blockSize) {
List list = new List(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();
}
}
}
}
这将处理`queryable.Where(o => values.Contains(o.propertyToTest))`的情况,将其替换为`queryable.InRange(o => o.propertyToTest,blockSize,values)`(如果我理解正确的话) ),但我正在查看2100参数限制的类似溢出,例如`queryable.Where(o =>!values.Contains(o.propertyToTest))`.我正在尝试修改InRange()以获取NotInRange()等效项,我不知道如何进行布尔否定.我的想法是在'foreach(源记录中的T记录(λ))行？
实际上,在经过多次狩猎之后,我想我已经找到了所需要的东西,从你一个月前给出的答案中得到了适当的答案:http://stackoverflow.com/questions/457316/combining-two-expressions-expressionfunct- bool#457328,ref"这也适用于否定单一操作:"
2> kwcto..：
最简单的方法:使用LINQKit(免费,非限制性许可)
工作版代码:
public static IEnumerable SelectByParameterList(this Table items, IEnumerable parameterList, Expression> 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 results = Refills.SelectByParameterList(local_refill_ids, r => r.Id, 10); //runs 2 SQL queries with 10 parameters each
再次感谢你的帮助!
您通常希望尽可能少的块.主要问题是SQL Server每个查询/命令只允许2100个参数.我通常会使用2000的blockSize.
京公网安备 11010802040832号