title: How can I use more than 2100 values in an IN clause using Dapper?
body: <p>I have a List containing ids that I want to insert into a temp table using Dapper in order to avoid the SQL limit on parameters in the 'IN' clause.</p>
  
  <p>So currently my code looks like this:</p>
  
  <pre><code>public IList&lt;int&gt; LoadAnimalTypeIdsFromAnimalIds(IList&lt;int&gt; animalIds)
  {
      using (var db = new SqlConnection(this.connectionString))
      {
          return db.Query&lt;int&gt;(
              @"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();
      }
  }
  </code></pre>
  
  <p>The problem I need to solve is that when there are more than 2100 ids in the animalIds list then I get a SQL error "The incoming request has too many parameters. The server supports a maximum of 2100 parameters".</p>
  
  <p>So now I would like to create a temp table populated with the animalIds passed into the method. Then I can join the animals table on the temp table and avoid having a huge "IN" clause.</p>
  
  <p>I have tried various combinations of syntax but not got anywhere.
  This is where I am now:</p>
  
  <pre><code>public IList&lt;int&gt; LoadAnimalTypeIdsFromAnimalIds(IList&lt;int&gt; animalIds)
  {
      using (var db = new SqlConnection(this.connectionString))
      {
          db.Execute(@"SELECT INTO #tempAnmialIds @animalIds");
  
          return db.Query&lt;int&gt;(
              @"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();
      }
  }
  </code></pre>
  
  <p>I can't get the SELECT INTO working with the list of IDs. Am I going about this the wrong way maybe there is a better way to avoid the "IN" clause limit.</p>
  
  <p>I do have a backup solution in that I can split the incoming list of animalIDs into blocks of 1000 but I've read that the large "IN" clause sufferes a performance hit and joining a temp table will be more efficient and it also means I don;t need extra 'splitting' code to batch up the ids in to blocks of 1000.</p>