SELECT * FROM X WHERE id IN (...) с Dapper ORM

Ответ 1

Dapper поддерживает это напрямую. Например...

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

Ответ 2

Непосредственно из главной страницы проекта GitHub:

Dapper позволяет вам передать IEnumerable и автоматически параметризовать ваш запрос.

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

Будет переведено на:

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

Ответ 3

Если ваше предложение IN слишком велико для обработки MSSQL, вы можете легко использовать параметр TableValueParameter с Dapper.

• Создайте свой TVP-тип в MSSQL:

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

• Создайте DataTable с тем же столбцом (-ами) в качестве TVP и заполните его значениями

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

• Измените запрос Dapper, чтобы сделать INNER JOIN в таблице TVP:

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

• Передайте DataTable в вызове запроса Dapper

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

Это также фантастически работает, когда вы хотите сделать массовое обновление нескольких столбцов - просто создайте TVP и выполните UPDATE с внутренним соединением с TVP.

Ответ 4

Вот, возможно, самый быстрый способ запросить большое количество строк с Dapper, используя список идентификаторов. Я обещаю вам, что это быстрее, чем любой другой способ, о котором вы можете думать (за исключением, возможно, использования TVP, как указано в другом ответе, и который я не тестировал, но я подозреваю, что это может быть медленнее, потому что вам все еще нужно заполнять ТВП). Это планеты быстрее, чем Dapper, используя синтаксис IN и юниверсы быстрее, чем Entity Framework по строкам. И это даже континенты быстрее, чем передача в список элементов VALUES или UNION ALL SELECT. Его можно легко расширить, чтобы использовать многоколоночный ключ, просто добавьте дополнительные столбцы в таблицу DataTable, временную таблицу и условия соединения.

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

Помните, что вам нужно немного узнать о Bulk Inserts. Существуют варианты запуска триггеров (по умолчанию нет), соблюдение ограничений, блокировка таблицы, возможность одновременных вставок и т.д.

Ответ 5

Также убедитесь, что вы не заключаете скобки в строку запроса следующим образом:

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

Это вызвало ошибку синтаксиса SQL с использованием Dapper 1.50.2, исправленную удалением скобок

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

Ответ 6

It is Нет необходимости добавлять () в предложение WHERE, как мы делаем в обычном SQL. Потому что Даппер делает это автоматически для нас. Вот syntax: -

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

var conditions = new { listOfIntegers };

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

Ответ 7

В моем случае я использовал это:

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

моя переменная "ids" во второй строке является IEnumerable строк, также они могут быть целыми числами, которые, как я полагаю.

Ответ 8

По моему опыту, самый удобный способ борьбы с этим - иметь функцию, которая преобразует строку в таблицу значений.

В Интернете есть много функций сплиттера, вы легко найдете их для любого, если ваш вкус SQL.

Затем вы можете сделать...

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

или

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

(или аналогичный)

Ответ 9

Пример для postgres:

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