如何使用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