How should I pass an array to the FIELD statement using Dapper, c#

Question

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.

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
– Marc Gravell, Commented May 9, 2019 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
– Panic, Commented May 9, 2019 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
– Lasse V. Karlsen, Commented May 9, 2019 at 20:50
Years later... from my understanding dapper does not support arrays in the order by. — Paul Grimes
– Paul Grimes, Commented Aug 29, 2023 at 20:46

Surya Bhashyam · Accepted Answer · 2020-06-25 07:29:15Z

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();

answered Jun 25, 2020 at 7:29

Surya Bhashyam

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Rob Rader · Accepted Answer · 2019-05-12 01:45:25Z

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;
}

edited May 12, 2019 at 1:45

answered May 9, 2019 at 20:44

Rob Rader

112 bronze badges

1 Comment

Panic Over a year ago

No, I cannot use OrderBy using Linq. It defeats the whole purpose of using FIELD()

Collectives™ on Stack Overflow

How should I pass an array to the FIELD statement using Dapper, c#

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related