array as parameter of store procedure

array as parameter of store procedure

Postby protoway » Wed 09 Dec 2009 10:30


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
  ret$ integer;
  ret$ := 0;
  for i in array_lower(data, 1) .. array_upper(data, 1) loop
    ret$ := ret$ + data[i];
  end loop;

  return ret$;
$$ 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"
       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()

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

Thanks in advance.
Posts: 5
Joined: Mon 19 Oct 2009 09:27

Postby AndreyR » Thu 10 Dec 2009 16:50

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.
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Return to dotConnect for PostgreSQL