array as parameter of store procedure

[protoway]

Hi,

i would like to ask, whether there is possible to use stored procedures in
LINQ to SQL on postgre DB using dotConnect for PostgreSQL in c#.
We are using the latest version of dotConnect.

We have a test stored procedure to sum all numbers in input array:

Code: Select all

CREATE OR REPLACE FUNCTION sum(data integer[]) 
  RETURNS integer AS 
$$ 
declare 
  ret$ integer; 
BEGIN 
  ret$ := 0; 
   
  for i in array_lower(data, 1) .. array_upper(data, 1) loop 
    ret$ := ret$ + data[i]; 
  end loop; 
 
  return ret$; 
END; 
$$ LANGUAGE 'plpgsql';

After adding this procedure using Entity developer to my .lqml file. It is added like this:

Code: Select all

        [Function(Name=@"public.sum", IsComposable=true)]
        public System.Nullable sum([Parameter(Name="data", DbType="_int4")] object data)
        {
            return ((System.Nullable)(this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), data).ReturnValue));
        }

It wouldn't be a problem, but problem is:
what should i use as parameter? array? list? anything else?
if i tried array

Code: Select all

                int[] input = new Int32[] { 3, 5, 7 };
                int? result = db.sum((object)input);

i've received an exception:

Code: Select all

System.InvalidOperationException was unhandled
  Message="Invalid datatype: _int4"
  Source="Devart.Data.PostgreSql.Linq"
  StackTrace:
       at Devart.Data.PostgreSql.Linq.Provider.a.a(String A_0, Boolean A_1)
       at Devart.Data.PostgreSql.Linq.Provider.PgSqlDataSourceInfo.a.a(String A_0)
       at Devart.Data.Linq.Provider.Query.bk.a(MethodCallExpression A_0, MetaFunction A_1)
       at Devart.Data.Linq.Provider.Query.bk.a(MethodCallExpression A_0)
       at Devart.Data.Linq.Provider.Query.bk.b(MethodCallExpression A_0)
       at Devart.Data.Linq.Provider.Query.bk.j(Expression A_0)
       at Devart.Data.Linq.Provider.Query.bk.i(Expression A_0)
       at Devart.Data.Linq.Provider.DataProvider.a(Expression A_0)
       at Devart.Data.Linq.Provider.DataProvider.h(Expression A_0)
       at Devart.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters)
       at loadtestContext.loadtestDataContext.sum(Object data) in Z:\projects\Area 51\ConsoleApplication5\ConsoleApplication5\DataContext1.Designer.cs:line 194
       at ConsoleApplication5.Program.Main(String[] args) in Z:\projects\Area 51\ConsoleApplication5\ConsoleApplication5\Program.cs:line 58
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

How can be this solved?
Is there a way to use such procedure?

Thanks in advance.

[AndreyR]

Sorry, but PostgreSQL array parameters are not supported in LINQ to PostgreSQL yet.
We will investigate the possibility to add this functionality, I will let you know about the results of our investigation.