Calling a Oracle S.P. that is returning REFCURSOR through Entity Framework

Here are the steps for how to call Oracle S.P. that is returning REFCURSOR through Entity Framework :-

1.) Configure Entity Framework in project to work with oracle. Here is a link that explains how to do that :-

2.)  At this point you must have generated the edmx file for your data model. Now open server explorer and right click on the SP name then click run. Check the "Select for config" Checkbox and click "AddConfig" button. See the screen shot.

This will add the "bindInfo" and "metadata" for the SP in the config file. this looks something like :-

    <version number="*">
        <storedProcedure schema="USER_UPW" name="EMP_GETLIST">
          <refCursor name="O_CURSOR">
            <bindInfo mode="Output" />
            <metadata columnOrdinal="0" columnName="ID" providerType="Decimal" allowDBNull="false" nativeDataType="Number" />
            <metadata columnOrdinal="1" columnName="EMPNAME" providerType="Varchar2" allowDBNull="false" nativeDataType="Varchar2" />
            <metadata columnOrdinal="2" columnName="EMPADDRESS" providerType="Varchar2" allowDBNull="false" nativeDataType="Varchar2" />
            <metadata columnOrdinal="3" columnName="BRANCH" providerType="Varchar2" allowDBNull="false" nativeDataType="Varchar2" />

3.) Now just double click the edmx file to open the design view. Go to model browser and check if function import is created for your SP. If it is created then its good just double click it to open the "Edit function import" window. If it is not created then don't worry just right click on the SP name under "Stored Procedures / Function" then click on add function import this will open "Add funcction import" window it is same as "Edit funcction import" except the title.

4.) Now select "Complex" in "Returns a collection of" then click on "Get Column Information" this will get the columns returned by SP in table below the button.

5.) Click on "Create new complex type" button this will create the complex type for your SP. Make sure the complex type is selected in the dropdown against the complex. Then click on OK.

6.) All set to go now add the following lines of code to call the SP :-

     ObjectResult<EMP_GETLIST_Result> emps = dbContext.EMP_GETLIST()