Calling a scalar function with entity framework not possible?

Calling a scalar function with entity framework not possible?

Postby formatsw » Fri 30 Aug 2013 13:31

Dear Devart Team!
I am currently evaluating DotConnect for Oracle an ran into a problem.
I would like to exeute a stored function that returns a scalar using entity framewok. My current approach runs with SQL-Server, it did run with the oracle data provider but it fails using dotConnect from Oracle.
I am new to Oracle.

Here's my function definition

Code: Select all
CREATE FUNCTION "FORMAT_IVEAS"."NEXTRECCOUNTER"
( vchTABLENAME IN NVARCHAR2 ) RETURN NUMBER AS nRID NUMBER(11,0);
BEGIN
...
RETURN nRID;
END


Here comes the SSDL Part (that I needed to modify)

Code: Select all
  <Function Name="NEXTRECCOUNTER" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="FORMAT_IVEAS">
    <Parameter Name="vchTableName" store:Name="VCHTABLENAME" Type="NVARCHAR2" Mode="In" />
  </Function>

This is the function import in CSDL
Code: Select all
         
<FunctionImport Name="NEXTRECCOUNTER">
            <Parameter Name="vchTableName" Mode="In" Type="String" />
          </FunctionImport>

And this the function mapping
Code: Select all
<FunctionImportMapping FunctionImportName="NEXTRECCOUNTER" FunctionName="IVEASModel.Store.NEXTRECCOUNTER" />


Unfortunately the Entity Framework has a general Problem wird funtions that return a scalar. So we have to do this to call the function
Code: Select all
                ent.Connection.Open();
                using (DbCommand cmd = ent.Connection.CreateCommand())
                {
                    cmd.CommandText = "IVEASEntities.NEXTRECCOUNTER";
                    cmd.CommandType = CommandType.StoredProcedure;
                    var tableparam = cmd.CreateParameter();
                    tableparam.DbType = DbType.String;
                    tableparam.ParameterName = "vchTableName";
                    tableparam.Direction = ParameterDirection.Input;
                    tableparam.Value = tableName;
                    cmd.Parameters.Add(tableparam);
                    // Add param
                    var retval = cmd.CreateParameter();
                    retval.DbType = DbType.Int32;
                    retval.ParameterName = "RetVal";
                    retval.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(retval);
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        ent.Connection.Close();
                    }
                    return Convert.ToInt32(retval.Value);
                }


Using the Devart Database drivers, I get an error:
Devart.Data.Oracle.OracleException: ORA-06550: line 2, column 3:
PLS-00221: 'NEXTRECCOUNTER' is not a procedure or is undefined
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

I traced the commands sent to the database using dbMonitor.
What I found there looks quite confus. S.th. like
BEGIN NEXTRECCOUNTER(:vchTableName); END;

is sent to the server.
The Parameter list only shows the input parameter, but no return parameter. I believe that this problem causes the PLS-00221.

So, is there a way the retrieve the function return value using dotconnect?
Any help would be appreciated.

brgds
Sven Weiberg
formatsw
 
Posts: 1
Joined: Wed 28 Aug 2013 15:19

Re: Calling a scalar function with entity framework not possible?

Postby Shalex » Fri 06 Sep 2013 17:34

Please add Devart Entity Model (*.edml) instead of ADO.NET Entity Data Model (*.edmx) to your project: http://www.devart.com/entitydeveloper/ed-vs-edm.html.

The function will be added to the SSDL part of the model. Open Visual Studio > Tools > Entity Developer > Model Explorer and set Concealed Function=True for your function. Then drag&drop it to the diagram surface to generate the corresponding method. Open Method Editor and set Return Type to Scalars:Decimal. Save the model and try using the generated method.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle