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

Question

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

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.

Example for postgres:

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

Also make sure you do not wrap parentheses around your query string like so:

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

I had this cause a SQL Syntax error using Dapper 1.50.2, fixed by removing parentheses

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

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

Be aware that you need to learn a little bit about Bulk Inserts. There are options about firing triggers (the default is no), respecting constraints, locking the table, allowing concurrent inserts, and so on.