Linq .Contains with large set 导致 TDS 错误

Question

我将其过度简化了一点，因为我正在寻找一个通用的答案。假设我有一个这样的表格设置:

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

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

在我的 C# 程序中，我经历了很多麻烦才找到我感兴趣的父记录并将它们塞入列表。 Parent 是一个非常大的表，我不想不必要地查询它。所以我把 key 藏起来了:

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

稍后在 Linq 中我可以说，找到关联 parent 的所有 child 记录:

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

这一切都很好，直到 recs 包含超过 2100 个条目。然后我收到 TDS RPC 错误(参数太多)。

我的看法是:

• 用直接的 SQL 完成所有事情(我真的不想经历 DataReader 等的麻烦...)。有一个外部系统涉及对记录进行资格审查，所以我也不知道这是否完全可行。另外，我会生成该列表两次——一次是当我需要在 .Contains() 中使用它时，另一次是为了其他目的。

• 分解列表(记录)，然后分块阅读 Child。

如果我把它分成几 block ，那么我漂亮的 Linq 再往下一点就根本无法工作了:

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 recs 将包含需要组合在一起的东西，但对于 Linq 查询必须分开。

Answer 1

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

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

其中 nodeIdList 是一个字符串变量，其中包含来自先前查询的 ID 列表。丑陋，但它确实绕过了 2100 个参数限制。

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