Linq.包含大集合会导致TDS错误 [英] Linq .Contains with large set causes TDS error

问题描述

我稍微简化了一下，因为我正在寻找通用答案.假设我有这样的表格设置:

I've oversimplified this a bit, because I'm looking for a general-purpose answer. Let's say I've got a table setup like this:

Parent
recno    int (unique, pk)
date     datetime
stuff    varchar(50)

Child
parentrecno (int, fk)   --- PK
sequence    (int)       --- PK
data     varchar(50)

在我的C#程序中，我经历了很多麻烦，以查找我感兴趣的父记录并将其填充到列表中. Parent是一个非常大的表，我宁愿不要查询过多的表.所以我松开了钥匙:

And in my C# program I've gone through a lot of hassle to find the Parent records that I'm interested in and stuff them into a list. Parent is a really large table, and I'd rather not query it more than necessary. So I squirrel away the keys:

List<int> recs = (from d in Parent where [.....] select d.recno).ToList();

稍后我可以说，在Linq中，找到相关父母的所有孩子记录:

Later on in Linq I can say, find all of the child records for the associated parents:

var kids = from k in database.Childs
      where recs.Contains(k.parentrecno)
      select new { k };

这很好，直到recs包含2100多个条目.然后，我得到一个TDS RPC错误(参数太多).

This is all great until recs contains more than 2100 entries. Then I get a TDS RPC error (too many parameters).

我看到的方式是:

• 使用SQL直接完成整个过程(不是真的想通过DataReader来解决麻烦，等等).限定记录涉及一个外部系统，所以我也不知道这是否完全可能.另外，我将生成该列表两次-一次，当我需要在.Contains()中使用它时，再一次，将其用于其他目的.

• Do the whole thing in straight up SQL (didn't really want to do go through the hassle with a DataReader, etc...). There was an external system involved in qualifying the records, so I don't know if that's entirely possible either. Plus, I'd be generating that list twice -- once when I need to use it in .Contains(), and again for other purposes.

向上分解列表(记录)，然后分块读取Child.

Break the list (recs) up, and then read Child in chunks.

如果我将其拆成小块，那么我漂亮的Linq一点也不用:

If I break it up in chunks, then my pretty Linq a little farther down won't work at all:

var kids2 = (from kid in paydb.Childs
         where
             recs.Contains(kid.parentrecno)
         group pay by kid.parentrecno into kgroup
         select new { ParentRecNo = kgroup.Key, KidRecords = kgroup })
              .ToDictionary(kx => kx.ParentRecNo);

因为List记录将包含需要分组在一起的内容，但对于Linq查询却必须分开.

Because the List recs will contain things that needed to be grouped together, but necessarily split apart for the Linq query.

推荐答案

我们使用SQL函数，该函数以varchar(max)分隔的值列表作为参数并返回表变量. linq看起来像这样:

We use a SQL function taking a varchar(max) delimited list of values as an argument and returning a table variable. The linq looks like this:

from a in Context.SomeTable    
join nl in Context.ParseDelimited(nodeIdList) on a.NodeId.ToString() equals nl.FieldValue

其中nodeIdList是一个字符串变量，其中包含上一个查询的ID列表.丑陋，但确实能达到2100参数的限制.

where nodeIdList is a string variable containing the list of ids from a previous query. Ugly, but it does get around the 2100 parameter limit.

create function dbo.ParseDelimited(@delimitedList NVARCHAR(MAX)) returns @tblSample table(counter int, fieldValue NVARCHAR(100)) 
WITH SCHEMABINDING
as
begin
  declare @counter    NVARCHAR(  4)
  declare @fieldValue NVARCHAR(100)

  declare @tmpTable table(counter int primary key, fieldValue NVARCHAR(100))

  set @counter = 1

  while charindex(',', @delimitedList) > 0
  begin
    set @fieldValue = ltrim(rtrim(substring(@delimitedList, 1, charIndex(',', @delimitedList)-1)))

    insert into @tmpTable select @counter, @fieldValue

    set @delimitedList = ltrim(rtrim(substring(@delimitedList, (charindex(',', @delimitedList) + 1), len(@delimitedList))))

    set @counter = @counter + 1
  end

  if ltrim(rtrim(@delimitedList)) != ''
  begin
    insert into @tmpTable select @counter, @delimitedList
  end

  insert into @tblSample select counter, fieldValue from @tmpTable

  return
end

这篇关于Linq.包含大集合会导致TDS错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！