3 bugs: Multiple Return Types from Stored Procedure

Discussion of open issues, suggestions and bugs regarding Entity Developer - ORM modeling and code generation tool
Post Reply
jimi
Posts: 2
Joined: Tue 28 Feb 2012 03:15
Location: Sydney

3 bugs: Multiple Return Types from Stored Procedure

Post by jimi » Tue 28 Feb 2012 04:14

Hi, my apologies if I'm going over old ground with any of these but I searched the forums and couldn't find any related issues.

I've had some problems related to using oracle stored procs with multiple output parameters. I was wondering if I'm doing something wrong, or if these are genuine bugs (I'm using version 4.2)

1. "An item with the same key has already been added"

I created an Emp and Dept class mapping back to corresponding emp and dept tables, then created a get_dept_and_emp sproc that opens two out refcursors and selects the entire dept and emp table contents into the two respective refcursors (it doesn't matter what the columns are):

Code: Select all

PROCEDURE GET_DEPT_AND_EMP(P_OUT_EMP_CUR out SYS_REFCURSOR, P_OUT_DEPT_CUR out SYS_REFCURSOR)
is
BEGIN
  open p_out_emp_cur for
  select *
  from emp;
  
  open p_out_dept_cur for
  select *
  from DEPT;
end;
In Entity Developer (the VS 2010 plugin), I located sproc in the database explorer and dragged it straight onto "Methods" for my model. I edited the method and selected "Value Types", and chose EMP and DEPT as the return types.

On every subsequent save / close / reopen of the model, I get an error stating "An item with the same key has already been added" and a line number. When I go to that line in the XML, I see that duplicate EntitySet and ReturnType attributes have been added, with one pair having the devart: namespace prefix:

Code: Select all

<FunctionImport Name="GET_DEPT_AND_EMP" EntitySet="EMPs" ReturnType="Collection(Model.EMP)" devart:EntitySet="EMPs;DEPTs" devart:ReturnType="Collection(Model.EMP);Collection(Model.DEPT)" ...
Deleting the non-"devart: " attributes fixes the problem, but as soon as I save and reopen, the error comes back.

2. "Model.X does not contain a definition for 'EntityKey and no extension method..."

If I then select and enable the POCO Entity Class, the project will not build, raising errors relating to the entity key property not being found on EMP or DEPT (and also: The best overloaded method match for 'System.Data.Objects.ObjectContext.Attach(System.Data.Objects.DataClasses.IEntityWithKey)' has some invalid arguments). Somehow, the templating code sees these POCOs and treats them as entity classes because they are attached to some kind of multiple result set object.

3. "The data reader is incompatible with the specified 'Model.DEPT'"

Using a similar sproc with only one out refcursor for DEPT, if I change the name of a dept property (dept_name to deptname) but leave the column mapping unchanged (dept_name), and then try to call the sproc in my code, it throws this error: "The data reader is incompatible with the specified 'Model.DEPT'. A member of the type, 'DEPTNAME', does not have a corresponding column in the data reader with the same name." I don't have the same problem where there are multiple out refcursors, though, (in the case of Entity objects, seeing as it won't compile if I try this using the POCO template).

Are these genuine bugs / limitations, or am I just doing something wrong or missing a setting somewhere? I'm currently researching dotconnect in order to work out whether it's suitable for my department to use as a DAL for future projects, so any assistance would be very much appreciated.

Regards,
James

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 01 Mar 2012 17:09

Thank you for your reports. We have reproduced the mentioned three issues. We will investigate them and post here about the results.

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 07 Mar 2012 15:58

jimi wrote:1. "An item with the same key has already been added"
...
On every subsequent save / close / reopen of the model, I get an error stating "An item with the same key has already been added" and a line number. When I go to that line in the XML, I see that duplicate EntitySet and ReturnType attributes have been added, with one pair having the devart: namespace prefix:
...
The bug with parsing XML entries when using stored procedures with multiple result set is fixed.
jimi wrote:2. "Model.X does not contain a definition for 'EntityKey and no extension method..."

If I then select and enable the POCO Entity Class, the project will not build, raising errors relating to the entity key property not being found on EMP or DEPT (and also: The best overloaded method match for 'System.Data.Objects.ObjectContext.Attach(System.Data.Objects.DataClasses.IEntityWithKey)' has some invalid arguments). Somehow, the templating code sees these POCOs and treats them as entity classes because they are attached to some kind of multiple result set object.
The bug with generating code when using stored procedures with multiple result set with the POCO Entities template is fixed. We will post here when the corresponding build of dotConnect for Oracle is available for download.
jimi wrote:3. "The data reader is incompatible with the specified 'Model.DEPT'"

Using a similar sproc with only one out refcursor for DEPT, if I change the name of a dept property (dept_name to deptname) but leave the column mapping unchanged (dept_name), and then try to call the sproc in my code, it throws this error: "The data reader is incompatible with the specified 'Model.DEPT'. A member of the type, 'DEPTNAME', does not have a corresponding column in the data reader with the same name." I don't have the same problem where there are multiple out refcursors, though, (in the case of Entity objects, seeing as it won't compile if I try this using the POCO template).
Open your model in Entity Developer and make the following adjustment in Tools > Entity Developer > Model Explorer: right click on the method in CSDL > Result Mapping Details and correct the name of the corresponding column which is by default calculated basing on the property name.
You do not have the same problem for multiple out refcursors because in this case our own implementation of data retrieving is used which excludes the possibility of such problem.

jimi
Posts: 2
Joined: Tue 28 Feb 2012 03:15
Location: Sydney

Same problem for self-tracking entities

Post by jimi » Wed 07 Mar 2012 22:52

Thanks very much for your swift response, that's great.

I have since been trying out self-tracking entities, and the self-tracking entity template seems to have a similar issue to the POCO template. Have you noticed this as well?

Thanks again,
James

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 12 Mar 2012 16:49

jimi wrote:
Shalex wrote:
jimi wrote:2. "Model.X does not contain a definition for 'EntityKey and no extension method..."

If I then select and enable the POCO Entity Class, the project will not build, raising errors relating to the entity key property not being found on EMP or DEPT (and also: The best overloaded method match for 'System.Data.Objects.ObjectContext.Attach(System.Data.Objects.DataClasses.IEntityWithKey)' has some invalid arguments). Somehow, the templating code sees these POCOs and treats them as entity classes because they are attached to some kind of multiple result set object.
The bug with generating code when using stored procedures with multiple result set with the POCO Entities template is fixed. We will post here when the corresponding build of dotConnect for Oracle is available for download.
Thank you for posting.
I have since been trying out self-tracking entities, and the self-tracking entity template seems to have a similar issue to the POCO template.
Thank you for posting. We will fix the problem with the Self-Tracking Entity template as well.

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 13 Mar 2012 15:43

The bug with generating code when using stored procedures with multiple result set with the Self-Tracking Entity template is fixed. We will post here when the corresponding build of dotConnect for Oracle is available for download.

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 16 Mar 2012 17:50

New version of dotConnect for Oracle 6.80 is released!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=23639 .

Post Reply