This discussion is archived
5 Replies Latest reply: Feb 2, 2012 4:52 AM by Tridus RSS

get entity as return of collection in entity framework

878350 Newbie
Currently Being Moderated
how to get entity as return of collection in entity framework,,, i dont have any idea...
when i select entity and then get column information then no column return .....
  • 1. Re: get entity as return of collection in entity framework
    Christian.Shay - Oracle Pro
    Currently Being Moderated
    It sounds like you are trying to use an imported function. If you want any kind of return value from the function, the stored procedure must have a ref cursor. Then you need to set up the app.config to define the metadata for the ref cursor. If the metadata is setup wrong, clicking "get column information" will do nothing.
  • 2. Re: get entity as return of collection in entity framework
    878350 Newbie
    Currently Being Moderated
    let me tell you in detail,, while function import we have 3 option which tell us which return type we have to use scalar,complex or entity
    here what if i want to map my sp output to one entity , then here i m suppose to select entity as a return of collection type the same senario easily work with SQL DB
    over there i m able to get column information but for oracle can i do this...
    by using sp with ref cursor i can do for that i m suppose to define information in app.config so same manner what other settings i hav to do for entity collection type......
  • 3. Re: get entity as return of collection in entity framework
    911555 Newbie
    Currently Being Moderated
    I have about hundreed SP,- it's so tedious! Are you planned to automatize this process?
  • 4. Re: get entity as return of collection in entity framework
    Christian.Shay - Oracle Pro
    Currently Being Moderated
    user8323355 wrote:
    I have about hundreed SP,- it's so tedious! Are you planned to automatize this process?
    Yes, we are seriously considering adding such a tool.
  • 5. Re: get entity as return of collection in entity framework
    Tridus Journeyer
    Currently Being Moderated
    The way the Oracle provider works right now requires that you use a cursor, even if you want to map it to an entity. To map it to an entity, that cursor needs to contain the necessary columns to form the entity (ie: it's returning data from the same table).

    Here's an example from one I have mapped:

    CREATE OR REPLACE PROCEDURE ENVMSTR.P_ORG_UNIT_R_BY_STAFF
    (
    STAFF_ID_P IN NUMBER
    , RESULT_CURSOR_P OUT SYS_REFCURSOR
    ) AS
    BEGIN
    OPEN RESULT_CURSOR_P FOR

    select *
    from dept_organizational_unit d
    where deptorgunit_cd = (select deptorgunit_cd from staff where staff_id = STAFF_ID_P)
    or deptorgunit_cd in ( select deptorgunitrel_parent_cd from dept_org_unit_relation
    start with deptorgunitrel_child_cd = (select deptorgunit_cd from staff where staff_id = STAFF_ID_P)
    connect by prior deptorgunitrel_parent_cd = deptorgunitrel_child_cd);
    END P_ORG_UNIT_R_BY_STAFF;


    web.config:

    <add name="ENVMSTR.P_ORG_UNIT_R_BY_STAFF.RefCursor.RESULT_CURSOR_P" value="implicitRefCursor bindinfo='mode=Output'" />


    With those things set up, I'm able to import the function and map it to the entity that represents the dept_organizational_unit table (that entity is called OrgUnit in the model, but it doesn't really matter what you call it). The interesting thing I found is that in this situation I didn't actually have to add config file entries for each column in the cursor, probably because it maps perfectly to the entity. If I was trying to use a complex type instead then I probably would (and the configuration required to do that gets horrific in a hurry).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points