SNOW-1503425: Can't bind array properly - filtering IN or ARRAY_CONTAINS doesn't work

[myshon]

Please answer these questions before submitting your issue.
In order to accurately debug the issue this information is required. Thanks!

1. What version of .NET driver are you using?
   .net8

2. What operating system and processor architecture are you using?
   MAC OS Sonoma 14.5

3. What version of .NET framework are you using?
   .net8 latestFeature

4. What did you do?
   I have following table

create or replace TABLE MY_DB.MY_SCHEMA.OBJECTS (
	ID VARCHAR(36) NOT NULL,
	OBJECT_ID VARCHAR(36) NOT NULL,
	TEXT VARCHAR(36) NOT NULL,
);

I try to execute query with filtering that OBJECT_ID is in array passed as parameter.
I tried following queries:

1. Using IN (It fails on compilation)

SELECT OBJECT_ID, TEXT
FROM MY_DATABASE.MY_SCHEMA.OBJECTS
WHERE OBJECT_ID IN (?) 

OR
2. Using Array Contains (it compiles properly but return empty result)

SELECT OBJECT_ID, TEXT
FROM MY_DATABASE.MY_SCHEMA.OBJECTS
WHERE ARRAY_CONTAINS(OBJECT_ID::VARIANT, ?::ARRAY)

I tried pass array of values as parameter to ARRAY_CONTAINS

cmd = conn.CreateCommand();
cmd.CommandText = query;
var parameter = cmd.CreateParameter();
parameter.ParameterName = "0";
parameter.DbType = DbType.String;
parameter.Value = $"[{string.Join(",", request.ObjectIds)}]";
cmd.Parameters.Add(parameter);

I tried multiple way how to pass value, also

$"[{string.Join(",", request.ObjectIds.Select(id =>  $"'{id}'"))}]";

But it doesn't work (empty result is returned). When I execute same query from Snowflake console it returns values correctly.

SELECT OBJECT_ID, TEXT
FROM MY_DATABASE.MY_SCHEMA.OBJECTS
WHERE ARRAY_CONTAINS(OBJECT_ID::VARIANT, ['e1266587-e130-4417-8b1d-8521f1a4716e','08a9c500-ac03-45b8-9b04-62a0a09084af']::ARRAY)

5. What did you expect to see?

I want to know how to bind ARRAY_CONTAINS correctly for type string/varchar.

6. Can you set logging to DEBUG and collect the logs?

   https://community.snowflake.com/s/article/How-to-generate-log-file-on-Snowflake-connectors

   There is an example in READMD.md file showing you how to enable logging.

7. What is your Snowflake account identifier, if any? (Optional)

[myshon]

Ok, I was looking for a solution in the codebase and I haven't found one, then I created a bug today.
Finally I found the the very non-intuitive solution today 😄
#911

[sfc-gh-dszmolka]

good to hear you found the solution. created a new PR #985 because #911 was so old the doc structure changed in the meantime. hope we can merge this into the docs eventually

closing this now but do comment please if you need further help