Calling Stored Procedure with scalar return value from EF

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
tokh
Posts: 4
Joined: Fri 08 May 2009 08:59

Calling Stored Procedure with scalar return value from EF

Post by tokh » Wed 24 Jun 2009 12:53

Hi,
we want to use sequences to get the value for the id column before calling the Create()-Method.
To get these we wrote an StoredProcedure:

Code: Select all

create or replace PROCEDURE GetNextPrimaryKey (
                              sequenceName IN VARCHAR2,
                              NEXTID OUT VARCHAR2
                              ) AS v_sequence VARCHAR2(30);
  BEGIN
    SELECT OBJECT_NAME INTO v_sequence FROM USER_OBJECTS
    WHERE OBJECT_NAME = UPPER(sequenceName) AND OBJECT_TYPE = 'SEQUENCE';
    EXECUTE IMMEDIATE 'SELECT '|| v_sequence ||'.NEXTVAL from dual' INTO NEXTID;
  END GetNextPrimaryKey; 
(To keep it simple we use varchar during our first steps)
This works fine.

We stuck when trying to call this Procedure from EF

Code: Select all

context.GETNEXTPRIMARYKEY("SEQ_ANWENDER");
We have troubles with the return value of the Procedure. Since we return a scalar value (NEXTID), EF fails?!
We also tried to create an custom object in C# with one property "NEXTID" and the correspondent mapping in edmx - this ends up with
the error message "The data reader is incompatible with the specified 'Model.PrimaryKey'. A member of the type, 'NEXTID', does not have a corresponding column in the data reader with the same name.".


So, is there a possibility to access this stored procedure with EF?

Thanks!

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 24 Jun 2009 14:03

This is a limitation of Microsoft code generation, it was discussed at our forum here:
http://devart.com/forums/viewtopic.php?t=12383
Try Devart Entity Developer, this is our tool which uses different code generation.

tokh
Posts: 4
Joined: Fri 08 May 2009 08:59

Post by tokh » Thu 25 Jun 2009 09:32

Thanks for your response.
We tried to call the procedure using devart Entity Developer.
Calling the procedure now works (after some minor changes in xxx.Designer.cs). But neither the input nor the output parameters are delivered.
This is the code:

Code: Select all

        public void Getnextprimarykey(string SEQUENCENAME, string NEXTID)
        {
            this.Connection.Open();
            System.Data.EntityClient.EntityCommand command = new System.Data.EntityClient.EntityCommand();
            command.CommandType = System.Data.CommandType.StoredProcedure;
[color=blue]            command.CommandText = @"DataSourceModelEntities.Getnextprimarykey";[/color]
            command.Connection = (System.Data.EntityClient.EntityConnection)this.Connection;
            global::System.Data.EntityClient.EntityParameter SEQUENCENAMEParameter = new global::System.Data.EntityClient.EntityParameter("SEQUENCENAME", System.Data.DbType.String);
            if (SEQUENCENAME != null)
                SEQUENCENAMEParameter.Value = SEQUENCENAME;
          
            command.Parameters.Add(SEQUENCENAMEParameter);
            global::System.Data.EntityClient.EntityParameter NEXTIDParameter = new global::System.Data.EntityClient.EntityParameter("NEXTID", System.Data.DbType.String);
        [color=blue]    SEQUENCENAMEParameter.Direction = System.Data.ParameterDirection.Output;[/color]
            command.Parameters.Add(NEXTIDParameter);
            command.ExecuteNonQuery();
        }
We had to change the "blue lines" :wink: to get the procedure called.

The procedure is called (we checked this within oracle), but no parameters are delivered.

Do we have to use different types for parameters or do we have to call the procedure in a different way?

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Fri 26 Jun 2009 08:32

Thank you for the report, we have found the error in our code generation.
We plan to improve it cardinally in one of the near builds.
As a temporary workaround, follow these steps.
Please define the NEXTID parameter as REF one in your code and change the method call appropriately.
Also, please add the following code after the ExecuteNonQuery() method:

Code: Select all

if(NEXTIDParameter.Value != null)
  NEXTID = (string)NEXTIDParameter.Value;

skingaby
Posts: 32
Joined: Thu 14 May 2009 16:17

Post by skingaby » Tue 07 Jul 2009 13:58

Change the proc into a Before Insert trigger on the table. Then edit the EDMX/EDML file (Entity Framework Model) and add the StoreGeneratedPattern="Identity" attribute to the key field in that table's definition, for example:

We have a table named DEAL. The trigger looks like this:

Code: Select all

CREATE OR REPLACE TRIGGER GCSD9.DEAL_BI_GETSEQ
  BEFORE INSERT ON GCSD9.DEAL FOR EACH ROW
  WHEN (NEW.DEAL_ID IS NULL)
BEGIN SELECT DEAL_ID_SEQ.NEXTVAL INTO :NEW.DEAL_ID FROM DUAL; END;
and the EDMX file is modified like this:

Code: Select all

  
    
  
  
  
  
  
  
  
There is no GUI for the StoreGeneratedPattern attribute, you have to edit the XML. With this attribute set, the EF will automatically reload the newly populated key after persisting the entity.

Post Reply