LinqConnect generates return type of object for Oracle Table Functions

LinqConnect generates return type of object for Oracle Table Functions

Postby ryanbd » Tue 06 May 2014 03:50

We are updating some of our custom code to use Oracle instead of MSSQL. The code currently uses a DataContext class that was created by sqlmetal.exe to communicate with a MSSQL database.

I am trying to use LinqConnect to generate a new Datacontext. I am able to generate the Context classes but all of our table functions are being generated with a return type of System.Object instead of using IQueryable (http://www.devart.com/linqconnect/docs/Table-Functions.html).

Here is an example of one of the functions we are using as well as the types:

CREATE OR REPLACE TYPE MYUSER.T_ORDERINFO IS OBJECT
(
CUSTEDP NUMBER(18,0),
COMPANY CHAR(2),
DIVISION CHAR(2)
);

CREATE OR REPLACE TYPE MYUSER.T_ORDERINFO_COLL IS TABLE OF T_ORDERINFO;

CREATE OR REPLACE FUNCTION MYUSER.spMYUSERGetOrderInfo (OrderNo_IN IN VARCHAR2)
RETURN T_ORDERINFO_COLL
IS
l_res_coll T_ORDERINFO_COLL;
custedp NUMBER(18,0);
co CHAR(2);
div CHAR(2);
BEGIN

l_res_coll := T_ORDERINFO_COLL();

BEGIN
SELECT SUBSTR(OX.XREFNO,3,9) INTO custedp
FROM PRODUCTION.ORDERXREF OX
WHERE OX.SEARCHTYPE = 'PY'
AND OX.FULLORDERNO = CONCAT(OrderNo_IN , '0000');

SELECT OH.COMPANY, OH.DIVISION INTO co, div
FROM PRODUCTION.ORDERHEADER OH
WHERE OH.FULLORDERNO = CONCAT(OrderNo_IN , '0000');
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT OH.CUSTEDP, OH.COMPANY, OH.DIVISION INTO custedp, co, div
FROM PRODUCTION.ORDERHEADER OH
WHERE OH.FULLORDERNO = CONCAT(OrderNo_IN, '0000');
END;

l_res_coll.extend;
l_res_coll(1) := T_ORDERINFO(custedp, co, div);

RETURN l_res_coll;

END;

And here is a snippet of code from the Context.Designer.cs file that is generated by LinqConnect:

/// <summary>
/// There are no comments for SPMYUSERGETORDERINFO in the schema.
/// </summary>
[Function(Name=@"MYUSER.SPMYUSERGETORDERINFO", IsComposable=true)]
public System.Object SPMYUSERGETORDERINFO([Parameter(Name="ORDERNO_IN", DbType="VARCHAR2(4000 CHAR)")] string ORDERNO_IN)
{
IExecuteResult _SPMYUSERGETORDERINFOResult = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), ORDERNO_IN);
return ((System.Object)(_SPMYUSERGETORDERINFOResult.ReturnValue));
}

I also do not see the TYPES defined anywhere in the Context.Designer.cs class that is being generated.

Any advise would be appreciated.
ryanbd
 
Posts: 2
Joined: Tue 06 May 2014 03:33

Re: LinqConnect generates return type of object for Oracle Table Functions

Postby MariiaI » Tue 06 May 2014 11:39

In fact, LinqConnect works with a specific set of primitive data types only, i.e. user-defined types are not supported. Please refer here: http://www.devart.com/linqconnect/docs/?oracle_datatypemapping.html

As a workaround you can try creating the wrapper function, that calls your function but returns the cursor, and add it to the LinqConnect model.
If you encounter any problems with this, feel free to contact us.

JIC: the sample, which is described here, works without errors, e.g. the GetEmpsPipelined method is generated with the IQueryable<ShortEmpInfo> return type.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: LinqConnect generates return type of object for Oracle Table Functions

Postby ryanbd » Tue 06 May 2014 23:37

Thank you for the information. I have complete control over these functions since they exist for our software alone. I have modified one of the functions so it is now a pipelined function and it seems to have been generated correctly in our C# DataContext class.

I appreciate the help. Thanks again.
ryanbd
 
Posts: 2
Joined: Tue 06 May 2014 03:33

Re: LinqConnect generates return type of object for Oracle Table Functions

Postby MariiaI » Wed 07 May 2014 07:13

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to LinqConnect (LINQ to SQL support)