5 Replies Latest reply: Feb 2, 2012 6:52 AM by Tridus RSS

    get entity as return of collection in entity framework

    878350
      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
          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
            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
              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
                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
                  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).