¿Cómo puedo usar más de 2100 valores en una cláusula IN usando Dapper?

c# dapper sql-server

Pregunta

Tengo una lista que contiene identificadores que quiero insertar en una tabla temporal usando Dapper para evitar el límite SQL en los parámetros en la cláusula 'IN'.

Así que actualmente mi código se ve así:

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

El problema que necesito resolver es que cuando hay más de 2100 ids en la lista de animales, recibo un error de SQL "La solicitud entrante tiene demasiados parámetros. El servidor admite un máximo de 2100 parámetros".

Así que ahora me gustaría crear una tabla temporal poblada con los animalsIds pasados ​​al método. Entonces puedo unirme a la mesa de los animales en la mesa temporal y evitar tener una gran cláusula "IN".

He intentado varias combinaciones de sintaxis pero no he llegado a ninguna parte. Aquí es donde estoy ahora:

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

No puedo hacer que SELECT INTO trabaje con la lista de ID. Estoy haciendo esto de la manera incorrecta, tal vez haya una mejor manera de evitar el límite de la cláusula "IN".

Tengo una solución de respaldo porque puedo dividir la lista entrante de animales ID en bloques de 1000, pero he leído que la cláusula grande "IN" sufre un golpe de rendimiento y unirse a una tabla temporal será más eficiente y también significa que no necesita un código adicional de "división" para agrupar los ID en bloques de 1000.

Respuesta aceptada

Ok, esta es la versión que quieres. Estoy agregando esto como una respuesta separada, ya que mi primera respuesta con SP / TVP utiliza un concepto diferente.

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

Probar:

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

Solo necesita modificar su declaración seleccionada a lo que originalmente era. Como no tengo todas las tablas en mi entorno, acabo de seleccionar de la tabla temporal creada para demostrar que funciona de la manera que debería.

Trampas, ver comentarios:

  • Abra la conexión al principio, de lo contrario, la tabla temporal desaparecerá después de que dapper cierre automáticamente la conexión justo después de crear la tabla.
  • Este sabor particular de INSERT INTO está limitado a 1000 valores a la vez, por lo que los ID pasados ​​deben dividirse en trozos en consecuencia.
  • No pase claves duplicadas, ya que la clave principal en la tabla temporal no permitirá eso.

Editar

Parece que Dapper admite una operación basada en conjuntos que hará que esto también funcione:

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

No sé qué tan bien funcionará en comparación con la versión anterior (es decir, 2500 inserciones individuales en lugar de tres inserciones con 1000, 1000, 500 valores cada una). Pero el documento sugiere que funciona mejor si se usa junto con async, MARS y Pipelining.


Respuesta popular

En su ejemplo, lo que no puedo ver es cómo su lista de animalIds se pasa realmente a la consulta para insertarla en la tabla #tempAnimalIDs .

Hay una forma de hacerlo sin usar una tabla temporal, utilizando un procedimiento almacenado con un parámetro de valor de tabla.

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

Esto creará un tipo de tabla definido por el usuario llamado udtKeys que contiene solo una columna int llamada i , y un procedimiento almacenado que espera un parámetro de ese tipo. El proceso no hace más que seleccionar los ID que aprobó, pero puede, por supuesto, unir otras tablas. Para una pista sobre la sintaxis, mira aquí .

DO#:

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
}

El parámetro dentro del procedimiento se completa pasando una DataTable, y esa estructura de DataTable debe coincidir con la del tipo de tabla que usted creó.

Si realmente necesita pasar más de 2100 valores, es posible que desee considerar la indexación de su tipo de tabla para aumentar el rendimiento. En realidad, puede darle una clave principal si no pasa ninguna clave duplicada, como esta:

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

Es posible que también deba asignar permisos de ejecución para el tipo al usuario de la base de datos con el que lo ejecuta, como se muestra a continuación:

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

Ver también aquí y aquí .



Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué
Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué