SELECT * FROM X WHERE id IN (...) with Dapper ORM
.NetSqlDapper.Net Problem Overview
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?
.Net Solutions
Solution 1 - .Net
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 - .Net
Directly from the GitHub project homepage:
> Dapper allow you to pass in IEnumerable
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 - .Net
If your IN
clause is too big for MSSQL to handle, you can use a TableValueParameter with Dapper pretty easily.
-
Create your TVP type in MSSQL:
CREATE TYPE [dbo].[MyTVP] AS TABLE([ProviderId] [int] NOT NULL)
-
Create a
DataTable
with the same column(s) as the TVP and populate it with valuesvar tvpTable = new DataTable(); tvpTable.Columns.Add(new DataColumn("ProviderId", typeof(int))); // fill the data table however you wish
-
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";
-
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.
Solution 4 - .Net
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 }});
Solution 5 - .Net
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
Solution 6 - .Net
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.
Solution 7 - .Net
It is not necessary to add ()
in the WHERE clause as we do in a regular SQL. Because Dapper does that automatically for us. Here is the syntax
:-
const string SQL = "SELECT IntegerColumn, StringColumn FROM SomeTable WHERE IntegerColumn IN @listOfIntegers";
var conditions = new { listOfIntegers };
var results = connection.Query(SQL, conditions);
Solution 8 - .Net
In my case I've used this:
var query = "select * from table where Id IN @Ids";
var result = conn.Query<MyEntity>(query, new { Ids = ids });
my variable "ids" in the second line is an IEnumerable of strings, also they can be integers I guess.
Solution 9 - .Net
In my experience, the most friendly way of dealing with this is to have a function that converts a string into a table of values.
There are many splitter functions available on the web, you'll easily find one for whatever if your flavour of SQL.
You can then do...
SELECT * FROM table WHERE id IN (SELECT id FROM split(@list_of_ids))
Or
SELECT * FROM table INNER JOIN (SELECT id FROM split(@list_of_ids)) AS list ON list.id = table.id
(Or similar)
Solution 10 - .Net
SELECT * FROM tbl WHERE col IN @val
I have also noticed that this syntax does not work with byte[]
. Dapper takes only the last element and the parameter must be wrapped in parenteses.
However, when I change the type to int[]
everything works.