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

.net sql dapper
Marko picture Marko · Dec 5, 2011 · Viewed 112.7k times · Source

What is the best way to write a query with IN clause using Dapper ORM when the list of values for the IN clause is coming from business logic? For example let's say I have a query:

SELECT * 
  FROM SomeTable 
 WHERE id IN (commaSeparatedListOfIDs)

The commaSeparatedListOfIDs is being passed in from business logic and it can be any type of IEnumerable(of Integer). How would I construct a query in this case? Do I have to do what I've been doing so far which is basically string concatenation or is there some sort of advanced parameter mapping technique that I'm not aware of?

Answer

LukeH picture LukeH · Dec 5, 2011

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

Related questions

Multiple SQL statements in one roundtrip using Dapper.NET

There is a nice feature in ADO.NET that allows you to send multiple SQL statements to database in one roundtrip and receive results for all statements: var command = new SqlCommand("SELECT count(*) FROM TableA; SELECT count(*) FROM TableB;", connection); …

Read More
Closing connection when using Dapper

Is it necessary to close connection once query is executed explicitly calling Close method or putting the connection within Using statement? Would leaving connection open lead to connection reuse and improve SQL performance for future queries?

Read More
How do I build a dynamic Where clause with Dapper when passing in a model

I have an example model that looks like this: public class PersonModel { public int Id {get; set;} public string FirstName {get; set;} public string Lastname {get; set;} public string City {get; set;} } In my repository I want to create a …

Read More