ВЫБЕРИТЕ * ОТ X ГДЕ id IN (...) с помощью Dapper ORM

Question

ВЫБЕРИТЕ * ОТ X ГДЕ id IN (...) с помощью Dapper ORM

Каков наилучший способ написать запрос с предложением IN, используя Dapper ORM, если список значений для предложения IN исходит из бизнес-логики? Например, скажем, у меня есть запрос:

SELECT * 
  FROM SomeTable 
 WHERE id IN (commaSeparatedListOfIDs)

commaSeparatedListOfIDs передается из бизнес-логики, и это может быть любой тип IEnumerable(of Integer), Как бы я построить запрос в этом случае? Нужно ли мне делать то, что я делал до сих пор, это в основном конкатенация строк или есть какая-то продвинутая техника отображения параметров, о которой я не знаю?

337

sql .net dapper

Источник

user758093 05 дек '11 в 15:59

12 ответов

Решение

Непосредственно с домашней страницы проекта 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

82

Источник

user1569 05 дек '11 в 17:04

Если твой 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.

57

Источник

user2078975 30 мар '17 в 21:22

Пример для postgres:

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

55

Источник

user305285 18 окт '19 в 12:51

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

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

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

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

28

Источник

user1258525 05 мар '18 в 20:01

Вот, пожалуй, самый быстрый способ запросить большое количество строк с помощью Dapper, используя список идентификаторов. Я обещаю вам, что это быстрее, чем любой другой способ, которым вы можете придумать (за возможным исключением использования TVP, как указано в другом ответе, и которое я не проверял, но я подозреваю, что может быть медленнее, потому что вам все еще нужно заполнить ТВП). Это планеты быстрее, чем Даппер, использующий 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;
   }
}

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

20

Источник

user57611 25 янв '17 в 23:54

Не надо добавлять () в предложении 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);

15

Источник

user5319580 10 апр '18 в 08:20

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

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

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

4

Источник

user3877076 15 июл '15 в 12:49

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

В Интернете доступно множество функций разветвителя, вы легко найдете их для любого вида 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

(Или похожие)

3

Источник

user53341 05 дек '11 в 16:05

      SELECT * FROM tbl WHERE col IN @val

Я также заметил, что этот синтаксис не работает с byte[]. Dapper принимает только последний элемент, и параметр должен быть заключен в круглые скобки. Однако, когда я меняю тип на int[]все работает.

0

Источник

user13179254 07 дек '21 в 09:13

Этот пример работает для PostgreSQL :

      using NpgsqlConnection connection = new(YourConnectionString);
string sql = "SELECT * FROM TableName ({string.Join(", ", YourList.Select(x => x.Id))})";

List<ModelVm> result = (await (connection.QueryAsync<ModelVm>(sql))).ToList();

Также для удаления вы можете использовать его так:

      using NpgsqlConnection connection = new(YourConnectionString);

var affected = await connection.ExecuteAsync($"DELETE FROM TableName WHERE id in ({string.Join(", ", YourList.Select(x => x.Id))})");

if (affected != 0)
    _logger.LogInformation($"TableName with ids {string.Join(", ", YourList.Select(x => x.Id))} are successfully deleted.");

-1

Источник

user7657155 09 окт '21 в 11:07

Что касается PostgreSQL, я обнаружил, что интерполяция строк мне полезна в .NET.

Пример:

      var ids = new int[] { 1, 2, 3 };
var query = "SELECT name FROM table WHERE id IN ({string.Join(",", ids)})";
using var connection = _dapperContext.CreateConnection();
var results = await connection.QueryAsync<ResultModel>(query);

-2

Источник

user8779543 20 мар '23 в 09:47

Другие вопросы по тегам sql .net dapper

user55847 05 дек '11 в 16:20 2011-12-05 16:20 · Accepted Answer · 2011-12-05 16:20

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

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

531

Источник

user55847 05 дек '11 в 16:20