使用Contains()时达到2100参数限制(SQL Server)

from f in CUSTOMERS
where depts.Contains(f.DEPT_ID)
select f.NAME

depts is a list (IEnumerable<int>) of department ids

depts是一个部门id的列表(IEnumerable )

This query works fine until you pass a large list (say around 3000 dept ids) .. then I get this error:

这个查询可以正常工作，直到您传递一个大型列表(比如大约3000个dept id)。然后我得到这个错误:

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.

传入的表格数据流(TDS)远程过程调用(RPC)协议流不正确。在这个RPC请求中提供了太多的参数。最大值是2100。

I changed my query to:

我将查询改为:

var dept_ids = string.Join(" ", depts.ToStringArray());
from f in CUSTOMERS
where dept_ids.IndexOf(Convert.ToString(f.DEPT_id)) != -1
select f.NAME

using IndexOf() fixed the error but made the query slow. Is there any other way to solve this? thanks so much.

使用IndexOf()修复了错误，但使查询速度变慢。还有别的办法吗?非常感谢。

5 个解决方案

#1

My solution (Guides -> List of Guid):

我的解决方案(指南- Guid的>列表):

List<tstTest> tsts = new List<tstTest>();
for(int i = 0; i < Math.Ceiling((double)Guides.Count / 2000); i++)
{
    tsts.AddRange(dc.tstTests.Where(x => Guides.Skip(i * 2000).Take(2000).Contains(x.tstGuid)));
}
this.DataContext = tsts;

#2

Why not write the query in sql and attach your entity?

为什么不使用sql编写查询并附加实体?

It's been awhile since I worked in Linq, but here goes:

我在Linq工作已经有一段时间了，但现在是这样的:

IQuery q = Session.CreateQuery(@"
         select * 
         from customerTable f
         where f.DEPT_id in (" + string.Join(",", depts.ToStringArray()) + ")");
q.AttachEntity(CUSTOMER);

Of course, you will need to protect against injection, but that shouldn't be too hard.

当然，您将需要防止注射，但这不应该太难。

#3

You will want to check out the LINQKit project since within there somewhere is a technique for batching up such statements to solve this issue. I believe the idea is to use the PredicateBuilder to break the local collection into smaller chuncks but I haven't reviewed the solution in detail because I've instead been looking for a more natural way to handle this.

您将希望检查LINQKit项目，因为在某个地方有一种技术可以对此类语句进行批处理以解决这个问题。我认为这个想法是使用PredicateBuilder来将本地集合分解为更小的块，但是我还没有详细地检查解决方案，因为我一直在寻找更自然的方法来处理这个问题。

Unfortunately it appears from Microsoft's response to my suggestion to fix this behavior that there are no plans set to have this addressed for .NET Framework 4.0 or even subsequent service packs.

不幸的是，从微软对我修复这种行为的建议的反应来看，目前还没有计划在。net Framework 4.0甚至后续的服务包中解决这个问题。

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=475984

UPDATE:

更新:

I've opened up some discussion regarding whether this was going to be fixed for LINQ to SQL or the ADO.NET Entity Framework on the MSDN forums. Please see these posts for more information regarding these topics and to see the temporary workaround that I've come up with using XML and a SQL UDF.

我已经展开了一些讨论，讨论这是否将由LINQ修复到SQL或ADO。在MSDN论坛上的网络实体框架。有关这些主题的更多信息，请参阅这些文章，并了解我使用XML和SQL UDF提出的临时解决方案。

#4

I had similar problem, and I got two ways to fix it.

我也遇到过类似的问题，我有两种方法来解决它。

Intersect method
相交的方法
join on IDs
在IDs加入

To get values that are NOT in list, I used Except method OR left join.

为了获取不在列表中的值，我使用了除方法或左连接。

#5

You could always partition your list of depts into smaller sets before you pass them as parameters to the IN statement generated by Linq. See here:

您可以在将深度列表作为参数传递给Linq生成的IN语句之前，将它们划分为更小的集合。在这里看到的:

Divide a large IEnumerable into smaller IEnumerable of a fix amount of item

将一个大的IEnumerable分割为较小的IEnumerable，表示固定数量的项目

#1