ti-enxame.com

SELECT * FROM X ONDE ID IN (...) com Dapper ORM

Qual é a melhor maneira de gravar uma consulta com a cláusula IN usando Dapper ORM quando a lista de valores para a cláusula IN vem da lógica de negócios? Por exemplo, digamos que eu tenha uma consulta:

SELECT * 
  FROM SomeTable 
 WHERE id IN (commaSeparatedListOfIDs)

O commaSeparatedListOfIDs está sendo passado da lógica de negócios e pode ser qualquer tipo de IEnumerable(of Integer). Como eu poderia construir uma consulta neste caso? Eu tenho que fazer o que eu tenho feito até agora, que é basicamente concatenação de strings ou existe algum tipo de técnica avançada de mapeamento de parâmetros que eu não conheço?

188
Marko

Dapper suporta isso diretamente. Por exemplo...

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

Diretamente do homepage do projeto GitHub :

O Dapper permite que você passe em IEnumerable e parametrize automaticamente sua 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á traduzido para:

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
54
Factor Mystic

Se sua cláusula IN for muito grande para o MSSQL manipular, você pode usar um TableValueParameter com o Dapper facilmente.

  1. Crie seu tipo de TVP no MSSQL:

    CREATE TYPE [dbo].[MyTVP] AS TABLE([ProviderId] [int] NOT NULL)
    
  2. Crie um DataTable com a (s) mesma (s) coluna (s) que o TVP e preencha-o com valores

    var tvpTable = new DataTable();
    tvpTable.Columns.Add(new DataColumn("ProviderId", typeof(int)));
    // fill the data table however you wish
    
  3. Modifique sua consulta Dapper para fazer um INNER JOIN na tabela TVP:

    var query = @"SELECT * FROM Providers P
        INNER JOIN @tvp t ON p.ProviderId = t.ProviderId";
    
  4. Passe a DataTable na sua chamada de consulta Dapper

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

Isso também funciona fantasticamente quando você quer fazer uma atualização em massa de múltiplas colunas - simplesmente construa um TVP e faça um UPDATE com uma junção interna ao TVP.

32
Mr. T

Aqui está possivelmente a maneira mais rápida de consultar um grande número de linhas com o Dapper usando uma lista de IDs. Eu prometo a você que isso é mais rápido do que quase qualquer outra maneira que você possa pensar (com a possível exceção de usar um TVP como dado em outra resposta, e que eu não testei, mas eu suspeito que pode ser mais lento porque você ainda tem que preencher o TVP). É planetas mais rápido que o Dapper usando a sintaxe IN e niverses mais rápido que o Entity Framework linha por linha. E é ainda mais rápido que passar em uma lista de itens VALUES ou UNION ALL SELECT. Ele pode ser facilmente estendido para usar uma chave com várias colunas, basta adicionar as colunas extras ao DataTable, a tabela temporária e as condições de associação.

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

Esteja ciente de que você precisa aprender um pouco sobre insumos em massa. Há opções sobre acionadores de disparo (o padrão é não), respeitando restrições, bloqueando a tabela, permitindo inserções simultâneas e assim por diante.

10
ErikE

Além disso, certifique-se de não envolver parênteses em torno de sua string de consulta da seguinte forma:

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

Eu tive essa causa um erro de sintaxe SQL usando o Dapper 1.50.2, corrigido removendo parênteses

SELECT Name from [USER] WHERE [UserId] in @ids
6
Brian Ogden

É não é necessário para adicionar () na cláusula WHERE como fazemos em um SQL normal. Porque Dapper faz isso automaticamente para nós. Aqui está o syntax: -

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

var conditions = new { listOfIntegers };

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

No meu caso eu usei isso:

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

minha variável "ids" na segunda linha é um IEnumerable de seqüências de caracteres, também eles podem ser números inteiros, eu acho.

3
Cesar

Na minha experiência, a maneira mais amigável de lidar com isso é ter uma função que converte uma string em uma tabela de valores.

Existem muitas funções de divisão disponíveis na web, você encontrará facilmente uma para o que quer que seja, se o seu sabor de SQL.

Você pode então fazer ...

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

Ou

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

(Ou similar)

2
MatBailie