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

在网络上有很多可用的分配器函数，如果你喜欢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

(或相似的)

Dapper直接支持这一点。例如……

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

除非你正在使用Postgres，在这种情况下，请参阅这个答案

直接从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)、尊重约束、锁定表、允许并发插入等选项。

如果你的IN子句太大，MSSQL无法处理，你可以很容易地使用一个TableValueParameter与Dapper。

Create your TVP type in MSSQL: CREATE TYPE [dbo].[MyTVP] AS TABLE([ProviderId] [int] NOT NULL) Create a DataTable with the same column(s) as the TVP and populate it with values var tvpTable = new DataTable(); tvpTable.Columns.Add(new DataColumn("ProviderId", typeof(int))); // fill the data table however you wish Modify your Dapper query to do an INNER JOIN on 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")});

当您想要对多个列进行大规模更新时，这也非常有效——只需构建一个TVP，并使用到TVP的内部连接执行update。

还要确保你没有像这样用圆括号括起你的查询字符串:

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

我使用Dapper 1.50.2导致SQL语法错误，通过删除括号来修复

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

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

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

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

postgres的示例:

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

SELECT * FROM tbl WHERE col IN @val

我还注意到这种语法不适用于byte[]。Dapper只接受最后一个元素，参数必须用圆括号括起来。 然而，当我将类型更改为int[]时，一切正常。