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';
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));
}
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);
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:
Is there a way to use such procedure?
Thanks in advance.