DapperLib / Dapper

Dapper - a simple object mapper for .Net
https://www.learndapper.com/
Other
17.49k stars 3.67k forks source link

Pass C# array to UDTF in Snowflake as an array #2081

Open vyeghikyan-st opened 5 months ago

vyeghikyan-st commented 5 months ago

I have a parameter in C#

var selectedUsersIds = new long[]{1,2,3};

which I want to pass to a UDTF in Snowflake:

CREATE OR REPLACE FUNCTION GetUserInfo  ( pselectedUsersIds ARRAY)
  RETURNS TABLE ( 
  "Username" VARCHAR(1000), 
  "Age" NUMBER(38,0))
...

My sql query reads

SELECT * FROM TABLE(GetUserInfo(:SelectedUsers))

When I pass it via a DynamicParameters object

 var dynamicParams = new DynamicParameters();
 dynamicParams.Add("SelectedUsers", selectedUsersIds, (DbType?)null);

I get an error from Snowflake, saying that

Invalid argument types for function 'GetUserInfo': ( ROW(NUMBER(10,0), ROW(NUMBER(10,0), NUMBER(10,0))))

Is there a way to pass the C# array as a Snowflake array?