This topic is applicable to Entity Framework v1 - v6. It is not applicable to Entity Framework Core because Entity Developer generates ADO.NET code for invoking stored procedures in this case.
In this chapter we consider an usage of stored functions with ORM tools.
CREATE FUNCTION GET_DEPT_FUNC RETURN SYS_REFCURSOR AS cur SYS_REFCURSOR; BEGIN OPEN cur FOR SELECT * FROM DEPT ORDER BY DEPTNO; RETURN cur; END;
Expand the connection node in the Database Explorer, then your schema node and then the Functions node. Drag the GET_DEPT_FUNC to the diagram sheet. The function will be created in the storage model, and its following attributes will be set:
Corresponding method will be automatically created in the conceptual model. However, you need to set the return type for it manually. To do this perform the following actions:
You also may drag the function from Database Explorer to the storage model node in the Model Explorer. In such case only the storage model is updated, the corresponding method is not created. To create corresponding method, drag the function from the Model Explorer to the diagram sheet or right-click it in the Model Explorer and select Create Method from the popup menu.
<Function Name="GET_DEPT_FUNC" ReturnType="REF CURSOR" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="SCOTT" />We need to perform some XML code changes manually to use this function in our model.
<Function Name="GET_DEPT_FUNC" devart:IsConcealedFunction="true" devart:ReturnType="REF CURSOR" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="SCOTT" />
This example illustrates call of the method, retrieving the first element of the collection of entities, and output to console. Object context name and case of the Depts class and its fields may differ in your case.
Entity Framework Tutorial | Using Entity Data Model Wizard | Stored Routines in Entity Framework Model | Creating Database and Model | Stored Procedure Returning Result Set using REF CURSOR Out Parameter | Mapping CUD Operations to Stored Routines