How to pass an Array of GUIDs to Dapper/MySQL as parameter?

Question

I have a table that the ID field is a GUID stored in binray form. Now i need to pass an array of GUIDs to the MySQL to perform the bellow query:

SELECT `ID` FROM `TABLE_NAME` WHERE `ID` IN (...`GUIDs in Binary Form`...);

However, the MySqlConnector seems to not support an array of byte arrays (Byte[][]) and Dapper can not convert this type of parameter.

I'm using a custom function to convert GUID type to Byte[]. Is there any way to do the above query in Dapper?

Answer 1

If you're using MySqlConnector, you can set the GuidFormat connection string option to Binary16, LittleEndianBinary16, or TimeSwapBinary16 to match how your application code is converting Guid to byte arrays.

Then you can use Dapper's list support to pass an IEnumerable<Guid> or Guid[] as a parameter: Dapper will expand it to a list of individual Guid parameters and MySqlConnector will format them correctly as binary for your MySQL table.