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

hvvq6cgz 于 2022-12-03 发布在 SQL Server

关注(0)|答案(6)|浏览(215)

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

depts是部门ID的列表（IEnumerable<int>）
这个查询可以正常工作，直到您传递一个大列表（比如大约3000个部门ID）..然后我得到这个错误：
传入得表格格式数据流（TDS）远程过程调用（RPC）协议流不正确.此RPC请求中提供得参数太多.最多为2100.
我将查询更改为：

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

使用IndexOf()修正了这个错误，但是使查询变慢了。有没有其他方法可以解决这个问题？非常感谢。

sql-server

来源：https://stackoverflow.com/questions/656167/hitting-the-2100-parameter-limit-sql-server-when-using-contains

6条答案

按热度按时间

0pizxfdo1#

我的解决方案（Guids是您要筛选的id列表）：

List<MyTestEntity> result = new List<MyTestEntity>();
for(int i = 0; i < Math.Ceiling((double)Guids.Count / 2000); i++)
{
    var nextGuids = Guids.Skip(i * 2000).Take(2000);
    result.AddRange(db.Tests.Where(x => nextGuids.Contains(x.Id)));
}
this.DataContext = result;

赞(0）回复(0）举报 2022-12-03

pcww981p2#

为什么不用sql编写查询并附加实体呢？
我已经有一段时间没有在Linq工作了，但现在开始：

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

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

赞(0）回复(0）举报 2022-12-03

t3irkdon3#

您可能希望查看一下LINQKit project，因为其中包含了一种批处理此类语句的技术来解决此问题。我相信其想法是使用PredicateBuilder将本地集合分解为更小的块，但我没有详细查看该解决方案，因为我一直在寻找一种更自然的方法来处理此问题。
不幸的是，从Microsoft's response to my suggestion到修复此行为，似乎没有为.NET Framework 4.0甚至后续的Service Pack设置解决此问题的计划。
https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=475984
最新消息：
我已经在MSDN论坛上打开了一些讨论，讨论是否要针对LINQ to SQL或ADO.NET Entity Framework修复此问题。请查看这些帖子以了解有关这些主题的更多信息，并查看我使用XML和SQL UDF提出的临时解决方案。

赞(0）回复(0）举报 2022-12-03

bqjvbblv4#

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

Intersect方法
1.基于ID的联接
为了获取不在列表中的值，我使用了Except方法或左连接。

更新

EntityFramework 6.2会成功执行下列查询：

var employeeIDs = Enumerable.Range(3, 5000);
var orders =
    from order in Orders
    where employeeIDs.Contains((int)order.EmployeeID)
    select order;

赞(0）回复(0）举报 2022-12-03

jtw3ybtb5#

你的帖子是不久前的，但也许有人会从中受益。实体框架做了很多查询缓存，每次你发送一个不同的参数计数，它被添加该高速缓存中。使用“包含”调用将导致SQL生成一个子句，如“WHERE x IN（@p1，@p2.... @pn）"，并膨胀EF缓存。
最近我寻找一种新的方法来处理这个问题，我发现你可以创建一个完整的数据表作为一个参数。
首先，您需要创建一个自定义表类型，因此在SQL Server中运行此命令（在我的示例中，我将自定义类型称为“TableId”）：

CREATE TYPE [dbo].[TableId] AS TABLE(
            Id[int] PRIMARY KEY
        )

然后，在C#中，可以创建一个DataTable，并将其加载到与该类型匹配的结构化参数中。可以根据需要添加任意数量的数据行：

DataTable dt = new DataTable();
        dt.Columns.Add("id", typeof(int));

这是要搜索的任意ID列表。您可以根据需要设置列表的大小：

dt.Rows.Add(24262);
        dt.Rows.Add(24267);
        dt.Rows.Add(24264);

使用自定义表类型和数据表创建SqlParameter：

SqlParameter tableParameter = new SqlParameter("@id", SqlDbType.Structured);
        tableParameter.TypeName = "dbo.TableId";
        tableParameter.Value = dt;

然后，您可以从上下文中调用一些SQL语句，将现有的表与表参数中的值连接起来。这将为您提供与ID列表匹配的所有记录：

var items = context.Dailies.FromSqlRaw<Dailies>("SELECT * FROM dbo.Dailies d INNER JOIN @id id ON d.Daily_ID = id.id", tableParameter).AsNoTracking().ToList();

赞(0）回复(0）举报 2022-12-03

suzh9iv86#

在将depts作为参数传递给Linq生成的IN语句之前，你可以将depts列表划分为更小的集合。
Divide a large IEnumerable into smaller IEnumerable of a fix amount of item

赞(0）回复(0）举报 2022-12-03