In Dapper, how can I send more than 2100 parameters with an IN clause?

c# dapper sql-server

Question

I want to use Dapper to insert a List of ids into a temporary database in order to get around the SQL restriction on arguments in the "IN" clause.

My code presently looks like this:

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();
    }
}

When there are more than 2100 ids in the animalIds list, I see a SQL issue, which I need to fix "There are too many arguments in the incoming request. The server can accommodate up to 2100 parameters ".

Therefore, I want to construct a temporary table and fill it with the animalIds supplied to the procedure. I can avoid having a lengthy "IN" clause by joining the animals table to the temporary table at that point.

I've experimented with several syntax combinations without success. I'm at this moment:

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();
    }
}

With the list of IDs, I am unable to make the SELECT INTO function. Am I approaching this incorrectly? Perhaps there is an easier method to get around the "IN" clause restriction.

I do have a fallback option in that I can batch up the incoming list of animalIDs into blocks of 1000, but I've read that doing so will cause the large "IN" clause to perform poorly. Instead, joining a temporary table will be more effective, and it will also eliminate the need for additional "splitting" code.

1
6
9/21/2016 7:00:22 AM

Accepted Answer

Okay, here is the desired version. Since my initial solution, which also uses SP/TVP, makes use of a distinct idea, I'm adding this as a second response.

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();
  }
}

As a test:

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

Simply restore the original choose statement to your code. Since I don't have access to all of your tables, I merely made a selection from the newly generated temporary table to demonstrate that it functions as it should.

Risks, see remarks:

  • Start by opening the connection; otherwise, dapper would immediately terminate it after constructing the table, erasing the temporary table.
  • The provided IDs must be divided into appropriate chunks since this specific flavor of INSERT INTO can only hold 1000 data at once.
  • Passing duplicate keys is not permitted since the temporary table's main key prevents it.

Edit

It seems Dapper supports a set-based operation, so this will also function:

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();
  }
}

In comparison to the prior version, I'm not sure how well this will function (ie. 2500 single inserts instead of three inserts with 1000, 1000, 500 values each). But according to the document, it works best when combined with async, MARS, and pipelining.

6
8/3/2017 4:06:48 PM

Popular Answer

What I don't see in your example is how your list ofanimalIds actually provided to the query, which will be placed into the#tempAnimalIDs table.

There is a workaround that involves using a stored procedure with a table value argument in place of a temporary table.

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

This will create a custom table type with the nameudtKeys It only has one named integer columni , a stored method that anticipates a parameter of that kind, and more. You may connect additional tables to the proc, but it just selects the IDs that you give it. Please use the following syntax as a hint: see this.

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
}

A DataTable is sent into the method to fill a parameter, and the structure of that DataTable must match the structure of the table type you constructed.

If passing more than 2100 data is really necessary, you may want to think about indexing your table type to improve speed. If you don't send any duplicate keys, you may really provide it with a main key in the following way:

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

Additionally, you may need to provide the database user you're using to perform this command execute permissions for the type, as follows:

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

Check out here and here as well.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow