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 }});
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?
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 }});
I think it's important to note that there is a finite limit to how many items can you send in your array. I realized this the hard way when I passed in too many ids. I don't remember the exact number but from my memory I think it's 200 elements before Dapper stops working/executing the query.
Marko, that IS important. And, if you are doing it that way, you might consider finding another way of querying your data, such as doing a join or an anti-join rather than passing a list of ids. The IN clause is not the most highly performing query and can often be replaced by an exists clause, which will be faster.
FYI - SQL Server 2008 R2 has a limit of 2100 entries on the
INclause.And SQLite has a default limit of 999 variables.
Beware: in SQL Server this fails if you have multiple items in your array and you wrap the parameter in brackets. Removing the brackets will fix the issue.