postgres的示例:

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

根据我的经验，处理这个问题最友好的方法是使用一个函数将字符串转换为值表。

在网络上有很多可用的分配器函数，如果你喜欢SQL，你很容易就能找到一个。

然后你可以做…

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

(或相似的)

不需要像在常规SQL中那样在WHERE子句中添加()。因为达普会自动帮我们做。语法如下:-

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

var conditions = new { listOfIntegers };
    
var results = connection.Query(SQL, conditions);

直接从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

在我的例子中，我使用了这个:

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

我的变量“ids”在第二行是一个字符串的IEnumerable，我猜他们也可以是整数。

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 still have to populate the TVP). It is planets faster than Dapper using IN syntax and universes faster than Entity Framework row by row. And it is even continents faster than passing in a list of VALUES or UNION ALL SELECT items. 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.

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

请注意，您需要学习一些关于批量插入的知识。有关于触发触发器(默认为no)、尊重约束、锁定表、允许并发插入等选项。