SELECT FROM X WHERE id IN with Dapper ORM

Question

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

User · Accepted Answer

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

User · Answer

Directly from the GitHub project homepage      Dapper allow you to pass in IEnumerable and will automatically parameterize your query    connection Query lt int gt         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

User · Answer

In my case I ve used this   var query    select   from table where Id IN  Ids   var result   conn Query lt MyEntity gt  query  new   Ids   ids       my variable  ids  in the second line is an IEnumerable of strings  also they can be integers I guess

User · Answer

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    quot SELECT IntegerColumn  StringColumn FROM SomeTable WHERE IntegerColumn IN  listOfIntegers quot    var conditions   new   listOfIntegers         var results   connection Query SQL  conditions

User · Answer

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 lt Item gt  GetItemsByItemIds IEnumerable lt int gt  items       var itemList   new HashSet items      if  itemList Count    0    return Enumerable Empty lt Item gt    ToList   AsReadOnly          var itemDataTable   new DataTable       itemDataTable Columns Add  ItemId   typeof int       itemList ForEach itemid   gt  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 lt Item gt                 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

User · Answer

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

User · Answer

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

User · Answer

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 values  var 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

User · Answer

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

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

Examples related to .net

Examples related to sql

Examples related to dapper