postgres的示例:

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

不需要像在常规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

SELECT * FROM tbl WHERE col IN @val

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

如果你的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。

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

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

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