3

I am trying to pass an array as a parameter using Dapper. My array of values must go into the FIELD section.

I tried to join the array elements into a String and pass it. Still doesn't work.

Guid[] myArr = Ids.ToArray(); // Ids are List<Guid>
var script = @"SELECT * FROM table WHERE Id in @Ids ORDER BY FIELD(Id, @param)";
using (var connection = database.Connection)
            {
                return connection.Query<MyDataType>(script, new {Ids = Ids, param = myArr}).ToList();
            }

This query is just doing an Order By Id. I also passed in param = Ids. Still doesn't work.

Panic
  • 91
  • 11
  • 3
    Interesting; short version is "that's the first time I've ever seen that syntax, and consequently Dapper currently has no support for that" - so: nothing great right now. – Marc Gravell May 09 '19 at 20:02
  • When I join my values to a String, I get "'1','2','3'". I wanted to get rid of the double quotes at the start and end; so I used .Trim('"'). Could not make it work – Panic May 09 '19 at 20:06
  • You sure those double quotes isn't just the debugger being "helpful"? It tries to format text into legal C# syntax, which means it adds quotes, and escapes problematic characters, both of which there are ample amounts of questions about here on Stack Overflow. – Lasse V. Karlsen May 09 '19 at 20:50
  • Years later... from my understanding dapper does not support arrays in the order by. – Paul Grimes Aug 29 '23 at 20:46

2 Answers2

1

Convert the list into array in parameter list of dapper.

 var sqlQuery = "Select * from table Where Columnname IN @periodIdStr";
 var result = dapperUOW.Connection.Query<Entity>(sqlQuery ,
                        param: new { periodIdStr = periodIds.ToArray() }, transaction: dapperUOW.Transaction).ToList();
Surya Bhashyam
  • 11
  • 1
0

According to this question SELECT * FROM X WHERE id IN (...) with Dapper ORM you should be able to do a WHERE in with dapper but the limit of the number of items in the array is something to watch out for.

Then you could also break out the ORDER BY FIELD SQL and use linq to do an OrderBy on your results.

Edit: Would this work?

Guid[] myArr = Ids.ToArray(); // Ids are List<Guid>
var script = @"SELECT * FROM table WHERE Id in @Ids)";
using (var connection = database.Connection)
{
    var myDataTypeObjects = connection.Query<MyDataType>(script, new {Ids = Ids}).ToList();
    myDataTypeObjects = myDataTypeObjects.OrderBy(x => Ids.IndexOf(x.Id)).ToList();
    return myDataTypeObjects;
}
Rob Rader
  • 11
  • 2