SQL SELECT * FROM X WHERE id IN (...) with Dapper ORM
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8388093/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT * FROM X WHERE id IN (...) with Dapper ORM
提问by Marko
What is the best way to write a query with IN clause using Dapper ORM when the list of values for the IN clause is coming from business logic? For example let's say I have a query:
当 IN 子句的值列表来自业务逻辑时，使用 Dapper ORM 编写带有 IN 子句的查询的最佳方法是什么？例如，假设我有一个查询：
SELECT *
FROM SomeTable
WHERE id IN (commaSeparatedListOfIDs)
The commaSeparatedListOfIDsis being passed in from business logic and it can be any type of IEnumerable(of Integer). How would I construct a query in this case? Do I have to do what I've been doing so far which is basically string concatenation or is there some sort of advanced parameter mapping technique that I'm not aware of?
在commaSeparatedListOfIDs正在从业务逻辑传递，它可以是任何类型的IEnumerable(of Integer)。在这种情况下，我将如何构建查询？我是否必须做到目前为止我一直在做的基本上是字符串连接的工作，或者是否有某种我不知道的高级参数映射技术？
回答by LukeH
Dapper supports this directly. For example...
Dapper 直接支持这一点。例如...
string sql = "SELECT * FROM SomeTable WHERE id IN @ids"
var results = conn.Query(sql, new { ids = new[] { 1, 2, 3, 4, 5 }});
回答by Factor Mystic
Directly from the GitHub project homepage:
直接从GitHub 项目主页：
Dapper allow you to pass in IEnumerable and will automatically parameterize your query.
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 });
Will be translated to:
将被翻译为：
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
回答by Mr. T
If your INclause is too big for MSSQL to handle, you can use a TableValueParameter with Dapper pretty easily.
如果您的IN子句对于 MSSQL 来说太大而无法处理，您可以非常轻松地将 TableValueParameter 与 Dapper 一起使用。
Create your TVP type in MSSQL:
CREATE TYPE [dbo].[MyTVP] AS TABLE([ProviderId] [int] NOT NULL)Create a
DataTablewith the same column(s) as the TVP and populate it with valuesvar tvpTable = new DataTable(); tvpTable.Columns.Add(new DataColumn("ProviderId", typeof(int))); // fill the data table however you wishModify your Dapper query to do an
INNER JOINon the TVP table:var query = @"SELECT * FROM Providers P INNER JOIN @tvp t ON p.ProviderId = t.ProviderId";Pass the DataTable in your Dapper query call
sqlConn.Query(query, new {tvp = tvpTable.AsTableValuedParameter("dbo.MyTVP")});
在 MSSQL 中创建您的 TVP 类型：
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";在您的 Dapper 查询调用中传递 DataTable
sqlConn.Query(query, new {tvp = tvpTable.AsTableValuedParameter("dbo.MyTVP")});
This also works fantastically when you want to do a mass update of multiple columns - simply build a TVP and do an UPDATEwith an inner join to the TVP.
当您想要对多个列进行批量更新时，这也非常有效 - 只需构建一个 TVP 并UPDATE与TVP进行内部连接。
回答by ErikE
Here is possibly the fastest way to query a large number of rows with Dapper using a list of IDs. I promise you this is faster than almost any other way you can think of (with the possible exception of using a TVP as given in another answer, and which I haven't tested, but I suspect may be slower because you stillhave to populate the TVP). It is planetsfaster than Dapper using INsyntax and universesfaster than Entity Framework row by row. And it is even continents faster than passing in a list of VALUESor UNION ALL SELECTitems. It can easily be extended to use a multi-column key, just add the extra columns to the DataTable, the temp table, and the join conditions.
这可能是使用 ID 列表使用 Dapper 查询大量行的最快方法。我向你保证这比你能想到的几乎任何其他方式都快（除了使用另一个答案中给出的 TVP 可能的例外，我还没有测试过，但我怀疑可能会更慢，因为你仍然需要填充TVP）。它是 行星比使用小巧玲珑更快IN的语法和宇宙不是按照行实体框架排得更快。它甚至比传递一个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;
}
}
Be aware that you need to learn a little bit about Bulk Inserts. There are options about firing triggers (the default is no), respecting constraints, locking the table, allowing concurrent inserts, and so on.
请注意，您需要了解一些有关批量插入的知识。有关于触发触发器（默认为否）、遵守约束、锁定表、允许并发插入等的选项。
回答by Brian Ogden
Also make sure you do not wrap parentheses around your query string like so:
还要确保不要像这样将括号括在查询字符串周围：
SELECT Name from [USER] WHERE [UserId] in (@ids)
I had this cause a SQL Syntax error using Dapper 1.50.2, fixed by removing parentheses
我使用 Dapper 1.50.2 导致 SQL 语法错误，通过删除括号修复
SELECT Name from [USER] WHERE [UserId] in @ids
回答by Coder Absolute
It is not necessaryto add ()in the WHERE clause as we do in a regular SQL. Because Dapper does that automatically for us. Here is the syntax:-
这是没有必要添加()在WHERE子句中，我们在一个普通的SQL做。因为 Dapper 会自动为我们做到这一点。这是syntax：-
const string SQL = "SELECT IntegerColumn, StringColumn FROM SomeTable WHERE IntegerColumn IN @listOfIntegers";
var conditions = new { listOfIntegers };
var results = connection.Query(SQL, conditions);
回答by SanS?????Y?????S?????
Example for postgres:
postgres 示例：
string sql = "SELECT * FROM SomeTable WHERE id = ANY(@ids)"
var results = conn.Query(sql, new { ids = new[] { 1, 2, 3, 4, 5 }});
回答by Cesar
In my case I've used this:
在我的情况下，我使用了这个：
var query = "select * from table where Id IN @Ids";
var result = conn.Query<MyEntity>(query, new { Ids = ids });
my variable "ids" in the second line is an IEnumerable of strings, also they can be integers I guess.
我在第二行中的变量“ids”是一个 IEnumerable 字符串，我猜它们也可以是整数。
回答by MatBailie
In my experience, the most friendly way of dealing with this is to have a function that converts a string into a table of values.
根据我的经验，最友好的处理方法是使用一个函数将字符串转换为值表。
There are many splitter functions available on the web, you'll easily find one for whatever if your flavour of SQL.
网络上有许多可用的拆分器函数，无论您的 SQL 风格如何，您都可以轻松找到一个。
You can then do...
然后你可以做...
SELECT * FROM table WHERE id IN (SELECT id FROM split(@list_of_ids))
Or
或者
SELECT * FROM table INNER JOIN (SELECT id FROM split(@list_of_ids)) AS list ON list.id = table.id
(Or similar)
（或类似）
