Question
I am trying to run following query but getting an error.
conn.Query("select d.ID, d.CategoryID from Document d inner join @Cases c on d.CaseID = c.ID", new { Cases = new List<string> { "000-6575-101", "5902-205" }});
When I run the command I receive Incorrect syntax near ','.
My question is, is it even possible to do something like what I am doing?
Accepted Answer
Dapper support an in syntax for this scenario:
var ids = new List<string> { "000-6575-101", "5902-205" };
conn.Query("select d.ID, d.CategoryID from Document d where d.CaseID in @ids", new { ids});
This is one of the few cases where dapper will actually change your query to do what you want (while remaining fully parameterized, etc).
It also supports (optionally, see SqlMapper.Settings):
- parameter padding to reduce query-plan cache saturation
- use of
string_spliton SQL 2016 for integer types (List<int>etc)
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow