SELECT * FROM X WHERE id IN (...) with Dapper ORM

Solution 1:

Dapper supports this directly. For example...

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

unless you are using Postgres, in which case see this answer

Solution 2:

Directly from the GitHub project homepage:

Dapper allow you to pass in IEnumerable and will automatically parameterize your query.

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

Solution 3:

If your IN clause is too big for MSSQL to handle, you can use a TableValueParameter with Dapper pretty easily.

1. Create your TVP type in MSSQL:

   CREATE TYPE [dbo].[MyTVP] AS TABLE([ProviderId] [int] NOT NULL)
   

2. 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
   

3. 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";
   

4. Pass the DataTable in your Dapper query call

   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 UPDATE with an inner join to the TVP.