Devart Forums

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/ ... tions.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.

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/ ... pping.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.

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.

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.