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

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

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

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

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

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

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

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

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

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

SELECT * FROM tbl WHERE col IN @val

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

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

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

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

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