.net - net - SELECCIONAR*DESDE X DONDE id IN(...) con Dapper ORM

dapper.net core

Question

¿Cuál es la mejor manera de escribir una consulta con la cláusula IN utilizando Dapper ORM cuando la lista de valores para la cláusula IN proviene de lógica empresarial? Por ejemplo, digamos que tengo una consulta:

SELECT * 
  FROM SomeTable 
 WHERE id IN (commaSeparatedListOfIDs)

El commaSeparatedListOfIDs se transfiere desde la lógica de negocio y puede ser cualquier tipo de IEnumerable(of Integer) . ¿Cómo construiría una consulta en este caso? ¿Tengo que hacer lo que he estado haciendo hasta ahora, que es básicamente concatenación de cadenas o hay algún tipo de técnica avanzada de asignación de parámetros de la que no tengo conocimiento?

Answer 1

Dapper lo admite directamente. Por ejemplo...

string sql = "SELECT * FROM SomeTable WHERE id IN @ids"
var results = conn.Query(sql, new { ids = new[] { 1, 2, 3, 4, 5 }});

Answer 2

Directamente desde la página de inicio del proyecto GitHub :

Dapper le permite pasar IEnumerable y automáticamente parametrizará su consulta.

connection.Query<int>(
    @"select * 
      from (select 1 as Id union all select 2 union all select 3) as X 
      where Id in @Ids", 
    new { Ids = new int[] { 1, 2, 3 });

Será traducido a:

select * 
from (select 1 as Id union all select 2 union all select 3) as X 
where Id in (@Ids1, @Ids2, @Ids3)

// @Ids1 = 1 , @Ids2 = 2 , @Ids2 = 3

Answer 3

En mi caso, he usado esto:

var query = "select * from table where Id IN @Ids";
var result = conn.Query<MyEntity>(query, new { Ids = ids });

mis "ids" variables en la segunda línea son un IEnumerable de cadenas, también pueden ser enteros, supongo.

Answer 4

En mi experiencia, la manera más amigable de lidiar con esto es tener una función que convierta una cadena en una tabla de valores.

Hay muchas funciones de divisor disponibles en la web, encontrará fácilmente una para cualquier cosa si su sabor de SQL.

Entonces puedes hacer ...

SELECT * FROM table WHERE id IN (SELECT id FROM split(@list_of_ids))

O

SELECT * FROM table INNER JOIN (SELECT id FROM split(@list_of_ids)) AS list ON list.id = table.id

(O similar)

Answer 5

Esta es posiblemente la forma más rápida de consultar una gran cantidad de filas con Dapper utilizando una lista de ID. Te prometo que esto es más rápido que casi cualquier otra forma que se te ocurra (con la posible excepción de usar un TVP como se da en otra respuesta, y que no he probado, pero sospecho que puede ser más lento porque todavía tienes que poblar el TVP). Son planetas más rápidos que Dapper utilizando la sintaxis IN y los universos más rápido que Entity Framework fila por fila. Y es incluso continentes más rápidos que pasar en una lista de VALUES o elementos UNION ALL SELECT . Se puede ampliar fácilmente para usar una clave de múltiples columnas, simplemente agregue las columnas adicionales a la tabla de datos, la tabla temporal y las condiciones de combinación.

public IReadOnlyCollection<Item> GetItemsByItemIds(IEnumerable<int> items) {
   var itemList = new HashSet(items);
   if (itemList.Count == 0) { return Enumerable.Empty<Item>().ToList().AsReadOnly(); }

   var itemDataTable = new DataTable();
   itemDataTable.Columns.Add("ItemId", typeof(int));
   itemList.ForEach(itemid => itemDataTable.Rows.Add(itemid));

   using (SqlConnection conn = GetConnection()) // however you get a connection
   using (var transaction = conn.BeginTransaction()) {
      conn.Execute(
         "CREATE TABLE #Items (ItemId int NOT NULL PRIMARY KEY CLUSTERED);",
         transaction: transaction
      );

      new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction) {
         DestinationTableName = "#Items",
         BulkCopyTimeout = 3600 // ridiculously large
      }
         .WriteToServer(itemDataTable);
      var result = conn
         .Query<Item>(@"
            SELECT i.ItemId, i.ItemName
            FROM #Items x INNER JOIN dbo.Items i ON x.ItemId = i.ItemId
            DROP TABLE #Items;",
            transaction: transaction,
            commandTimeout: 3600
         )
         .ToList()
         .AsReadOnly();
      transaction.Rollback(); // Or commit if you like
      return result;
   }
}

Tenga en cuenta que necesita aprender un poco sobre Inserciones masivas. Hay opciones sobre disparadores de disparo (el valor predeterminado es no), respetando las restricciones, bloqueando la tabla, permitiendo inserciones concurrentes, y así sucesivamente.

Answer 6

Sé que esta no es una pregunta reciente, no es necesario agregar () en la cláusula Sql''s where, que solemos hacer, porque Dapper lo hace automáticamente. Aquí está la sintaxis que puede ayudar a alguien.

const string SQL = "SELECT IntegerColumn, StringColumn FROM SomeTable WHERE IntegerColumn IN @EnumeratedList";

var conditions = new { listOfIntegers };

var results = connection.Query(SQL, conditions);

Answer 7

Si su cláusula IN es demasiado grande para que la maneje MSSQL, puede usar TableValueParameter con Dapper con bastante facilidad.

1. Crea tu tipo de TVP en MSSQL:

   CREATE TYPE [dbo].[MyTVP] AS TABLE([ProviderId] [int] NOT NULL)

2. Cree una DataTable con la (s) misma (s) columna (s) que el TVP y rellene con valores

   var tvpTable = new DataTable();
   tvpTable.Columns.Add(new DataColumn("ProviderId", typeof(int)));
   // fill the data table however you wish

3. Modifique su consulta Dapper para hacer una INNER JOIN en la tabla TVP:

   var query = @"SELECT * FROM Providers P
       INNER JOIN @tvp t ON p.ProviderId = t.ProviderId";

4. Pase la DataTable en su llamada de consulta Dapper

   sqlConn.Query(query, new {tvp = tvpTable.AsTableValuedParameter("dbo.MyTVP")});

Esto también funciona fantásticamente cuando quieres hacer una actualización masiva de múltiples columnas: simplemente crea un TVP y realiza una UPDATE con una combinación interna al TVP.

Answer 8

También asegúrese de no colocar paréntesis alrededor de la cadena de consulta de la siguiente manera:

SELECT Name from [USER] WHERE [UserId] in (@ids)

Tuve esto causar un error de sintaxis SQL utilizando Dapper 1.50.2, corregido al eliminar paréntesis

SELECT Name from [USER] WHERE [UserId] in @ids