Oracle Stored Procedures and Entity Framework Methods

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
mservidio
Posts: 14
Joined: Mon 26 Jul 2010 19:46

Oracle Stored Procedures and Entity Framework Methods

Post by mservidio » Thu 11 Nov 2010 20:01

Hi -

I'm using Entity Developer for my application, and I've mapped all the tables, and everything works great so far. Today, I wanted to create a method to execute an Oracle Store Procedure that has 4 in parameters and 1 out parameter. I haven't had any luck with getting the method to work. Since this is Oracle sp, I don't believe that the return type would be scaler as this isn't a function returning a scaler value. How do I go about configuring EF through Entity Developer to call an oracle store procedure with an number out parameter?

thanks,
Mark

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

Post by AndreyR » Fri 12 Nov 2010 17:32

I have just added a stored procedure having four input and one output parameter to Devart Entity model and had successfully called the procedure using the latest 5.70.190 release of dotConnect for Oracle.

mservidio
Posts: 14
Joined: Mon 26 Jul 2010 19:46

Post by mservidio » Fri 12 Nov 2010 18:10

Here's the error message I get:
The data reader is incompatible with the specified 'DataService.Metadata.CREATEVERSIONResult'. A member of the type, 'aNEWVERSIONID', does not have a corresponding column in the data reader with the same name.
'aNEWVERSIONID' is the name of my output parameter in the oracle procedure:

Code: Select all

PROCEDURE CREATEVERSION (
    aORIGINALVERSIONID NUMBER,
    aUSERID            NUMBER,
    aUSERCOMMENT VARCHAR2,
    aVERSIONTYPE VARCHAR2,
    aNEWVERSIONID OUT NUMBER);
Here is the result set class:

Code: Select all

public partial class CREATEVERSIONResult
    {
        #region Primitive Properties
    
        public Nullable aNEWVERSIONID
        {
            get;
            set;
        }

        #endregion
    }
Here is the generated function:

Code: Select all

public ObjectResult CREATEVERSION(Nullable aORIGINALVERSIONID, Nullable aUSERID, string aUSERCOMMENT, string aVERSIONTYPE, ObjectParameter aNEWVERSIONID)
        {
    
            ObjectParameter aORIGINALVERSIONIDParameter;
    
            if (aORIGINALVERSIONID.HasValue)
            {
                aORIGINALVERSIONIDParameter = new ObjectParameter("AORIGINALVERSIONID", aORIGINALVERSIONID);
            }
            else
            {
                aORIGINALVERSIONIDParameter = new ObjectParameter("AORIGINALVERSIONID", typeof(decimal));
            }
    
            ObjectParameter aUSERIDParameter;
    
            if (aUSERID.HasValue)
            {
                aUSERIDParameter = new ObjectParameter("AUSERID", aUSERID);
            }
            else
            {
                aUSERIDParameter = new ObjectParameter("AUSERID", typeof(decimal));
            }
    
            ObjectParameter aUSERCOMMENTParameter;
    
            if (aUSERCOMMENT != null)
            {
                aUSERCOMMENTParameter = new ObjectParameter("AUSERCOMMENT", aUSERCOMMENT);
            }
            else
            {
                aUSERCOMMENTParameter = new ObjectParameter("AUSERCOMMENT", typeof(string));
            }
    
            ObjectParameter aVERSIONTYPEParameter;
    
            if (aVERSIONTYPE != null)
            {
                aVERSIONTYPEParameter = new ObjectParameter("AVERSIONTYPE", aVERSIONTYPE);
            }
            else
            {
                aVERSIONTYPEParameter = new ObjectParameter("AVERSIONTYPE", typeof(string));
            }
            return base.ExecuteFunction("CREATEVERSION", aORIGINALVERSIONIDParameter, aUSERIDParameter, aUSERCOMMENTParameter, aVERSIONTYPEParameter, aNEWVERSIONID);
        }

mservidio
Posts: 14
Joined: Mon 26 Jul 2010 19:46

Post by mservidio » Fri 12 Nov 2010 18:12

I haven't defined any output types as this is a stored procedure and has output parameters, which I assume should be defined in the custom class as shown above. Could you show me example of the one you setup?

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

Post by AndreyR » Tue 16 Nov 2010 13:50

Are you able to reproduce the problem using the latest 5.70.190 release build?
Please try to regenerate the model using the latest version.
Notify me if anything goes wrong.

mservidio
Posts: 14
Joined: Mon 26 Jul 2010 19:46

Post by mservidio » Tue 16 Nov 2010 14:19

I'm using latest version of DevArt.Data.Oracle (5.70.190) already. Can you send me your test project, so that I can try and mimic it?

Still getting this error:
The data reader is incompatible with the specified 'DataService.Metadata.CREATEVERSIONResult'. A member of the type, 'ANEWVERSIONID', does not have a corresponding column in the data reader with the same name.
Here is the class for CREATEVERSIONResult:

Code: Select all

public partial class CREATEVERSIONResult
    {
        #region Primitive Properties
    
        public Nullable ANEWVERSIONID
        {
            get;
            set;
        }

        #endregion
    }
Here is the method from which I try and execute the sp call:

Code: Select all

public virtual void CreateVersion(int versionId, String versionType, int userId, String userComment)
        {
            using (ModelEntities context = new ModelEntities())
            {
                ObjectParameter newVersion = new ObjectParameter("ANEWVERSIONID", typeof(Decimal));
                context.CREATEVERSION(versionId, userId, userComment, versionType.Trim().ToUpper(), newVersion);
                int newVersionId = Convert.ToInt32(newVersion.Value);
            }
        }
Thanks,
Mark

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

Post by AndreyR » Wed 17 Nov 2010 14:17

I have sent the project to the e-mail provided in your user profile.
Please let me know if anything goes wrong.

mservidio
Posts: 14
Joined: Mon 26 Jul 2010 19:46

Post by mservidio » Wed 17 Nov 2010 14:51

Thanks for the example. One difference between my procedure call and yours was that I had a return type, so I adjusted mine to have similar settings. The other thing I just realized, is that when I turn the return type to none, my tt4 poco entity generator doesn't create a method for the method that I created in the entity framework model. tt4 picked up when it had a return type and created a function but it doesn't seem to create a method for me properly. Do you know if tt4 poco generator supports methods?

mservidio
Posts: 14
Joined: Mon 26 Jul 2010 19:46

Post by mservidio » Thu 18 Nov 2010 00:27

I've figured out my issue. I'm using tt4 template with poco objects, so the context class is built using template. The default Poco tt4 template v1.1 from microsoft doesn't support sp's with no return type. It doesn't include them in generation. Here is the fix at these links:

http://pastebin.com/sLbECkBg

http://visualstudiogallery.msdn.microso ... 3d02752313

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

Post by AndreyR » Thu 18 Nov 2010 15:06

Glad to hear the issue is resolved.
We plan to add POCO templates to Devart Entity models, it's in our roadmap.
You can influence our roadmap at Devart User Voice.

Post Reply