sql查询中的最大参数数

问题描述

一段时间以来，我一直在尝试 LINQ.在我的代码中枚举集合并更改其某些属性的典型方法如下所示:

I do experiment with LINQ since some time. Typical method to enumerate through a collection and change some of its properties in my code would look like:

ATDataContext dc = new ATDataContext(Settings.connection_string);

int[] col = ListViewClass.getListViewSelectedPositionTags(listView);

try
{
    foreach (var item in col)
    {
        var ctx = (from r in dc.MailingLists
                   where r.ID == item
                   select r).Single();

        ctx.Excluded = 'Y';
        ctx.ExcludedComments = reason;
    }

    dc.SubmitChanges();
}

有一段时间，有人建议这样做……似乎是更聪明的方法:

Some time a have got an advice to do this by... seems like much smarter way:

var ctx = from r in dc.MailingLists
    where col.Contains(r.ID)
    select r;

foreach (var item in ctx)
{
    item.Excluded = 'Y';
    item.ExcludedComments = reason;
}

dc.SubmitChanges();

它在很多层面上都有意义，我喜欢这个解决方案.它比第一个更智能、更快.

Iit makes sense on so many levels and I love this solution. It’s smart and faster than the first one.

我在生产环境中使用这个解决方案有一段时间了.

I have used this solution in a production environment for some time.

几周后，当我搜索应用程序日志文件并看到这个时，我感到惊讶的是什么:

What was my surprise after few weeks when searching an application log files and see this:

传入的表格数据流 (TDS) 远程过程调用 (RPC) 协议流不正确.此 RCP 请求中提供的参数过多.最大值为 2100."

LINQ to SQL 将 where col.Contains(r.ID) 转换为 IN 子句，如下所示:
在哪里 ID (@p1, @p1, @p2 ... )

The LINQ to SQL converts where col.Contains(r.ID) to INclause looking something like:
WHERE ID IN (@p1, @p1, @p2 … )

col 集合达到(在我的例子中)超过 2100 个元素并且查询未能执行.我对这个问题做了一些研究，结果是:

The col collection reached (in my case) more than 2100 elements and the query failed to perform. I have done some research on the problem and what I ended up is:

…… sql 查询中的最大参数数为 2100.还有更多限制，例如整个查询字符串不能超过 8044 个字符."

我非常喜欢第二种解决方案.我对 SQL Server 的这些硬编码限制感到非常失望.

I have loved the second solution so much. I am so disappointed with these hard-coded limitations of the SQL Server.

我错过了什么吗?我能做些什么才能使用where col.Contains(r.ID)"版本?

Did I miss something? Is there anything I can do to be able to use the "where col.Contains(r.ID)" version?

问候马里乌斯

ps.(我使用 Win XP、C# 和 LINQ 和 SQL 2005 Express).

ps. (I use Win XP, C# with LINQ and SQL 2005 Express).

推荐答案

限制 是硬编码的:

• 每个存储过程的参数 2,100
• 每个用户定义函数的参数 2,100

我之前写了一些代码，将 Contains 查询分成批次并合并结果... 更多信息请看这里.

I wrote some code before that split the Contains query into batches and combined the results... see here for more.

这篇关于sql查询中的最大参数数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！