如何使用Dapper在IN子句中使用超过2100个值?

c# dapper sql-server

我有一个包含ID的List,我想使用Dapper将其插入到临时表中,以避免对“IN”子句中的参数进行SQL限制。

所以目前我的代码看起来像这样:

public IList<int> LoadAnimalTypeIdsFromAnimalIds(IList<int> animalIds)
{
    using (var db = new SqlConnection(this.connectionString))
    {
        return db.Query<int>(
            @"SELECT a.animalID        
            FROM
            dbo.animalTypes [at]
            INNER JOIN animals [a] on a.animalTypeId = at.animalTypeId
            INNER JOIN edibleAnimals e on e.animalID = a.animalID
            WHERE
            at.animalId in @animalIds", new { animalIds }).ToList();
    }
}

我需要解决的问题是,当animalIds列表中有超过2100个ID时,我得到一个SQL错误“传入请求有太多参数。服务器最多支持2100个参数”。

所以现在我想创建一个填充了传递给方法的animalIds的临时表。然后我可以在临时表上加入动物表,避免使用巨大的“IN”子句。

我尝试了各种语法组合,但没有得到任何结论。这就是我现在所处的位置:

public IList<int> LoadAnimalTypeIdsFromAnimalIds(IList<int> animalIds)
{
    using (var db = new SqlConnection(this.connectionString))
    {
        db.Execute(@"SELECT INTO #tempAnmialIds @animalIds");

        return db.Query<int>(
            @"SELECT a.animalID        
            FROM
            dbo.animalTypes [at]
            INNER JOIN animals [a] on a.animalTypeId = at.animalTypeId
            INNER JOIN edibleAnimals e on e.animalID = a.animalID
            INNER JOIN #tempAnmialIds tmp on tmp.animalID = a.animalID).ToList();
    }
}

我无法让SELECT INTO使用ID列表。我是否以错误的方式解决这个问题,也许有更好的方法来避免“IN”条款的限制。

我确实有一个备份解决方案,我可以将传入的animalID列表分成1000个块,但我已经读过大的“IN”子句会影响性能,加入临时表会更有效,这也意味着我不需要额外的“拆分”代码来将ID批量分成1000块。

一般承认的答案

好的,这是你想要的版本。我将此作为单独的答案添加,因为我使用SP / TVP的第一个答案使用了不同的概念。

public IList<int> LoadAnimalTypeIdsFromAnimalIds(IList<int> animalIds)
{
  using (var db = new SqlConnection(this.connectionString))
  {
    // This Open() call is vital! If you don't open the connection, Dapper will
    // open/close it automagically, which means that you'll loose the created
    // temp table directly after the statement completes.
    db.Open();

    // This temp table is created having a primary key. So make sure you don't pass
    // any duplicate IDs
    db.Execute("CREATE TABLE #tempAnimalIds(animalId int not null primary key);");
    while (animalIds.Any())
    {
      // Build the statements to insert the Ids. For this, we need to split animalIDs
      // into chunks of 1000, as this flavour of INSERT INTO is limited to 1000 values
      // at a time.
      var ids2Insert = animalIds.Take(1000);
      animalIds = animalIds.Skip(1000).ToList();

      StringBuilder stmt = new StringBuilder("INSERT INTO #tempAnimalIds VALUES (");
      stmt.Append(string.Join("),(", ids2Insert));
      stmt.Append(");");

      db.Execute(stmt.ToString());
    }

    return db.Query<int>(@"SELECT animalID FROM #tempAnimalIds").ToList();
  }
}

去测试:

var ids = LoadAnimalTypeIdsFromAnimalIds(Enumerable.Range(1, 2500).ToList());

您只需要将select语句修改为最初的语句。由于我的环境中没有所有表,因此我只是从创建的临时表中进行选择,以证明它的工作方式应该如此。

陷阱,见评论:

  • 在开头打开连接,否则在创建表后dapper自动关闭连接后临时表将消失。
  • INSERT INTO这种特殊风格一次限制为1000个值,因此传递的ID需要相应地分成块。
  • 不要传递重复的密钥,因为临时表上的主键不允许这样做。

编辑

似乎Dapper支持基于集合的操作,这也将使这项工作:

public IList<int> LoadAnimalTypeIdsFromAnimalIdsV2(IList<int> animalIds)
{
  // This creates an IEnumerable of an anonymous type containing an Id property. This seems
  // to be necessary to be able to grab the Id by it's name via Dapper.
  var namedIDs = animalIds.Select(i => new {Id = i});
  using (var db = new SqlConnection(this.connectionString))
  {
    // This is vital! If you don't open the connection, Dapper will open/close it
    // automagically, which means that you'll loose the created temp table directly
    // after the statement completes.
    db.Open();

    // This temp table is created having a primary key. So make sure you don't pass
    // any duplicate IDs
    db.Execute("CREATE TABLE #tempAnimalIds(animalId int not null primary key);");

    // Using one of Dapper's convenient features, the INSERT becomes:
    db.Execute("INSERT INTO #tempAnimalIds VALUES(@Id);", namedIDs);

    return db.Query<int>(@"SELECT animalID FROM #tempAnimalIds").ToList();
  }
}

我不知道与先前版本相比这将有多好(即2500个单个插入而不是三个插入,每个插入1000,1000,500个值)。但该文档表明,如果与async,MARS和Pipelining一起使用它会表现得更好。


热门答案

在您的示例中,我看不到的是您的animalIds列表实际上是如何传递给要插入#tempAnimalIDs表的查询的。

有一种方法可以在不使用临时表的情况下使用带有表值参数的存储过程。

SQL:

CREATE TYPE [dbo].[udtKeys] AS TABLE([i] [int] NOT NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[myProc](@data as dbo.udtKeys readonly)AS
BEGIN
    select i from @data;
END
GO

这将创建一个名为udtKeys的用户定义表类型, udtKeys包含一个名为i int列,以及一个需要该类型参数的存储过程。 proc除了选择您传递的ID之外别无其他,但您当然可以将其他表连接到它。有关语法的提示, 请参见此处

C#:

var dataTable = new DataTable();
dataTable.Columns.Add("i", typeof(int));
foreach (var animalId in animalIds)
    dataTable.Rows.Add(animalId);
using(SqlConnection conn = new SqlConnection("connectionString goes here"))
{
    var r=conn.Query("myProc", new {data=dataTable},commandType: CommandType.StoredProcedure);
    // r contains your results
}

过程中的参数通过传递DataTable来填充,并且DataTable的结构必须与您创建的表类型相匹配。

如果您确实需要传递2100个以上的值,则可能需要考虑索引表类型以提高性能。如果你没有传递任何重复的键,你实际上可以给它一个主键,如下所示:

CREATE TYPE [dbo].[udtKeys] AS TABLE(
    [i] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
        [i] ASC
    )WITH (IGNORE_DUP_KEY = OFF)
)
GO

您可能还需要将类型的执行权限分配给您执行此操作的数据库用户,如下所示:

GRANT EXEC ON TYPE::[dbo].[udtKeys] TO [User]
GO

另见此处此处



许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow