Каков наилучший способ написать запрос с предложением IN с использованием Dapper ORM, когда список значений для предложения IN исходит из бизнес-логики? Например, допустим, у меня есть запрос:
SELECT *
FROM SomeTable
WHERE id IN (commaSeparatedListOfIDs)
В бизнес-логике commaSeparatedListOfIDs передается commaSeparatedListOfIDs, и это может быть любой тип IEnumerable(of Integer). Как я могу построить запрос в этом случае? Должен ли я делать то, что я делал до сих пор, что в основном представляет собой конкатенацию строк или есть какая-то техника расширенного параметрирования, о которой я не знаю?
Также убедитесь, что вы не завертываете круглые скобки вокруг строки запроса следующим образом:
SELECT Name from [USER] WHERE [UserId] in (@ids)
У меня возникла ошибка SQL-синтаксиса с использованием Dapper 1.50.2, исправлена путем удаления круглых скобок
[F2]В моем случае я использовал это:
var query = "select * from table where Id IN @Ids";
var result = conn.Query<MyEntity>(query, new { Ids = ids });
моя переменная «ids» во второй строке является IEnumerable из строк, также они могут быть целыми числами, которые я думаю.
Нет необходимости добавлять () в предложение WHERE, которое мы обычно делаем, потому что Dapper делает это автоматически для вас. Вот syntax: -
const string SQL = "SELECT IntegerColumn, StringColumn FROM SomeTable WHERE IntegerColumn IN @EnumeratedList";
var conditions = new { listOfIntegers };
var results = connection.Query(SQL, conditions);
Возможно, это самый быстрый способ запросить большое количество строк с помощью 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. Существуют варианты запуска триггеров (по умолчанию нет), соблюдение ограничений, блокировка таблицы, возможность одновременных вставок и т. Д.
Непосредственно с главной страницы проекта 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
По моему опыту, самым удобным способом борьбы с этим является наличие функции, которая преобразует строку в таблицу значений.
В Интернете есть много функций сплиттера, вы будете легко найти любой, если ваш вкус 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
(или аналогичный)
Если ваше предложение 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.