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

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

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

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.